November 19, 2019 at 3:46 pm
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
November 19, 2019 at 3:56 pm
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
November 19, 2019 at 4:03 pm
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
November 19, 2019 at 4:46 pm
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
November 19, 2019 at 4:51 pm
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 DateYou 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 thanROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
.Drew
Thank you Drew, but do you know if that functionality is available in SQL 2008?
November 19, 2019 at 5:01 pm
Frames were introduced in SQL 2012.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 19, 2019 at 5:44 pm
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 DateYou 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 thanROWS 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.
November 19, 2019 at 6:30 pm
Jonathan, Drew and ZZartin,
Wow, that was fast!
Thank you very much. The 2nd method provides me with what I need.
Phil...
November 19, 2019 at 7:31 pm
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
Change is inevitable... Change for the better is not.
November 19, 2019 at 8:04 pm
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.
November 19, 2019 at 8:42 pm
...
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".
November 19, 2019 at 8:42 pm
...
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".
November 19, 2019 at 9:55 pm
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 DateYou 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 thanROWS 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
November 20, 2019 at 12:54 am
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 DateYou 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 thanROWS 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.
November 20, 2019 at 2:48 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply