Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running Total Query Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 8:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
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
Post #1470762
Posted Friday, July 5, 2013 10:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
Nevermind. I misread the post. :)
Post #1470816
Posted Monday, July 8, 2013 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/68467/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471205
Posted Monday, July 8, 2013 5:12 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 632, Visits: 2,951
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
( SELECT n, DATEADD(DAY,n,@DtBegin) AS TranDate
FROM cteTally ta
WHERE n<=@dtDif)
INSERT INTO #results
SELECT n,
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)

SELECT value,
runningTotal,
TranDate
FROM #results

--cleanup
DROP TABLE #TranType1;
DROP TABLE #TranType2;
DROP TABLE #TranType3;
DROP TABLE #Tally;
DROP TABLE #Results;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1471393
Posted Tuesday, July 9, 2013 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
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


Post #1471674
Posted Tuesday, July 9, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471681
Posted Tuesday, July 9, 2013 9:04 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 6:17 AM
Points: 632, Visits: 2,951
Glad to help

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1471682
Posted Tuesday, July 9, 2013 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse