September 25, 2009 at 1:55 am
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
September 25, 2009 at 2:13 am
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://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
Regards
Gianluca
-- Gianluca Sartori
September 27, 2009 at 11:34 pm
Thanks for yoiur post Santori.. it was really helpful.
Any othe ideas / suggestions still welcome 🙂
Thank you
September 27, 2009 at 11:40 pm
Thanks for yoiur post Santori.. it was really helpful.
Any othe ideas / suggestions still welcome 🙂
Thank you
September 30, 2009 at 1:01 pm
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
Serial TransNo Amnt Running Total
1 456745 45 45
2 578433 34 79
3 347 12 91
4 299009 68 159
5 74566 5 5
6 754556 6 11
7 36333 95 106
8 128378 56 56
9 89965 29 85
10 82553 4 89
September 30, 2009 at 3:40 pm
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
September 30, 2009 at 7:28 pm
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://qa.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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply