March 12, 2013 at 11:34 pm
I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible.
The way it's currently written, there are 3 temp tables that have just a few rows each
Header has 5 million rows
Details has 3 million rows
I have included a stripped donw version of the code as it is currently written: 3 updates to populate a "summary row", making three passes of multi-million row tables. I am trying to find a way to do this in a single pass.
Expected output (sorry for any formatting glitches):
EmployeeID Code1PeriodToDateCode1YearToDate Code2PeriodToDateCode2YearToDate Code3PeriodToDate Code3YearToDate
1 3.00 3.00 4.00 4.00 5.00 5.00
We are running SQL 2008 R2 Standard.
Thanks in advance for any suggestions.
sqlnyc
CREATE TABLE CodeIDTable1 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
)
CREATE TABLE CodeIDTable2 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
)
CREATE TABLE CodeIDTable3 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
)
INSERT CodeIDTable1 VALUES (1, 'A')
INSERT CodeIDTable1 VALUES (2, 'B')
INSERT CodeIDTable1 VALUES (3, 'C')
INSERT CodeIDTable2 VALUES (4, 'D')
INSERT CodeIDTable2 VALUES (5, 'E')
INSERT CodeIDTable2 VALUES (6, 'F')
INSERT CodeIDTable3 VALUES (7, 'G')
INSERT CodeIDTable3 VALUES (8, 'H')
INSERT CodeIDTable3 VALUES (9, 'I')
CREATE TABLE Details (
HeaderID INT NOT NULL
,DetailID INT NOT NULL
,CodeID INT NOT NULL
,EmployeeID INT NOT NULL
,Date Datetime NOT NULL
,Amount DECIMAL (18,2) NOT NULL
)
CREATE TABLE Header (
HeaderID INT NOT NULL
,EmployeeID INT NOT NULL
,HeaderDate DATETIME NOT NULL
)
INSERT Header VALUES (1, 1, '2013-01-01')
INSERT Header VALUES (2, 1, '2013-01-01')
INSERT Header VALUES (3, 1, '2013-01-01')
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 1, 1, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 2, 1, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 3, 1, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 4, 5, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 5, 5, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 6, 5, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 7, 6, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 8, 7, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 9, 7, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 10, 7, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 11, 7, 1, '2013-01-01', 1.00)
INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 12, 7, 1, '2013-01-01', 1.00)
--drop TABLE EmployeeSummary
CREATE TABLE EmployeeSummary (
EmployeeID INT NOT NULL
,Code1PeriodToDate DECIMAL(18,2) NULL
,Code1YearToDate DECIMAL(18,2) NULL
,Code2PeriodToDate DECIMAL(18,2) NULL
,Code2YearToDate DECIMAL(18,2) NULL
,Code3PeriodToDate DECIMAL(18,2) NULL
,Code3YearToDate DECIMAL(18,2) NULL
)
--TRUNCATE TABLE EmployeeSummary
INSERT EmployeeSummary (EmployeeID) VALUES (1)
DECLARE @FirstDayOfYear DATETIME = '2013-01-01', @LastDayOfYear DATETIME = '2013-12-31'
DECLARE @ReportStartingDate DATETIME = '2013-01-01', @ReportEndingDate DATETIME = '2013-12-31'
UPDATE EmployeeSummary
SET Code1PeriodToDate = Summary.AmountPeriod
,Code1YearToDate = Summary.AmountYtd
FROM (
SELECT Header.EmployeeID
,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountYtd
FROM Details
INNER JOIN CodeIDTable1 ON CodeIDTable1.CodeID = Details.CodeID
INNER JOIN Header ON Details.HeaderID = Header.HeaderID
WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY Header.EmployeeID
) AS Summary
WHERE EmployeeSummary.EmployeeId = Summary.EmployeeID
UPDATE EmployeeSummary
SET Code2PeriodToDate = Summary.AmountPeriod
,Code2YearToDate = Summary.AmountYtd
FROM (
SELECT Header.EmployeeID
,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountYtd
FROM Details
INNER JOIN CodeIDTable2 ON CodeIDTable2.CodeID = Details.CodeID
INNER JOIN Header ON Details.HeaderID = Header.HeaderID
WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY Header.EmployeeID
) AS Summary
WHERE EmployeeSummary.EmployeeId = Summary.EmployeeID
UPDATE EmployeeSummary
SET Code3PeriodToDate = Summary.AmountPeriod
,Code3YearToDate = Summary.AmountYtd
FROM (
SELECT Header.EmployeeID
,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount
ELSE 0
END) AS AmountYtd
FROM Details
INNER JOIN CodeIDTable3 ON CodeIDTable3.CodeID = Details.CodeID
INNER JOIN Header ON Details.HeaderID = Header.HeaderID
WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY Header.EmployeeID
) AS Summary
WHERE EmployeeSummary.EmployeeId = Summary.EmployeeID
SELECT * FROM EmployeeSummary
March 12, 2013 at 11:46 pm
Short answer is - yes it is possible.
You have three subqueries each aliased as "Summary." If you take each of those subqueries and Alias them to Summary1, Summary2, and Summary3 then join each alias to the table being updated it is fairly straightforward.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2013 at 6:21 am
Hi Jason,
Thanks so much for your response --
I see how what you proposed makes it a single statement, but my main goal was to accomplish the UPDATE using a single pass through the tables. I don't think what you proposed does that, unless I'm missing something.
Thanks again,
sqlnyc
March 14, 2013 at 7:10 am
Yes, this is almost certainly possible using this construct:
UPDATE tablea
set ... case statements here for each field with proper code table
FROM tablea
LEFT JOIN to various code tables
It is pretty complex and to me goes beyond the simple nature of forum posts. Hopefully you can figure it out from above or someone else will jump in and take the time to build it out for you. Or you can get a consultant to help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 14, 2013 at 7:13 am
With just a little thought I came up with the following two solutions:
set nocount on;
CREATE TABLE dbo.CodeIDTable1 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable2 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable3 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
INSERT dbo.CodeIDTable1 VALUES (1, 'A');
INSERT dbo.CodeIDTable1 VALUES (2, 'B');
INSERT dbo.CodeIDTable1 VALUES (3, 'C');
INSERT dbo.CodeIDTable2 VALUES (4, 'D');
INSERT dbo.CodeIDTable2 VALUES (5, 'E');
INSERT dbo.CodeIDTable2 VALUES (6, 'F');
INSERT dbo.CodeIDTable3 VALUES (7, 'G');
INSERT dbo.CodeIDTable3 VALUES (8, 'H');
INSERT dbo.CodeIDTable3 VALUES (9, 'I');
CREATE TABLE dbo.Details (
HeaderID INT NOT NULL
,DetailID INT NOT NULL
,CodeID INT NOT NULL
,EmployeeID INT NOT NULL
,Date Datetime NOT NULL
,Amount DECIMAL (18,2) NOT NULL
);
CREATE TABLE dbo.Header (
HeaderID INT NOT NULL
,EmployeeID INT NOT NULL
,HeaderDate DATETIME NOT NULL
);
INSERT dbo.Header VALUES (1, 1, '2013-01-01');
INSERT dbo.Header VALUES (2, 1, '2013-01-01');
INSERT dbo.Header VALUES (3, 1, '2013-01-01');
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 1, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 2, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 3, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 4, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 5, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 6, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 7, 6, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 8, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 9, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 10, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 11, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 12, 7, 1, '2013-01-01', 1.00);
--drop TABLE EmployeeSummary
CREATE TABLE dbo.EmployeeSummary (
EmployeeID INT NOT NULL
,Code1PeriodToDate DECIMAL(18,2) NULL
,Code1YearToDate DECIMAL(18,2) NULL
,Code2PeriodToDate DECIMAL(18,2) NULL
,Code2YearToDate DECIMAL(18,2) NULL
,Code3PeriodToDate DECIMAL(18,2) NULL
,Code3YearToDate DECIMAL(18,2) NULL
);
DECLARE @FirstDayOfYear DATETIME = '2013-01-01', @LastDayOfYear DATETIME = '2013-12-31';
DECLARE @ReportStartingDate DATETIME = '2013-01-01', @ReportEndingDate DATETIME = '2013-12-31';
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
)
insert into dbo.EmployeeSummary
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID;
select * from dbo.EmployeeSummary;
TRUNCATE TABLE dbo.EmployeeSummary;
INSERT EmployeeSummary (EmployeeID) VALUES (1);
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
),SummaryData as (
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID)
update es set
Code1PeriodToDate = sd.Code1AmountPeriod,
Code1YearToDate = sd.Code1AmountYTD,
Code2PeriodToDate = sd.Code2AmountPeriod,
Code2YearToDate = sd.Code2AmountYTD,
Code3PeriodToDate = sd.Code3AmountPeriod,
Code3YearToDate = sd.Code3AmountYTD
from
dbo.EmployeeSummary es
inner join SummaryData sd
on es.EmployeeID = sd.EmployeeID;
select * from dbo.EmployeeSummary;
go
drop table dbo.Header;
drop table dbo.Details;
drop table dbo.CodeIDTable1;
drop table dbo.CodeIDTable2;
drop table dbo.CodeIDTable3;
drop table dbo.EmployeeSummary;
go
March 14, 2013 at 7:31 am
sqlnyc (3/12/2013)
I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible. ...
I'd check that it's doing what you think it is before attepting to optimise it:
SELECT h.EmployeeID
,SUM(CASE WHEN h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN h.HeaderDate <= @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountYtd
FROM Details d
INNER JOIN Header h ON d.HeaderID = h.HeaderID
INNER JOIN CodeIDTable1 c ON c.CodeID = d.CodeID
WHERE h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY h.EmployeeID
Look at the WHERE clause and the two CASE expressions 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2013 at 7:36 am
Hmm, I wonder what happens if the codeid's repeat instead of being unique across all 3 code tables. Hopefully the way the aggregates are done will factor the Cartesian out but I think it could duplicate hits...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 14, 2013 at 7:38 am
TheSQLGuru (3/14/2013)
Hmm, I wonder what happens if the codeid's repeat instead of being unique across all 3 code tables. Hopefully the way the aggregates are done will factor the Cartesian out but I think it could duplicate hits...
+1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2013 at 8:34 am
TheSQLGuru (3/14/2013)
Hmm, I wonder what happens if the codeid's repeat instead of being unique across all 3 code tables. Hopefully the way the aggregates are done will factor the Cartesian out but I think it could duplicate hits...
And it could also be a valid scenerio as well, only the OP can tell us for sure.
March 14, 2013 at 10:11 am
Lynn Pettis (3/14/2013)
TheSQLGuru (3/14/2013)
Hmm, I wonder what happens if the codeid's repeat instead of being unique across all 3 code tables. Hopefully the way the aggregates are done will factor the Cartesian out but I think it could duplicate hits...And it could also be a valid scenerio as well, only the OP can tell us for sure.
Know thy data
Big reason we ask for sample data and desired output, right?:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2013 at 10:29 am
ChrisM@Work (3/14/2013)
sqlnyc (3/12/2013)
I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible. ...I'd check that it's doing what you think it is before attepting to optimise it:
SELECT h.EmployeeID,SUM(CASE WHEN h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountPeriod
,SUM(CASE WHEN h.HeaderDate <= @ReportEndingDate THEN d.Amount
ELSE 0
END) AS AmountYtd
FROM Details d
INNER JOIN Header h ON d.HeaderID = h.HeaderID
INNER JOIN CodeIDTable1 c ON c.CodeID = d.CodeID
WHERE h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate
GROUP BY h.EmployeeID
Look at the WHERE clause and the two CASE expressions 😉
Cool, need to modify my code as I managed miss the WHERE CLAUSE.
March 14, 2013 at 10:32 am
set nocount on;
CREATE TABLE dbo.CodeIDTable1 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable2 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable3 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
INSERT dbo.CodeIDTable1 VALUES (1, 'A');
INSERT dbo.CodeIDTable1 VALUES (2, 'B');
INSERT dbo.CodeIDTable1 VALUES (3, 'C');
INSERT dbo.CodeIDTable2 VALUES (4, 'D');
INSERT dbo.CodeIDTable2 VALUES (5, 'E');
INSERT dbo.CodeIDTable2 VALUES (6, 'F');
INSERT dbo.CodeIDTable3 VALUES (7, 'G');
INSERT dbo.CodeIDTable3 VALUES (8, 'H');
INSERT dbo.CodeIDTable3 VALUES (9, 'I');
CREATE TABLE dbo.Details (
HeaderID INT NOT NULL
,DetailID INT NOT NULL
,CodeID INT NOT NULL
,EmployeeID INT NOT NULL
,Date Datetime NOT NULL
,Amount DECIMAL (18,2) NOT NULL
);
CREATE TABLE dbo.Header (
HeaderID INT NOT NULL
,EmployeeID INT NOT NULL
,HeaderDate DATETIME NOT NULL
);
INSERT dbo.Header VALUES (1, 1, '2013-01-01');
INSERT dbo.Header VALUES (2, 1, '2013-01-01');
INSERT dbo.Header VALUES (3, 1, '2013-01-01');
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 1, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 2, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 3, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 4, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 5, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 6, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 7, 6, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 8, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 9, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 10, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 11, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 12, 7, 1, '2013-01-01', 1.00);
--drop TABLE EmployeeSummary
CREATE TABLE dbo.EmployeeSummary (
EmployeeID INT NOT NULL
,Code1PeriodToDate DECIMAL(18,2) NULL
,Code1YearToDate DECIMAL(18,2) NULL
,Code2PeriodToDate DECIMAL(18,2) NULL
,Code2YearToDate DECIMAL(18,2) NULL
,Code3PeriodToDate DECIMAL(18,2) NULL
,Code3YearToDate DECIMAL(18,2) NULL
);
DECLARE @FirstDayOfYear DATETIME = '2013-01-01', @LastDayOfYear DATETIME = '2013-12-31';
DECLARE @ReportStartingDate DATETIME = '2013-01-01', @ReportEndingDate DATETIME = '2013-12-31';
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
)
insert into dbo.EmployeeSummary
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID
where
h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate;
select * from dbo.EmployeeSummary;
TRUNCATE TABLE dbo.EmployeeSummary;
INSERT EmployeeSummary (EmployeeID) VALUES (1);
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
),SummaryData as (
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID
where
h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate)
update es set
Code1PeriodToDate = sd.Code1AmountPeriod,
Code1YearToDate = sd.Code1AmountYTD,
Code2PeriodToDate = sd.Code2AmountPeriod,
Code2YearToDate = sd.Code2AmountYTD,
Code3PeriodToDate = sd.Code3AmountPeriod,
Code3YearToDate = sd.Code3AmountYTD
from
dbo.EmployeeSummary es
inner join SummaryData sd
on es.EmployeeID = sd.EmployeeID;
select * from dbo.EmployeeSummary;
go
drop table dbo.Header;
drop table dbo.Details;
drop table dbo.CodeIDTable1;
drop table dbo.CodeIDTable2;
drop table dbo.CodeIDTable3;
drop table dbo.EmployeeSummary;
go
Added WHERE CLAUSE, but yes, is this really doing what you expect?
March 14, 2013 at 11:12 am
Many thanks to all that replied.
I simplified the example to (hopefully) make it easy for others to help me. The code as originally written has the following WHERE clause:
WHERE YEAR(h.HeaderDate) = YEAR(ReportEndingDate)
Which should be ok for the aggregates in the CASE statement
But the obvious problem here is what should YTD represent, if you have ReportStartingDate ReportEndingDate that are in different years? I have posed this question to management, and am awaiting a response.
Lynn - I had initially tried putting all the codes in a single CTE as you did, but then in a "deer in headlights" moment, couldn't wrap my brain around how to differentiate the sets of codes when JOINing.
All of the codes are unique in the source table, but on the client end, they are allowed to pick codes for the three sets. Then comma delimited strings of the three sets of codes are passed to the stored procedure that contains the code I posted.
I am trying to determine if it is possible for the clients to select codes that might be duplicated among the three sets.
Again my thanks to everyone. Sorry if what I posted was confusing --
Best wishes,
sqlnyc
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply