Calculating the Running Sum Conditionally

Question

Post reply

Calculating the Running Sum Conditionally

Jonathan Mallia

SSCertifiable

Points: 5192
More actions
September 25, 2009 at 1:55 am

#210189

Hi guys / gals,
I am trying to calculate the running sum conditionally; basically if the running sum exceeds the value for 100, it restarts from 0 again.
Anyone can share their thoughts on how best to achieve the desired result?
The running sum is calculate on the field named Amnt in the sample data below:
CREATE TABLE #t (
Serial INT IDENTITY,
TransactionNo INT,
Amnt INT
)
INSERT INTO #t(TransactionNo, Amnt)
SELECT 456745, 45 UNION ALL
SELECT 578433,34 UNION ALL
SELECT 347,12 UNION ALL
SELECT 299009,68 UNION ALL
SELECT 74566,5 UNION ALL
SELECT 754556,6 UNION ALL
SELECT 36333,95 UNION ALL
SELECT 128378,56 UNION ALL
SELECT 89965,29 UNION ALL
SELECT 82553,4
Thanks

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could use the famous "quirky update" method for running totals:

DECLARE @RunningTotal INT

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t

CREATE TABLE #t (

Serial INT IDENTITY,

TransactionNo INT,

Amnt INT,

RunningTotal INT

)

CREATE CLUSTERED INDEX IX_t_Serial ON #t (Serial)

INSERT INTO #t(TransactionNo, Amnt)

SELECT 456745, 45 UNION ALL

SELECT 578433,34 UNION ALL

SELECT 347,12 UNION ALL

SELECT 299009,68 UNION ALL

SELECT 74566,5 UNION ALL

SELECT 754556,6 UNION ALL

SELECT 36333,95 UNION ALL

SELECT 128378,56 UNION ALL

SELECT 89965,29 UNION ALL

SELECT 82553,4

SET @RunningTotal = 0

UPDATE #t

SET @RunningTotal = RunningTotal = CASE WHEN @RunningTotal + Amnt > 100 THEN Amnt ELSE @RunningTotal + Amnt END

FROM #t WITH(INDEX(IX_t_Serial), TABLOCKX)

OPTION (MAXDOP 1)

The code relies on the clustered index order to update the table propagating the values from the previous rows to the following ones.

There's a great article by Jeff Moden describing this tachnique, but it's under rewrite. I suggest you take a look at it anyway: there's a .sql file attached to it that describes what goes on very well.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Regards

Gianluca

-- Gianluca Sartori

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 2

Thanks for yoiur post Santori.. it was really helpful.

Any othe ideas / suggestions still welcome 🙂

Thank you

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 3

Thanks for yoiur post Santori.. it was really helpful.

Any othe ideas / suggestions still welcome 🙂

Thank you

maria.rivera Grasshopper Points: 13 More actions · Answer 4

I'm a beginner but, what about this:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t

CREATE TABLE #t (

Serial INT IDENTITY,

TransactionNo INT,

Amnt INT,

RunningTotal INT

)

CREATE CLUSTERED INDEX IX_t_Serial ON #t (Serial)

INSERT INTO #t(TransactionNo, Amnt)

SELECT 456745, 45 UNION ALL

SELECT 578433,34 UNION ALL

SELECT 347,12 UNION ALL

SELECT 299009,68 UNION ALL

SELECT 74566,5 UNION ALL

SELECT 754556,6 UNION ALL

SELECT 36333,95 UNION ALL

SELECT 128378,56 UNION ALL

SELECT 89965,29 UNION ALL

SELECT 82553,4

declare @serial int

declare @amt int

declare @rtotal int

declare cursor_sum cursor for

select serial, amnt from #t

open cursor_sum

fetch next from cursor_sum

into @serial, @amt

while @@fetch_status = 0

begin

set @rtotal = case when isnull(@rtotal,0) > 100 then @amt else isnull(@rtotal,0) + @amt end

update #t set runningtotal = @rtotal

where serial = @serial

print @rtotal

fetch next from cursor_sum

into @serial, @amt

end

close cursor_sum

deallocate cursor_sum

select * from #t

SerialTransNoAmntRunning Total

14567454545

25784333479

33471291

429900968159

57456655

6754556611

73633395106

81283785656

9899652985

1082553489

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Maria, thanks for posting your code.

The cursor based solution works well and reliably, but could become too slow when the table becomes really large. Cursor based solutions don't scale well, while set based solutions generally perform way better.

Jeff Moden's quirky update is a "hack" to perform something that can't generally be done in a set based way combining set based code with an internal pseudo-cursor. I love this solution and I suggest you give it a try over a very large table: you'll be impressed by the performance.

Regards

Gianluca

-- Gianluca Sartori

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Maria,

You might also take a look at this thread, which shows another application of the quirky update and compares it to other methods.

http://www.sqlservercentral.com/Forums/Topic789373-8-1.aspx

While a lot of it might be a bit over your head as yet, it discusses the method heavily and shows other means of achieving the same goals, as well as the pros and cons involved in each.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]