Running Total Query

  • Hi there people,

    I'm writting a T-SQL procedure to get a running total over a certain period,

    the data may have lapses in time between data rows, here is a example of the schema

    CREATE TABLE #TranType1(

    value int,

    TranDate datetime

    )

    CREATE TABLE #TranType2(

    value int,

    TranDate datetime

    )

    CREATE TABLE #TranType3(

    value int,

    TranDate datetime

    )

    INSERT INTO #TranType1

    SELECT 10,'2013-06-14'

    UNION

    SELECT 0,'2013-06-19'

    UNION

    SELECT 0,'2013-06-24'

    INSERT INTO #TranType2

    SELECT 0,'2013-06-26'

    UNION

    SELECT 5208,'2013-06-28'

    UNION

    SELECT 10,'2013-06-29'

    UNION

    SELECT 16005,'2013-07-01'

    INSERT INTO #TranType3

    SELECT 23135,'2013-07-02'

    UNION

    SELECT 35070,'2013-07-03'

    UNION

    SELECT 26509,'2013-07-04'

    UNION

    SELECT 7000,'2013-07-05'

    And here is the procedure I wrote

    DECLARE @DtBegin datetime,@DtEnd date,@dtDif int

    set @DtBegin = '5/1/2013'

    SET @DtEnd = GETDATE()

    set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd)

    ;WITH

    ctePingInfo(sumValue,TranDate) AS (

    SELECT SUM(SumValue),Trandate

    FROM(

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType1

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType2

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType3) ValInfo(SumValue,TranDate)

    WHERE Trandate BETWEEN @DtBegin AND @DtEnd

    GROUP BY TranDate

    ),

    E1(N) AS ( --=== 1x10^1

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 --10

    ),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --1x10^2

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --1x10^4

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

    cteDateRange(dateRange) AS (SELECT TOP (@dtDif+1) dateadd(day,n,@DtBegin) FROM cteTally),

    cteResult(totValue,TranDate) AS (

    SELECT COALESCE(cpi.Sumvalue,0),cdr.daterange

    FROM cteDateRange cdr LEFT JOIN ctePingInfo cpi ON cdr.daterange = cpi.TranDate

    )

    SELECT DISTINCT SUM(res2.totvalue) OVER (PARTITION BY res.TranDate),res.TranDate FROM cteResult res INNER JOIN cteResult res2 ON res.TranDate >= res2.TranDate ORDER BY res.TranDate

    DROP TABLE #TranType1

    DROP TABLE #TranType2

    DROP TABLE #TranType3

    It does return exatcly what I want, but given I believe this kind of request to be very common, I was wondering if there are any ways to speed it up, creating the physical tally table might not be an option, I believe there must be other topics on this very subject, but I'm not aware of what terms to look up for, any help is appreciated thanks !

    //Edit

    Please people nevermind, just found this link

    http://www.sqlservercentral.com/articles/T-SQL/68467/

    Don't know how that didn't come up on google, just had to make some minor changes

  • Nevermind. I misread the post. 🙂

  • Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    Make sure you follow the caveats if you decide to use this approach.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The COALESCE method for running totals is terribly slow. Working with the link that Sean posted, here is an example of how to accomplish this using the "Quirky Update" method.

    Note that I added a running total column because it is easier to test 😉

    --Sample Data

    ---------------------------------------------------------------------------------

    IF OBJECT_ID('tempdb..#TranType1') IS NOT NULL

    DROP TABLE #TranType1;

    IF OBJECT_ID('tempdb..#TranType2') IS NOT NULL

    DROP TABLE #TranType2;

    IF OBJECT_ID('tempdb..#TranType3') IS NOT NULL

    DROP TABLE #TranType3;

    IF OBJECT_ID('tempdb..#Tally') IS NOT NULL

    DROP TABLE #Tally;

    IF OBJECT_ID('tempdb..#Results') IS NOT NULL

    DROP TABLE #Results;

    CREATE TABLE #TranType1(

    value int,

    TranDate datetime);

    CREATE TABLE #TranType2(

    value int,

    TranDate datetime);

    CREATE TABLE #TranType3(

    value int,

    TranDate datetime);

    CREATE TABLE #Tally(n int primary key);

    INSERT INTO #TranType1

    SELECT 10,'2013-06-14' UNION SELECT 0,'2013-06-19' UNION SELECT 0,'2013-06-24';

    INSERT INTO #TranType2

    SELECT 0,'2013-06-26' UNION SELECT 5208,'2013-06-28' UNION

    SELECT 10,'2013-06-29' UNION SELECT 16005,'2013-07-01';

    INSERT INTO #TranType3

    SELECT 23135,'2013-07-02' UNION SELECT 35070,'2013-07-03' UNION

    SELECT 26509,'2013-07-04' UNION SELECT 7000,'2013-07-05';

    --Original Solution

    ---------------------------------------------------------------------------------

    DECLARE @DtBegin datetime,@DtEnd date,@dtDif int

    set @DtBegin = '5/1/2013'

    SET @DtEnd = GETDATE()

    set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd)

    ;WITH

    ctePingInfo(sumValue,TranDate) AS (

    SELECT SUM(SumValue),Trandate

    FROM(

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType1

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType2

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType3) ValInfo(SumValue,TranDate)

    WHERE Trandate BETWEEN @DtBegin AND @DtEnd

    GROUP BY TranDate

    ),

    E1(N) AS ( --=== 1x10^1

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 --10

    ),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --1x10^2

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --1x10^4

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

    cteDateRange(dateRange) AS (SELECT TOP (@dtDif+1) dateadd(day,n,@DtBegin) FROM cteTally),

    cteResult(totValue,TranDate) AS (

    SELECT COALESCE(cpi.Sumvalue,0),cdr.daterange

    FROM cteDateRange cdr LEFT JOIN ctePingInfo cpi ON cdr.daterange = cpi.TranDate)

    SELECT DISTINCT SUM(res2.totvalue) OVER (PARTITION BY res.TranDate),res.TranDate

    FROM cteResult res

    JOIN cteResult res2 ON res.TranDate >= res2.TranDate

    ORDER BY res.TranDate;

    GO

    --Faster Running Total Action

    ---------------------------------------------------------------------------------

    IF OBJECT_ID('tempdb..#results') IS NOT NULL

    DROP TABLE #results;

    CREATE TABLE #results (id int primary key, value int not null, runningTotal int not null, TranDate datetime not null);

    DECLARE @DtBegin datetime, @DtEnd date, @dtDif int, @runningTotal int=0;

    SET @DtBegin = '5/1/2013'

    SET @DtEnd = GETDATE()

    SET @dtDif = DATEDIFF(day,@DtBegin,@DtEnd);

    WITH

    TranTypes AS

    (SELECT value, TranDate

    FROM #TranType1

    UNION ALL

    SELECT value, TranDate

    FROM #TranType2

    UNION ALL

    SELECT value, TranDate

    FROM #TranType3),

    E1(N) AS

    (SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b),

    E4(N) AS (SELECT 1 FROM E2 a, E2 b),

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

    dates AS

    (SELECTn, DATEADD(DAY,n,@DtBegin) AS TranDate

    FROM cteTally ta

    WHERE n<=@dtDif)

    INSERT INTO #results

    SELECTn,

    ISNULL(t.value,0) AS value,

    0 AS RunningTotal,

    d.TranDate

    FROM dates d

    LEFT JOIN TranTypes t ON d.TranDate=t.TranDate

    ORDER BY TranDate;

    ;WITH ResultsOrdered AS

    (SELECT TOP 2147483648

    value,

    runningTotal,

    TranDate

    FROM #results

    ORDER BY id)

    UPDATE ResultsOrdered

    SET @runningTotal=RunningTotal=@runningTotal+value

    FROM ResultsOrdered WITH (TABLOCKX)

    OPTION (MAXDOP 1)

    SELECTvalue,

    runningTotal,

    TranDate

    FROM #results

    --cleanup

    DROP TABLE #TranType1;

    DROP TABLE #TranType2;

    DROP TABLE #TranType3;

    DROP TABLE #Tally;

    DROP TABLE #Results;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks Guys !

    I ended up with something like this

    CREATE TABLE #TranType1(

    value int,

    TranDate datetime

    )

    CREATE TABLE #TranType2(

    value int,

    TranDate datetime

    )

    CREATE TABLE #TranType3(

    value int,

    TranDate datetime

    )

    INSERT INTO #TranType1

    SELECT 10,'2013-06-14'

    UNION

    SELECT 0,'2013-06-19'

    UNION

    SELECT 0,'2013-06-24'

    INSERT INTO #TranType2

    SELECT 0,'2013-06-26'

    UNION

    SELECT 5208,'2013-06-28'

    UNION

    SELECT 10,'2013-06-29'

    UNION

    SELECT 16005,'2013-07-01'

    INSERT INTO #TranType3

    SELECT 23135,'2013-07-02'

    UNION

    SELECT 35070,'2013-07-03'

    UNION

    SELECT 26509,'2013-07-04'

    UNION

    SELECT 7000,'2013-07-05'

    DECLARE @DtBegin datetime,@DtEnd date,@dtDif int,@RunningTotal int = NULL

    set @DtBegin = '5/1/2013'

    SET @DtEnd = GETDATE()

    set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd)

    CREATE TABLE #QuirkUpdate(

    value int,

    TranDate date PRIMARY KEY

    )

    ;WITH

    ctePingInfo(sumValue,TranDate) AS (

    SELECT SUM(SumValue),Trandate

    FROM(

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType1

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType2

    UNION ALL

    SELECT COALESCE(value,0),CAST(Trandate AS date)

    FROM #TranType3) ValInfo(SumValue,TranDate)

    WHERE Trandate BETWEEN @DtBegin AND @DtEnd

    GROUP BY TranDate

    ),

    E1(N) AS ( --=== 1x10^1

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 --10

    ),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --1x10^2

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --1x10^4

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

    cteDateRange(dateRange) AS (SELECT TOP (@dtDif+1) dateadd(day,n,@DtBegin) FROM cteTally),

    cteResult(totValue,TranDate) AS (

    SELECT COALESCE(cpi.Sumvalue,0),cdr.daterange

    FROM cteDateRange cdr LEFT JOIN ctePingInfo cpi ON cdr.daterange = cpi.TranDate

    )

    INSERT INTO #QuirkUpdate

    SELECT totValue,TranDate FROM cteResult

    UPDATE #QuirkUpdate

    SET @RunningTotal = value =CASE

    WHEN @RunningTotal IS NOT NULL

    THEN @RunningTotal+value

    ELSE value

    END

    FROM #QuirkUpdate WITH (TABLOCKX) OPTION (MAXDOP 1) -- Nao remover o MAXDOP, pode quebrar o codigo

    SELECT value,tranDate FROM #QuirkUpdate

    DROP TABLE #QuirkUpdate

  • That looks pretty good. One recommendation, I would add the keyword CLUSTERED after PRIMARY KEY. And add a comment that it MUST be clustered. Yes I know that the default is clustered for a primary key BUT if at some point somebody else comes along and modifies the table they may inadvertently add a clustered index which would break your update.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Glad to help 🙂

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Also, if you give @RunningTotal an initial value of 0 instead of null you could remove the case expression.

    ...

    DECLARE @DtBegin datetime,@DtEnd date,@dtDif int,@RunningTotal int = 0

    ...

    UPDATE #QuirkUpdate

    SET @RunningTotal = value =@RunningTotal + value

    FROM #QuirkUpdate WITH (TABLOCKX) OPTION (MAXDOP 1) -- Nao remover o MAXDOP, pode quebrar o codigo

    ...

    Same results just slightly easier to read.

    Nice job with your code and thanks for posting back the results.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

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