Running Totals by Date and Account

Question

Running Totals by Date and Account

pgainer

SSC Enthusiast

Points: 181
More actions
November 19, 2019 at 3:46 pm

#3698866

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

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

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

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

drew.allen SSC Guru Points: 76945 More actions · Answer 3

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

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?

drew.allen SSC Guru Points: 76945 More actions · Answer 5

Frames were introduced in SQL 2012.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ZZartin SSC-Dedicated Points: 30896 More actions · Answer 6

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.

pgainer SSC Enthusiast Points: 181 More actions · Answer 7

Jonathan, Drew and ZZartin,

Wow, that was fast!

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

Phil...

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

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)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 9

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.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 10

...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 11

...

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

drew.allen SSC Guru Points: 76945 More actions · Answer 12

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

ZZartin SSC-Dedicated Points: 30896 More actions · Answer 13

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.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

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)