Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to make triple-pass UPDATE single-pass? Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 10:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 20,861, Visits: 32,888
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431101
Posted Thursday, March 14, 2013 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 20,861, Visits: 32,888

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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431104
Posted Thursday, March 14, 2013 11:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, December 13, 2014 8:27 AM
Points: 61, Visits: 678
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
Post #1431143
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse