All employees with Hours or Sales

This has me a little stumped... this is not the real database situation,
just a paraphrased version that should produce the results in an example I
can then use to make my actual query.
I need to get a recordset for each store of all the hours and sales broken
down by employee... Employees can have Hours with no Sales and Sales with
no Hours that day (returns or layaways).
I can't seem to get the correct answer with this one, and I know that it is
not all that hard, but I am missing something. Please help!
I am kinda lost here, any help would be greatly appreciated.
Here is the structure and the data:
CREATE TABLE [dbo].[Employees] (
[EmpID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Hours] (
[HID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [smalldatetime] NULL ,
[Hours] [int] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmpID] [int] NULL ,
[StoreID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sales] (
[SID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [smalldatetime] NULL ,
[TotalSales] [float] NULL ,
[EmpID] [int] NULL ,
[StoreID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stores] (
[StoreID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(1,'Bob',1
)
INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(2,'Sam',2
)
INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(3,'Charli
e',3)
INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(4,'Ralph'
,1)
INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
Center')
INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
Mall')
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
2005 12:00:00:000AM','8',1,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
2005 12:00:00:000AM','8',1,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
2005 12:00:00:000AM','8',2,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
2005 12:00:00:000AM','8',3,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
2005 12:00:00:000AM','8',1,2)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
2005 12:00:00:000AM','8',2,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
2005 12:00:00:000AM','8',3,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
2005 12:00:00:000AM','8',1,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
2005 12:00:00:000AM','8',2,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
4 2005 12:00:00:000AM','8',3,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
5 2005 12:00:00:000AM','8',3,1)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
5 2005 12:00:00:000AM','8',2,2)
INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
5 2005 12:00:00:000AM','8',1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(1,'Feb 1 2005
12:00:00:000AM',1.000000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(2,'Feb 2 2005
12:00:00:000AM',1.300000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(3,'Feb 1 2005
12:00:00:000AM',2.300000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(4,'Feb 1 2005
12:00:00:000AM',3.200000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(5,'Feb 1 2005
12:00:00:000AM',3.400000000000000e+001,2,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(6,'Feb 1 2005
12:00:00:000AM',4.500000000000000e+001,2,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(7,'Feb 1 2005
12:00:00:000AM',4.300000000000000e+001,2,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(8,'Feb 1 2005
12:00:00:000AM',3.200000000000000e+001,3,2)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(9,'Feb 1 2005
12:00:00:000AM',3.300000000000000e+001,3,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(10,'Feb 1 2005
12:00:00:000AM',2.320000000000000e+002,3,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(11,'Feb 2 2005
12:00:00:000AM',1.230000000000000e+002,2,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(12,'Feb 2 2005
12:00:00:000AM',2.300000000000000e+001,2,2)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(13,'Feb 2 2005
12:00:00:000AM',1.200000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(14,'Feb 2 2005
12:00:00:000AM',4.300000000000000e+001,1,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(15,'Feb 3 2005
12:00:00:000AM',5.400000000000000e+001,2,1)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(16,'Feb 3 2005
12:00:00:000AM',2.300000000000000e+001,2,2)
INSERT INTO [sales]
([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(17,'Feb 3 2005
12:00:00:000AM',7.400000000000000e+001,3,1)Select S.Name StoreName, E.Name EmpName,
Sum(H.Hours) Hours,
Sum(A.TotalSales) TotSales
From Stores S Cross Join Employees E
Left Join Hours H
On H.EmpID = E.EmpID
And H.StoreID = S.StoreID
Left Join Sales A
On A.EmpID = E.EmpID
And A.StoreID = S.StoreID
Group By S.Name, E.Name
"Atley" wrote:

> This has me a little stumped... this is not the real database situation,
> just a paraphrased version that should produce the results in an example I
> can then use to make my actual query.
>
> I need to get a recordset for each store of all the hours and sales broken
> down by employee... Employees can have Hours with no Sales and Sales with
> no Hours that day (returns or layaways).
> I can't seem to get the correct answer with this one, and I know that it i
s
> not all that hard, but I am missing something. Please help!
> I am kinda lost here, any help would be greatly appreciated.
>
>
> Here is the structure and the data:
> CREATE TABLE [dbo].[Employees] (
> [EmpID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [tinyint] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Hours] (
> [HID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [Hours] [int] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sales] (
> [SID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [TotalSales] [float] NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Stores] (
> [StoreID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(1,'Bob',1
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(2,'Sam',2
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(3,'Charli
e',3)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(4,'Ralph'
,1)
>
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
> Center')
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
> Mall')
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
> 2005 12:00:00:000AM','8',1,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
> 4 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
> 5 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
> 5 2005 12:00:00:000AM','8',2,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
> 5 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(1,'Feb 1 2005
> 12:00:00:000AM',1.000000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(2,'Feb 2 2005
> 12:00:00:000AM',1.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(3,'Feb 1 2005
> 12:00:00:000AM',2.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(4,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(5,'Feb 1 2005
> 12:00:00:000AM',3.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(6,'Feb 1 2005
> 12:00:00:000AM',4.500000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(7,'Feb 1 2005
> 12:00:00:000AM',4.300000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(8,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,3,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(9,'Feb 1 2005
> 12:00:00:000AM',3.300000000000000e+001,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(10,'Feb 1 2005
> 12:00:00:000AM',2.320000000000000e+002,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(11,'Feb 2 2005
> 12:00:00:000AM',1.230000000000000e+002,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(12,'Feb 2 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(13,'Feb 2 2005
> 12:00:00:000AM',1.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(14,'Feb 2 2005
> 12:00:00:000AM',4.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(15,'Feb 3 2005
> 12:00:00:000AM',5.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(16,'Feb 3 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(17,'Feb 3 2005
> 12:00:00:000AM',7.400000000000000e+001,3,1)
>
>
>
>|||And if you also want it broken out by date, then
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
Order By S.Name, E.Name, D.Date
"Atley" wrote:

> This has me a little stumped... this is not the real database situation,
> just a paraphrased version that should produce the results in an example I
> can then use to make my actual query.
>
> I need to get a recordset for each store of all the hours and sales broken
> down by employee... Employees can have Hours with no Sales and Sales with
> no Hours that day (returns or layaways).
> I can't seem to get the correct answer with this one, and I know that it i
s
> not all that hard, but I am missing something. Please help!
> I am kinda lost here, any help would be greatly appreciated.
>
>
> Here is the structure and the data:
> CREATE TABLE [dbo].[Employees] (
> [EmpID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [tinyint] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Hours] (
> [HID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [Hours] [int] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sales] (
> [SID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [TotalSales] [float] NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Stores] (
> [StoreID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(1,'Bob',1
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(2,'Sam',2
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(3,'Charli
e',3)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(4,'Ralph'
,1)
>
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
> Center')
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
> Mall')
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
> 2005 12:00:00:000AM','8',1,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
> 4 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
> 5 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
> 5 2005 12:00:00:000AM','8',2,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
> 5 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(1,'Feb 1 2005
> 12:00:00:000AM',1.000000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(2,'Feb 2 2005
> 12:00:00:000AM',1.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(3,'Feb 1 2005
> 12:00:00:000AM',2.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(4,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(5,'Feb 1 2005
> 12:00:00:000AM',3.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(6,'Feb 1 2005
> 12:00:00:000AM',4.500000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(7,'Feb 1 2005
> 12:00:00:000AM',4.300000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(8,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,3,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(9,'Feb 1 2005
> 12:00:00:000AM',3.300000000000000e+001,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(10,'Feb 1 2005
> 12:00:00:000AM',2.320000000000000e+002,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(11,'Feb 2 2005
> 12:00:00:000AM',1.230000000000000e+002,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(12,'Feb 2 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(13,'Feb 2 2005
> 12:00:00:000AM',1.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(14,'Feb 2 2005
> 12:00:00:000AM',4.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(15,'Feb 3 2005
> 12:00:00:000AM',5.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(16,'Feb 3 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(17,'Feb 3 2005
> 12:00:00:000AM',7.400000000000000e+001,3,1)
>
>
>
>|||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:

> This has me a little stumped... this is not the real database situation,
> just a paraphrased version that should produce the results in an example I
> can then use to make my actual query.
>
> I need to get a recordset for each store of all the hours and sales broken
> down by employee... Employees can have Hours with no Sales and Sales with
> no Hours that day (returns or layaways).
> I can't seem to get the correct answer with this one, and I know that it i
s
> not all that hard, but I am missing something. Please help!
> I am kinda lost here, any help would be greatly appreciated.
>
>
> Here is the structure and the data:
> CREATE TABLE [dbo].[Employees] (
> [EmpID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [tinyint] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Hours] (
> [HID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [Hours] [int] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sales] (
> [SID] [int] IDENTITY (1, 1) NOT NULL ,
> [Date] [smalldatetime] NULL ,
> [TotalSales] [float] NULL ,
> [EmpID] [int] NULL ,
> [StoreID] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Stores] (
> [StoreID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(1,'Bob',1
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(2,'Sam',2
)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(3,'Charli
e',3)
> INSERT INTO [employees] ([EmpID],[Name],[Level])VALUES(4,'Ralph'
,1)
>
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(1,'EC
Main','Eaton
> Center')
> INSERT INTO [stores] ([StoreID],[Name],[Address])VALUES(2,'FV
Main','Fairview
> Mall')
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(1,'Feb 1
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(2,'Feb 2
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(3,'Feb 2
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(4,'Feb 2
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(5,'Feb 1
> 2005 12:00:00:000AM','8',1,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(6,'Feb 3
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(7,'Feb 3
> 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(8,'Feb 4
> 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(9,'Feb 4
> 2005 12:00:00:000AM','8',2,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(10,'Feb
> 4 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(11,'Feb
> 5 2005 12:00:00:000AM','8',3,1)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(12,'Feb
> 5 2005 12:00:00:000AM','8',2,2)
> INSERT INTO [hours] ([HID],[Date],[Hours],[EmpID],[StoreID])
VALUES(13,'Feb
> 5 2005 12:00:00:000AM','8',1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(1,'Feb 1 2005
> 12:00:00:000AM',1.000000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(2,'Feb 2 2005
> 12:00:00:000AM',1.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(3,'Feb 1 2005
> 12:00:00:000AM',2.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(4,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(5,'Feb 1 2005
> 12:00:00:000AM',3.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(6,'Feb 1 2005
> 12:00:00:000AM',4.500000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(7,'Feb 1 2005
> 12:00:00:000AM',4.300000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(8,'Feb 1 2005
> 12:00:00:000AM',3.200000000000000e+001,3,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(9,'Feb 1 2005
> 12:00:00:000AM',3.300000000000000e+001,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(10,'Feb 1 2005
> 12:00:00:000AM',2.320000000000000e+002,3,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(11,'Feb 2 2005
> 12:00:00:000AM',1.230000000000000e+002,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(12,'Feb 2 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(13,'Feb 2 2005
> 12:00:00:000AM',1.200000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(14,'Feb 2 2005
> 12:00:00:000AM',4.300000000000000e+001,1,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(15,'Feb 3 2005
> 12:00:00:000AM',5.400000000000000e+001,2,1)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(16,'Feb 3 2005
> 12:00:00:000AM',2.300000000000000e+001,2,2)
> INSERT INTO [sales]
> ([SID],[Date],[TotalSales],[EmpID],[Stor
eID])VALUES(17,'Feb 3 2005
> 12:00:00:000AM',7.400000000000000e+001,3,1)
>
>
>
>|||Thanks for the quick response!
Actually,
I want a list of all EmployeeID's 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,'Feb|||Hi...
Sorry for jumping in, I hope I'm not off-base.
I was under the impression that multiple summarizations with multiple outer
joins would lead to double-triple counting, because of the aggregation.
Any time I've ever needed multiple SUM statements, I've either used a scalar
subquery in the SELECT to return the sums, or I queried from derived tables
that summed the hours and sales.
Just wanted to make sure I wasn't missing something.
Thanks,
Kevin|||> I was under the impression that multiple summarizations with multiple
> outer
> joins would lead to double-triple counting, because of the aggregation.
> Any time I've ever needed multiple SUM statements, I've either used a
> scalar
> subquery in the SELECT to return the sums, or I queried from derived
> tables
> that summed the hours and sales.
> Just wanted to make sure I wasn't missing something.
I believe your understanding is correct. The example below uses both a
derived table and subqueries to list employees with either sales or hours
along with aggregate totals.
DECLARE
@.StartDT smalldatetime,
@.EndDT smalldatetime
SET @.StartDT = '20050202'
SET @.EndDT = '20050204'
SELECT EmpID, Name, Level, Hours, Sales
FROM
(
SELECT
E.EmpID, E.Name, E.Level,
(SELECT SUM(H.Hours) Hours
FROM Hours H
WHERE H.EmpID = E.EmpID AND
H.[Date] BETWEEN @.StartDT AND @.EndDT) AS Hours,
(SELECT SUM(A.TotalSales) Sales
FROM Sales A
WHERE A.EmpID = E.EmpID AND
A.[Date] BETWEEN @.StartDT AND @.EndDT) AS Sales
FROM Employees E
) AS EmployeeMetrics
WHERE
Hours IS NOT NULL OR
Sales IS NOT NULL
Hope this helps.
Dan Guzman
SQL Server MVP
<Kevin@.test.com> wrote in message
news:er2F9CnIFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi...
> Sorry for jumping in, I hope I'm not off-base.
> I was under the impression that multiple summarizations with multiple
> outer
> joins would lead to double-triple counting, because of the aggregation.
> Any time I've ever needed multiple SUM statements, I've either used a
> scalar
> subquery in the SELECT to return the sums, or I queried from derived
> tables
> that summed the hours and sales.
> Just wanted to make sure I wasn't missing something.
> Thanks,
> Kevin