Running Total Query

Question

Post reply

Running Total Query

Ariel Nessi

Old Hand

Points: 324
More actions
July 5, 2013 at 8:28 am

#279959

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

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

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

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

Erin Ramsay

SSCrazy Eights

Points: 9728

July 5, 2013 at 10:43 am

#1630143

Nevermind. I misread the post. 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

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 --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),

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

Ariel Nessi Old Hand Points: 324 More actions · Answer 4

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 --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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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/