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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 25, 2009 at 2:13 am

  #1058377

  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

  September 27, 2009 at 11:34 pm

  #1058904

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

  Any othe ideas / suggestions still welcome 🙂

  Thank you

• Jonathan Mallia

  SSCertifiable

  Points: 5192

  More actions

  September 27, 2009 at 11:40 pm

  #1058905

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

  Any othe ideas / suggestions still welcome 🙂

  Thank you

• maria.rivera

  Grasshopper

  Points: 13

  More actions

  September 30, 2009 at 1:01 pm

  #1060139

  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

  September 30, 2009 at 3:40 pm

  #1060210

  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

  September 30, 2009 at 7:28 pm

  #1060250

  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]