Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reset Running Total based on another column

Am trying to calculate running total. But it should reset when the cummulative sum greater than another column value

create table #reset_runn_total
(
id int identity(1,1),
val int, 
reset_val int
)

insert into #reset_runn_total
values 
(1,10),
(8,12),(6,14),(5,10),(6,13),(3,11),(9,8),(10,12)

sample data

+----+-----+-----------+
| id | val | reset_val |
+----+-----+-----------+
|  1 |   1 |        10 |
|  2 |   8 |        12 |
|  3 |   6 |        14 |
|  4 |   5 |        10 |
|  5 |   6 |        13 |
|  6 |   3 |        11 |
|  7 |   9 |         8 |
|  8 |  10 |        12 |
+----+-----+-----------+

Expected result

+----+-----+-----------------+-------------+
| id | val |    reset_val    | Running_tot |
+----+-----+-----------------+-------------+
|  1 |   1 | 10              |       1     |  
|  2 |   8 | 12              |       9     |  --1+8
|  3 |   6 | 14              |       15    |  --1+8+6 -- greater than reset val
|  4 |   5 | 10              |       5     |  --reset 
|  5 |   6 | 13              |       11    |  --5+6
|  6 |   3 | 11              |       14    |  --5+6+3 -- greater than reset val
|  7 |   9 | 8               |       9     |  --reset -- greater than reset val 
|  8 |  10 | 12              |      10     |  --reset
+----+-----+-----------------+-------------+

Query:

;WITH cte
     AS (SELECT id,
                val,
                reset_val,
                val AS running_total
         FROM   #reset_runn_total
         WHERE  id = 1
         UNION ALL
         SELECT r.*,
                CASE
                  WHEN lag(c.running_total + r.val) over(order by r.id) > lag(r.reset_val) over(order by r.id) THEN r.reset_val 
                  ELSE c.running_total + r.val
                END
         FROM   cte c
                JOIN #reset_runn_total r
                  ON r.id = c.id + 1)
SELECT *
FROM   cte 

obviously lag will not get the previous values any ideas?

like image 277
Pரதீப் Avatar asked Feb 07 '17 08:02

Pரதீப்


2 Answers

Try flag previous row

WITH cte
     AS (SELECT id,
                val,
                reset_val,
                val AS running_total,
                CASE WHEN val > reset_val THEN 1 ELSE 0 END as flag
         FROM   #reset_runn_total
         WHERE  id = 1
         UNION ALL
         SELECT r.*,
                CASE c.flag
                  WHEN 1 then r.val
                  ELSE c.running_total + r.val
                END,
                CASE WHEN CASE c.flag
                      WHEN 1 then r.val
                      ELSE c.running_total + r.val
                    END > r.reset_val 
                    THEN 1 ELSE 0 END
         FROM   cte c
                JOIN #reset_runn_total r
                  ON r.id = c.id + 1)
SELECT *
FROM   cte 
like image 188
Serg Avatar answered Nov 04 '22 10:11

Serg


You can try using a quirky update like this

--- setup
IF OBJECT_ID('tempdb..#reset_runn_total') IS NOT NULL DROP TABLE #reset_runn_total
create table #reset_runn_total(id int identity(1,1) PRIMARY KEY, val int, reset_val int, running_sum int)

insert into #reset_runn_total(val, reset_val) values (1,10),(8,12),(6,14),(5,10),(6,13),(3,11),(9,8),(10,12)

--- use quirky update
DECLARE     @running_sum    INT
            , @temp         INT

UPDATE      #reset_runn_total
SET         @temp = running_sum = COALESCE(@running_sum, 0) + val
            , @running_sum = CASE WHEN @temp < reset_val THEN @temp ELSE 0 END
OPTION      (FORCE ORDER)

--- dump result
SELECT * FROM #reset_runn_total

Note that CLUSTERED INDEX on the temp table is required (PK's default type) for OPTION (FORCE ORDER) to make sense.

like image 33
wqw Avatar answered Nov 04 '22 10:11

wqw