sql dynamic cumulative sum - SQL Server

Question

I have a table like this

+------+--------+
|   ID | Salary | 
+------+--------+
| 1    |    100 |
| 2    |     40 |
| 3    |     30 |
| 4    |     40 |
| 5    |     90 |
| 6    |    160 |
| 7    |     70 |
| 8    |     40 |
| 9    |     20 |
| 10   |     10 |
| 11   |    200 | 
| 12   |     50 |
+------+--------+

I can make normal cumulative sum but i need something different from normal CUMULATIVE SUM. If the cumulative sum is %50 higher than the last sum, for the next cumulative sums start from this value

+------+--------+---------------+
|   ID | Salary | Running Total |
+------+--------+---------------+
| 1    |    100 |           100 |
| 2    |     40 |           140 |
| 3    |     30 |           170 |
| 4    |     40 |           210 |
| 5    |     90 |           300 |
| 6    |    160 |           460 |
| 7    |     70 |           230 |
| 8    |     40 |           270 |
| 9    |     20 |           290 |
| 10   |     10 |           300 |
| 11   |    200 |           500 |
| 12   |     50 |           250 |
+------+--------+---------------+

I want a output like this.

Hi. Could you please add some examples on what youre trying to achieve? Like input <> output so we can unterstand better. As the question stands its hard to understand. — Fabian S., Jan 29 '18 at 10:31
Is the fact that `460 > 1.5 * 300` the reason to 'reset' and start from 160? Please confirm, or else add a better explanation + more examples. — Peter B, Jan 29 '18 at 10:35
Can you explain why your running total resets at `ID=6` but not at `ID=2`. And what is your SQL version? — uzi, Jan 29 '18 at 10:35
I want to examine the increment rates. The important thing for me is the relation between last two running total. — Gork. O, Jan 29 '18 at 10:36
The solution to this requires recursive CTEs. These are both rather expensive and something you don't currently understand. Before proferring an answer, I would want to be sure that the question is quite clear. Can you explain why 50% and why the row (6) is combined with the previous values and not the next value? — Gordon Linoff, Jan 29 '18 at 10:37
Yes, Peter B, you're right. Reason for reset is 6th line, total is 50% higher than line 5 — Gork. O, Jan 29 '18 at 10:42
@GordonLinoff %50 is not an important thing it could be change. Line 6 is a large increase in line. i do not have any relationship with the previous lines after the big increments — Gork. O, Jan 29 '18 at 10:54

DineshDB · Accepted Answer · 2018-01-30T09:49:57.310

Try this:

SELECT ID , Salary,SUM(Salary) OVER(ORDER BY ID)Running Total
FROM Your_Table

Updated Answer:

I spent my whole day to get this:

DECLARE @TAB TABLE(ID INT, SALARY INT)

INSERT INTO @TAB VALUES(1 ,100)
INSERT INTO @TAB VALUES(2 , 40)
INSERT INTO @TAB VALUES(3 , 30)
INSERT INTO @TAB VALUES(4 , 40)
INSERT INTO @TAB VALUES(5 , 90)
INSERT INTO @TAB VALUES(6 ,160)
INSERT INTO @TAB VALUES(7 , 70)
INSERT INTO @TAB VALUES(8 , 40)
INSERT INTO @TAB VALUES(9 , 20)
INSERT INTO @TAB VALUES(10, 10)
INSERT INTO @TAB VALUES(11,200)
INSERT INTO @TAB VALUES(12, 50)


DECLARE @MIN INT, @MAX INT, @PreVal INT, @CurVal INT, @OldVal INT,@NextVal,  INT, @ExistVal INT
DECLARE @TABLE1 TABLE(ID INT, Sal INT, RunTotal INT)

INSERT INTO @TABLE1 (Id, Sal)
SELECT * FROM @TAB

SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @TAB

WHILE(@MIN<=@MAX)
BEGIN
    SELECT @NextVal=SALARY FROM @TAB WHERE ID=@MIN
    SELECT @ExistVal=SALARY FROM @TAB WHERE ID=@MIN-1
    SELECT @OldVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-1
    SELECT @PreVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-2

    IF(@OldVal>@PreVal+(@PreVal/2))
    BEGIN
        SELECT @CurVal = @NextVal+@ExistVal
    END
    ELSE
    BEGIN
        SELECT @CurVal=ISNULL(@OldVal,0)+@NextVal-- FROM @TABLE1 WHERE ID=@MIN
    END

    UPDATE @TABLE1 SET RunTotal=@CurVal WHERE ID=@MIN
    SELECT @MIN=@MIN+1
END

SELECT * FROM @TABLE1

Result:

ID  Sal RunTotal
1   100 100
2   40  140
3   30  170
4   40  210
5   90  300
6   160 460
7   70  230
8   40  270
9   20  290
10  10  300
11  200 500
12  50  250

If anybody with better answer, kindly mention in the comments.

I just realized how do you calculate the runtotal value. You get the RunTotal value on line 7 by dividing by the value 2 on line 6. (I mean this SELECT @CurVal = @CurVal/2) But i don't want this i want to get value with sum of the salary line 6 and 7. Like "160 +70 = 230". — Gork. O, Jan 30 '18 at 09:43
It's working well, thanks again. Do you have an idea about what I mean at the bottom. (moving average) — Gork. O, Jan 30 '18 at 09:58

DineshDB · Answer 2 · 2018-01-30T11:53:29.817

Answer for Your Second Question:

DECLARE @MIN INT, @MAX INT, @PreVal INT, @CurVal INT, @OldVal INT,@NextVal INT, @ExistVal INT
DECLARE @TABLE1 TABLE(ID INT, Sal INT, RunTotal INT,TimesGen INT)

INSERT INTO @TABLE1 (Id, Sal)
SELECT * FROM @TAB

SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @TAB

WHILE(@MIN<=@MAX)
BEGIN
    SELECT @NextVal=SALARY FROM @TAB WHERE ID=@MIN
    SELECT @ExistVal=SALARY FROM @TAB WHERE ID=@MIN-1
    SELECT @OldVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-1
    SELECT @PreVal=RunTotal FROM @TABLE1 WHERE ID=@MIN-2

    IF(@OldVal>@PreVal+(@PreVal/2))
    BEGIN
        SELECT @CurVal = @NextVal--+@ExistVal
        UPDATE @TABLE1 SET TimesGen=1 WHERE ID=@MIN 
    END
    ELSE
    BEGIN
        SELECT @CurVal=ISNULL(@OldVal,0)+@NextVal-- FROM @TABLE1 WHERE ID=@MIN
        UPDATE @TABLE1 SET TimesGen=ISNULL((SELECT TimesGen FROM @TABLE1 WHERE ID=@MIN-1),0)+1 WHERE ID=@MIN
    END

    UPDATE @TABLE1 SET RunTotal=@CurVal WHERE ID=@MIN
    SELECT @MIN=@MIN+1
END

SELECT ID,Sal Salary
    ,'('+CAST(RunTotal AS VARCHAR)+'/'+CAST(TimesGen AS VARCHAR)+') '+CAST(CAST(RunTotal*1.0/TimesGen AS NUMERIC(8,1)) AS VARCHAR) MovingAvg
    ,CAST(RunTotal*1.0/TimesGen AS NUMERIC(8,1))MovingAvg1
FROM @TABLE1

OutPut:

ID  Salary  MovingAvg
1   100     (100/1) 100.0
2   40      (140/2) 70.0
3   30      (170/3) 56.7
4   40      (210/4) 52.5
5   90      (300/5) 60.0
6   160     (460/6) 76.7
7   70      (230/2) 115.0
8   40      (270/3) 90.0
9   20      (290/4) 72.5
10  10      (300/5) 60.0
11  200     (500/6) 83.3
12  50      (250/2) 125.0

Gork. O · Answer 3 · 2018-01-31T06:52:51.830

1

By the way how can i change the RunTotal column to the Moving Average column.I want to calculate the average of the total for that line up to that line

for an example for the same values;

+------+--------+----------+-----+
|   ID | Salary |MovingAvg | Flag|/* if the movavg increasing rate higher */
+------+--------+----------+-----+/* than %50 get 1 to flag, else 0 */
| 1    |    100 |      100 |  0  |
| 2    |     40 |       70 |  0  |/* (100+40)/2=70 */
| 3    |     30 |     56.6 |  0  |/* (100+40+30)/3=56.6 */
| 4    |     40 |     52.5 |  0  |/* (100+40+30+40)/4=52.5 */
| 5    |     90 |       60 |  0  |/* (100+40+30+40+90)/5=60 */
| 6    |    270 |       95 |  1  |/*MovAvg increasing rate is more than%50*/
| 7    |     70 |       70 |  0  |/* starting from this line*/
| 8    |     40 |       55 |  0  |/* (70+40)/2 */
| 9    |     10 |       40 |  0  |
| 10   |     60 |       45 |  0  |
| 11   |    300 |       96 |  1  |/*MovAvg increasing rate is more than%50*/
| 12   |     50 |       50 |  0  |
| 13   |     60 |       55 |  0  |/* (50+60)/2 */
+------+--------+----------+-----+

edited Jan 31 '18 at 06:52

answered Jan 30 '18 at 08:33

Gork. O

65
7

when i add new values to table, i got some problem, do you have any idea ? @DineshDB – Gork. O Jan 30 '18 at 08:34
Check the Answer. Hope it helps. – DineshDB Jan 30 '18 at 10:12
that's exactly what I want @DineshDB – Gork. O Jan 30 '18 at 10:22
Happy to help you. Always keep up-voting to encourage the Answers. – DineshDB Jan 30 '18 at 10:25
Nice to meet you @Gork. O. Bye. – DineshDB Jan 30 '18 at 11:00
I'm really sorry about that but i want to ask you something one last time. I have described the last thing wrong to you. I need to calculate break point for averages not for sum's. I updated the table could you please check it? @DineshDB – Gork. O Jan 30 '18 at 11:35
Try my Updated answer. – DineshDB Jan 30 '18 at 11:53
i can not see a difference between updated and old answer. It gives me same result – Gork. O Jan 30 '18 at 12:39
Inside the `IF` condition, I commented one variable and change the `TimesGen=1` from 2. This should make the changes, what you are asking. – DineshDB Jan 31 '18 at 02:05

score 1 · Answer 4 · answered Mar 26 '18 at 15:16

Recursive CTE based answer

select * 
from @T t
order by t.id;
with cteRN as 
( select t.*, ROW_NUMBER() over (order by t.id) as rn 
  from @T t
)
, cte as 
( select t.id, t.salary, t.salary as running, flag = 0, t.rn
  from cteRN t
  where t.rn = 1
  union all 
  select t.id, t.salary
       , case when c.flag = 1 then t.salary + c.salary
              else                 c.running + t.salary
         end 
       , case when t.salary > c.running / 2 then 1
              else 0
         end
       , t.rn
  from cte c 
  join cteRN t
    on t.rn = c.rn + 1
)
select t.id, t.salary, t.running 
from cte t
order by t.id;

id          salary
----------- -----------
1           100
2           40
3           30
4           40
5           90
6           160
7           70
8           40
9           20
10          10
11          200
12          50

id          running     running
----------- ----------- -----------
1           100         100
2           140         140
3           170         170
4           210         210
5           300         300
6           460         460
7           230         230
8           270         270
9           290         290
10          300         300
11          500         500
12          250         250

sql dynamic cumulative sum - SQL Server

4 Answers4