How to make triple-pass UPDATE single-pass?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » How to make triple-pass UPDATE single-pass?

Add to briefcase

13 posts, Page 2 of 2

««1 2

How to make triple-pass UPDATE single-pass?

Expand / Collapse

Author

Message

Posted Thursday, March 14, 2013 10:29 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463

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 Wink

Wink

Cool, need to modify my code as I managed miss the WHERE CLAUSE.

Cool

Cool

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

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 21,624, Visits: 27,463


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?

Cool

Cool

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

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 49, Visits: 438

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

13 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse