Running Totals by Date and Account

  • I'm trying to develop T-SQL (SQLServer 2008 R2) logic for calculating running totals for all accounts each day.

    The database columns available are Date, AccountNo and Amount.

    I need a report to list dates in the first column and each accounts running total in succeeding columns to the right.

    I would like all dates listed in column 1.  I would like running totals for every account for every date  If an account does not have any changes from the previous date, the running total, for the previous date, will  carry over.

    EXAMPLE

    CREATE TABLE [dbo].[AcctsRunTotals](

    [Date] [date] NULL,

    [101] [int] NULL,

    [201] [int] NULL,

    [301] [int] NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-01',100,200,250)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-02',17,0,55)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-03',4,10,0)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-04',9,10,4)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-05',11,7,1)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-06',9,11,10)

    INSERT [dbo].[AcctsRunTotals]([Date], [101], [201], [301]) VALUES ('2019-11-07',0,0,3)

    RUNNING TOTALS BY DATE

    DATE        Acct#1        Acct#2        Acct#3   etc.

    11/1/19      100             200             250

    11/2/19      117             200             305

    11/3/19      121            210              305

    11/4/19      130            220              309

    11/5/19      141            227              310

    11/6/19      150            238              320

    11/7/19      150            238              323

  • SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date
  • I'm not sure if the OVER clause is available in SQL 2008, if it isn't you can use this method:

    SELECT a.Date,
    b.Acct#1,
    b.Acct#2,
    b.Acct#3
    FROM [dbo].[AcctsRunTotals] a
    CROSS APPLY(SELECT SUM([101]) Acct#1,
    SUM([201]) Acct#2,
    SUM([301]) Acct#3
    FROM [dbo].[AcctsRunTotals] b
    WHERE b.Date <= a.Date) b
    ORDER BY a.Date
  • Jonathan AC Roberts wrote:

    SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date

    You should always, always, ALWAYS specify the frame in windowed functions that use a frame.  If you do not specify a frame, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which performs much worse than ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen wrote:

    Jonathan AC Roberts wrote:

    SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date

    You should always, always, ALWAYS specify the frame in windowed functions that use a frame.  If you do not specify a frame, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which performs much worse than ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    Drew

    Thank you Drew, but do you know if that functionality is available in SQL 2008?

  • Frames were introduced in SQL 2012.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen wrote:

    Jonathan AC Roberts wrote:

    SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date

    You should always, always, ALWAYS specify the frame in windowed functions that use a frame.  If you do not specify a frame, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which performs much worse than ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    Drew

     

    Keep in mind ROWS BETWEEN and RANGE BETWEEN do have different behavior besides just performance that might not be what you expect.

  • Jonathan, Drew and ZZartin,

    Wow, that was fast!

    Thank you very much.  The 2nd method provides me with what I need.

    Phil...

  • pgainer wrote:

    Jonathan, Drew and ZZartin,

    Wow, that was fast!

    Thank you very much.  The 2nd method provides me with what I need.

    Phil...

    Be careful here.  It looks to me that the CROSS APPLY forms a "Triangular Join".  If you have just a few rows, you might miss all of the totally unnecessary CPU and memory I/O being used.

    Here's a link that explains what a "Triangular Join" is.  Oddly enough, it cites the problem using running totals for an example.

    https://www.sqlservercentral.com/articles/hidden-rbar-triangular-joins

    I do have a way to avoid that problem that will blow the doors off of most other methods for calculating running totals but it does take a bit of setup and purists will insist that it will blow up someday (even when there's a built in test-for-success added to the code).  Let me know if you' like to use that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    I do have a way to avoid that problem that will blow the doors off of most other methods for calculating running totals but it does take a bit of setup and purists will insist that it will blow up someday (even when there's a built in test-for-success added to the code).  Let me know if you' like to use that.

    Jeff, It's not a cursor is it? 😛

    PS: that was a joke.

  • ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ZZartin wrote:

    drew.allen wrote:

    Jonathan AC Roberts wrote:

    SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date

    You should always, always, ALWAYS specify the frame in windowed functions that use a frame.  If you do not specify a frame, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which performs much worse than ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    Drew

    Keep in mind ROWS BETWEEN and RANGE BETWEEN do have different behavior besides just performance that might not be what you expect.

    I still recommend specifying the frame even you do want RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because it makes it clear that you are intentionally using that frame rather than forgetting to specify a frame.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen wrote:

    ZZartin wrote:

    drew.allen wrote:

    Jonathan AC Roberts wrote:

    SELECT Date,
    SUM([101]) OVER (ORDER BY Date) Acct#1,
    SUM([201]) OVER (ORDER BY Date) Acct#2,
    SUM([301]) OVER (ORDER BY Date) Acct#3
    FROM [dbo].[AcctsRunTotals]
    ORDER BY Date

    You should always, always, ALWAYS specify the frame in windowed functions that use a frame.  If you do not specify a frame, it will use the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which performs much worse than ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    Drew

    Keep in mind ROWS BETWEEN and RANGE BETWEEN do have different behavior besides just performance that might not be what you expect.

    I still recommend specifying the frame even you do want RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because it makes it clear that you are intentionally using that frame rather than forgetting to specify a frame.

    Drew

     

    Agreed, just wanted to make sure that it was understood that there's not only a potential performance difference but also a very real functionality difference between the two.

  • Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    I do have a way to avoid that problem that will blow the doors off of most other methods for calculating running totals but it does take a bit of setup and purists will insist that it will blow up someday (even when there's a built in test-for-success added to the code).  Let me know if you' like to use that.

    Jeff, It's not a cursor is it? 😛

    PS: that was a joke.

    Heh... ironically, it is... sort of... it's a "Pseudo Cursor".  I think you're probably already familiar with the "Quirky Update", yes?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 17 total)

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