How to make triple-pass UPDATE single-pass?

  • sqlnyc

    SSCommitted

    Points: 1726

    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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • sqlnyc

    SSCommitted

    Points: 1726

    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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • Lynn Pettis

    SSC Guru

    Points: 442144

    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

  • ChrisM@Work

    SSC Guru

    Points: 186045

    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 ๐Ÿ˜‰

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • ChrisM@Work

    SSC Guru

    Points: 186045

    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

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Lynn Pettis

    SSC Guru

    Points: 442144

    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.

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • Lynn Pettis

    SSC Guru

    Points: 442144

    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

    SSC Guru

    Points: 442144

    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?

  • sqlnyc

    SSCommitted

    Points: 1726

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply