CODEWITHSUNDEEP
sqlsql-server
larug
larug

Reputation: 39

SQL cumulative sum until a flag value and resetting the sum

I'm still learning SQL and I'm trying to figure out a problem that I wasn't able to solve. So my problem is that I'm trying to select a table(let say Expense), ordered by date and in the table I have a column named Charged and I want to add charges to be cumulative(This part I figured out). However after that I have another column that will be acting as a flag called PayOut. When the PayOut value is 1 I want the summation of Charged(SumValue) to reset to zero. How would I do this? Here is what I have tried and the current output I get and what output I want. Note: I saw some posts using CTE's but wasn't the same scenario and more complex.

select ex.date, 
       ex.Charged,
       (case when(ex.PayOut=1) then 0 
             else sum(ex.Charged) over (order by ex.date)end) as SumValue, 
      ex.PayOut
from Expense ex 
order by ex.date asc

The data looks like this 

Date       Charged   PayOut
01/10/2018   10        0
01/20/2018   5         0
01/30/2018   3         0
02/01/2018   0         1
02/11/2018   12        0
02/21/2018   15        0

Output I get

Date       Charged   PayOut  SumValue
01/10/2018   10        0        10
01/20/2018   5         0        15
01/30/2018   3         0        18
02/01/2018   0         1        0
02/11/2018   12        0        30
02/21/2018   15        0        45

Output Wanted

Date       Charged   PayOut  SumValue
01/10/2018   10        0        10
01/20/2018   5         0        15
01/30/2018   3         0        18
02/01/2018   0         1        0
02/11/2018   12        0        12
02/21/2018   15        0        27

Upvotes: 2

Views: 2376

Answers (1)

Alex Sham
Alex Sham

Reputation: 468

Just create group from your PayOut Column and use it as a partition in OVER

WITH Expense AS (
              SELECT CAST('01/10/2018' AS DATE) AS Date, 10 AS Charged, 0 AS PayOut
    UNION ALL SELECT CAST('01/20/2018' AS DATE), 5, 0
    UNION ALL SELECT CAST('01/30/2018' AS DATE), 3, 0
    UNION ALL SELECT CAST('02/01/2018' AS DATE), 0, 1
    UNION ALL SELECT CAST('02/11/2018' AS DATE), 12, 0
    UNION ALL SELECT CAST('02/21/2018' AS DATE), 15, 0
)
SELECT
     dat.date
    ,dat.Charged
    ,dat.PayOut
    ,dat.PayOutGroup
    ,SUM(dat.Charged) OVER (PARTITION BY dat.PayOutGroup ORDER BY dat.date) as SumValue
FROM (
    SELECT 
         e.date
        ,e.Charged
        ,e.PayOut
        ,SUM(e.PayOut) OVER (ORDER BY e.date) AS PayOutGroup
    FROM Expense e
) dat

Upvotes: 7

Related Questions