All employees with Hours or Sales - USE THIS ONE

Thanks for the quick response!
I made a mistake on the last one.
Actually,
I want a list of all Employees (ID, Name and Level) who have either sales or
hours for a
certain date range
I can't seem to get that.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BDDBF80C-8838-49C3-A248-7ABF9AFF2172@.microsoft.com...
> And if you don;t want to see the rows where there are no Sales and no
Hours
> Add a "Having" clause...
> Having Sum(H.Hours) Is Not Null Or Sum(A.TotalSales) Is Not Null
> -- *************************************
> Select S.Name StoreName, E.Name EmpName, D.Date,
> Sum(H.Hours) Hours,
> Sum(A.TotalSales) TotSales
> From Stores S Cross Join Employees E
> Cross Join
> (Select Distinct Date From Hours
> Union
> Select Distinct Date From Sales) D
> Left Join Hours H
> On H.EmpID = E.EmpID
> And H.StoreID = S.StoreID
> And H.Date = D.Date
> Left Join Sales A
> On A.EmpID = E.EmpID
> And A.StoreID = S.StoreID
> And A.Date = D.Date
> Group By S.Name, E.Name, D.Date
> Having Sum(H.Hours) Is Not Null Or Sum(A.TotalSales) Is Not Null
> Order By S.Name, E.Name, D.Date
> "Atley" wrote:
>
I
broken
with
is
([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'FebThat's muh easier...
Select EmpID, Name EmpName
From Employees
Where EmpID In
(Select Distinct EmpID From Hours
Where Date > @.StartDT And Date < @.EndDT
Union
Select Distinct EmpID From Sales
Where Date > @.StartDT And Date < @.EndDT)
"Atley" wrote:

> Thanks for the quick response!
> I made a mistake on the last one.
> Actually,
> I want a list of all Employees (ID, Name and Level) who have either sales
or
> hours for a
> certain date range
>
> I can't seem to get that.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BDDBF80C-8838-49C3-A248-7ABF9AFF2172@.microsoft.com...
> Hours
> I
> broken
> with
> is
> ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
> ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
>
>|||> I want a list of all Employees (ID, Name and Level) who have either sales
> or
> hours for a
> certain date range
Another method is with EXISTS like the example below. This is most
efficient when you only need to check for existence of data.
SELECT EmpID, Name, Level
FROM Employees E
WHERE EXISTS
(
SELECT *
FROM Hours H
WHERE H.EmpID = E.EmpID AND
H.Date BETWEEN @.StartDT AND @.EndDT
) OR
EXISTS
(
SELECT *
FROM Sales A
WHERE A.EmpID = E.EmpID AND
A.Date BETWEEN @.StartDT AND @.EndDT
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23x$5V7cIFHA.2704@.tk2msftngp13.phx.gbl...
> Thanks for the quick response!
> I made a mistake on the last one.
> Actually,
> I want a list of all Employees (ID, Name and Level) who have either sales
> or
> hours for a
> certain date range
>
> I can't seem to get that.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BDDBF80C-8838-49C3-A248-7ABF9AFF2172@.microsoft.com...
> Hours
> I
> broken
> with
> is
> ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
> ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
> ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
>|||Dan,
As to efficiency, your solution, (using Exists) is faster than doing
"Where IN ..." ? when you're examing only one or two records in outer
table...
Something to do with the query proessor being able to stop as soon as it
finds a record that satisfies the exists) ?
otoh, when you're examining a great number of records in outer table,
because Where Exists subqueries are correlated, they would run once for each
empID in Employees, right ?
I wonder what the trade off would be ?
"Dan Guzman" wrote:

> Another method is with EXISTS like the example below. This is most
> efficient when you only need to check for existence of data.
> SELECT EmpID, Name, Level
> FROM Employees E
> WHERE EXISTS
> (
> SELECT *
> FROM Hours H
> WHERE H.EmpID = E.EmpID AND
> H.Date BETWEEN @.StartDT AND @.EndDT
> ) OR
> EXISTS
> (
> SELECT *
> FROM Sales A
> WHERE A.EmpID = E.EmpID AND
> A.Date BETWEEN @.StartDT AND @.EndDT
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Atley" <atley_1@.hotmail.com> wrote in message
> news:%23x$5V7cIFHA.2704@.tk2msftngp13.phx.gbl...
>
>|||The SQL 2000 optimizer will probably generate identical query plans as long
as the EXISTS or IN subqueries are the semantically the same. The
optimizer's job is to determine the best plan, which may vary depending on
the actual number of rows in the outer table. Consequently, you shouldn't
need to restructure a query depending on the number of outer rows. No need
to specify DISTINCT or UNION in the IN subquery either since this doesn't
affect the semantics.
My personal preference is to always use EXISTS because it is more flexible
and less prone to unexpected results when NOT is specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:B5C9D1FE-775E-44EE-BC99-CAF3919EB814@.microsoft.com...
> Dan,
> As to efficiency, your solution, (using Exists) is faster than doing
> "Where IN ..." ? when you're examing only one or two records in outer
> table...
> Something to do with the query proessor being able to stop as soon as it
> finds a record that satisfies the exists) ?
> otoh, when you're examining a great number of records in outer table,
> because Where Exists subqueries are correlated, they would run once for
> each
> empID in Employees, right ?
> I wonder what the trade off would be ?
> "Dan Guzman" wrote:
>|||Didn't know it could do that... Thanks, In general I prefer exists too, but
was sometimes using Where IN... when I thought the number of rows in outer
table was large enough to have an effect...
"Dan Guzman" wrote:

> The SQL 2000 optimizer will probably generate identical query plans as lon
g
> as the EXISTS or IN subqueries are the semantically the same. The
> optimizer's job is to determine the best plan, which may vary depending on
> the actual number of rows in the outer table. Consequently, you shouldn't
> need to restructure a query depending on the number of outer rows. No nee
d
> to specify DISTINCT or UNION in the IN subquery either since this doesn't
> affect the semantics.
> My personal preference is to always use EXISTS because it is more flexible
> and less prone to unexpected results when NOT is specified.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:B5C9D1FE-775E-44EE-BC99-CAF3919EB814@.microsoft.com...
>
>