I want to add a field to an existing MS-SQL-2000-table via Microsoft-JDBC-SP3-driver, Version 2.2.0040.
I tried to do this with a preparedStatement object for alter table in Java and wanted to pass the fieldname and fieldtype via Parameter. Then I get the message: cannot find the datatype @.P2 (which seems to be the internal placeholder for params). What is
the mistake or is it not possible to use the alter table command with prepared statement and parameters ?
Would be great, if someone knows something about it.
Here is something of the non working code:
PreparedStatement pstmtM1;
String sqlParamM1 = "ALTER TABLE LIMESTAB ADD [ ? ] [ ? ]";
...
pstmtM1 = conM.prepareStatement(sqlParamM1);
pstmtM1.setString (1, "orderno");
pstmtM1.setString(2,"varchar");
pstmtM1.executeUpdate();
Thank's !!
| Thread-Topic: Alter Table Add field via JDBC, preparedStatement and
Parameter fa
| thread-index: AcR0xrMPXWJxd53zRn66DN+jgi9f0g==
| X-WBNR-Posting-Host: 217.146.157.251
| From: "=?Utf-8?B?ZGJpbmZvcm1hdA==?="
<dbinformat@.discussions.microsoft.com>
| Subject: Alter Table Add field via JDBC, preparedStatement and Parameter
fa
| Date: Wed, 28 Jul 2004 10:17:02 -0700
| Lines: 15
| Message-ID: <19D18530-C3A9-4BFE-82C3-B742C37ED580@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6211
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I want to add a field to an existing MS-SQL-2000-table via
Microsoft-JDBC-SP3-driver, Version 2.2.0040.
| I tried to do this with a preparedStatement object for alter table in
Java and wanted to pass the fieldname and fieldtype via Parameter. Then I
get the message: cannot find the datatype @.P2 (which seems to be the
internal placeholder for params). What is the mistake or is it not possible
to use the alter table command with prepared statement and parameters ?
| Would be great, if someone knows something about it.
| Here is something of the non working code:
|
| PreparedStatement pstmtM1;
| String sqlParamM1 = "ALTER TABLE LIMESTAB ADD [ ? ] [ ? ]";
| ...
| pstmtM1 = conM.prepareStatement(sqlParamM1);
| pstmtM1.setString (1, "orderno");
| pstmtM1.setString(2,"varchar");
| pstmtM1.executeUpdate();
|
| Thank's !!
|
|
Hi,
You cannot submit an ALTER TABLE statement using parameters like this.
Below is how SQL Server is interpreting your code:
exec sp_executesql N'ALTER TABLE LIMESTAB ADD [ @.P1 ] [ @.P2 ]', N'@.P1
nvarchar(4000) ,@.P2 nvarchar(4000) ', N'orderno', N'varchar'
Even in straight T-SQL, you must dynamically build the query string and
then execute it using either sp_executesql or EXECUTE. Since you are using
Java, you should just build your string in the code and then execute it
using a standard Statement object:
Statement stmt = conn.createStatement();
String colname = "orderno";
String coltype = "varchar";
String sql = "ALTER TABLE LIMESTAB ADD ";
stmt.executeUpdate(sql + " " + colname + " " + coltype);
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Showing posts with label version. Show all posts
Showing posts with label version. Show all posts
Monday, March 19, 2012
Thursday, February 9, 2012
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
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
Subscribe to:
Posts (Atom)