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 Tuesday, March 12, 2013 11:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:54 PM
Points: 61, Visits: 677
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 Code1PeriodToDate Code1YearToDate Code2PeriodToDate Code2YearToDate 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

Post #1430220
Posted Tuesday, March 12, 2013 11:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 17,947, Visits: 15,940
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
Post #1430224
Posted Thursday, March 14, 2013 6:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:54 PM
Points: 61, Visits: 677
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
Post #1430912
Posted Thursday, March 14, 2013 7:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 4,435, Visits: 6,336
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 at GMail
Post #1430946
Posted Thursday, March 14, 2013 7:13 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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





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 #1430950
Posted Thursday, March 14, 2013 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,862, Visits: 14,160
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


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1430958
Posted Thursday, March 14, 2013 7:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 4,435, Visits: 6,336
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 at GMail
Post #1430964
Posted Thursday, March 14, 2013 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,862, Visits: 14,160
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


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1430966
Posted Thursday, March 14, 2013 8:34 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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.



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 #1431014
Posted Thursday, March 14, 2013 10:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 17,947, Visits: 15,940
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?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1431093
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse