How do I group sets based on column value

Question

I found this: How to group/rank records based on a changing value of a column? which is similar to what I'm looking for but isn't quite working the way I need it to.

Basically, I have data that looks like this:

Id          UserId      Type            Amount   RunningTotal
----------- ----------- --------------- -------- -------------
2759        750         charge          -50.00   -50.00
2760        750         charge          -125.00  -175.00
4308        750         paymentGC       50.00    -125.00
4309        750         paymentGC       125.00   0.00
19916       750         charge          -1.00    -1.00
19917       750         creditRefund    124.00   123.00
23238       750         paymentCC       50.00    173.00
23239       750         paymentCC       125.00   298.00
23240       750         charge          -50.00   248.00
23241       750         charge          -125.00  123.00
41300       750         creditRefund    125.00   248.00
42054       750         paymentCC       50.00    298.00
42055       750         paymentCC       125.00   423.00
42056       750         charge          -50.00   373.00
42057       750         charge          -125.00  248.00
56983       750         creditRefund    125.00   373.00
63083       750         paymentCC       50.00    423.00
63084       750         paymentCC       125.00   548.00
63085       750         charge          -50.00   498.00
63086       750         charge          -125.00  373.00
80829       750         creditRefund    125.00   498.00

This works, but I need the RunningTotal to reset each time it encounters creditRefund. I looked at using OVER(ROWS BETWEEN CURRENT ROW AND x FOLLOWING) but that won't work since there can be any number of rows between, depending on what happens on the account.

So I would need it to look more like this:

Id          UserId      Type            Amount   RunningTotal
----------- ----------- --------------- -------- -------------
2759        750         charge          -50.00   -50.00
2760        750         charge          -125.00  -175.00
4308        750         paymentGC       50.00    -125.00
4309        750         paymentGC       125.00   0.00
19916       750         charge          -1.00    -1.00
19917       750         creditRefund    124.00   123.00
23238       750         paymentCC       50.00    50.00
23239       750         paymentCC       125.00   175.00
23240       750         charge          -50.00   125.00
23241       750         charge          -125.00  0.00
41300       750         creditRefund    125.00   125.00
42054       750         paymentCC       50.00    50.00
42055       750         paymentCC       125.00   175.00
42056       750         charge          -50.00   125.00
42057       750         charge          -125.00  0.00
56983       750         creditRefund    125.00   125.00
63083       750         paymentCC       50.00    50.00
63084       750         paymentCC       125.00   175.00
63085       750         charge          -50.00   125.00
63086       750         charge          -125.00  0.00
80829       750         creditRefund    125.00   125.00

Here's what I've got so far:

SELECT Id, UserId, [Type], RunningTotal = SUM(Amount) OVER (ORDER BY t.Id)
FROM Transactions
WHERE UserId = @User

Any ideas on how to accomplish this? I feel like I need to group them in some way so that the running total resets and I can use the PARTITION BY clause. But I haven't been able to get it to work. If it comes down to it, I guess I can do it in C# after it gets returned from the db, but I'd rather not have to.

Answer 1

You can reset using case in the running total

;with cte as (
select *, sum(amount) over(order by id) RowSum, case when [Type]='creditRefund' Then 1 else 0 end as Num from #yourtable
)
, cte2 as ( SELECT *, sum(num) over (order by id) ResetFlag from cte )
select *, sum(case when [Type]='creditRefund' Then 0 else Amount END) over(partition by ResetFlag order by id) from cte2


create table #yourtable ( id int, userid int, type varchar(20), amount float)

insert into #yourtable (
Id      ,    UserId  ,    Type      ,      Amount ) values
----------- ----------- --------------- -------- -------------
 (2759  ,      750      ,   'charge      ', -50.00  )
,(2760  ,      750      ,   'charge      ', -125.00 )
,(4308  ,      750      ,   'paymentGC   ', 50.00   )
,(4309  ,      750      ,   'paymentGC   ', 125.00  )
,(19916 ,      750      ,   'charge      ', -1.00   )
,(19917 ,      750      ,   'creditRefund', 124.00  )
,(23238 ,      750      ,   'paymentCC   ', 50.00   )
,(23239 ,      750      ,   'paymentCC   ', 125.00  )
,(23240 ,      750      ,   'charge      ', -50.00  )
,(23241 ,      750      ,   'charge      ', -125.00 )
,(41300 ,      750      ,   'creditRefund', 125.00  )
,(42054 ,      750      ,   'paymentCC   ', 50.00   )
,(42055 ,      750      ,   'paymentCC   ', 125.00  )
,(42056 ,      750      ,   'charge      ', -50.00  )
,(42057 ,      750      ,   'charge      ', -125.00 )
,(56983 ,      750      ,   'creditRefund', 125.00  )
,(63083 ,      750      ,   'paymentCC   ', 50.00   )
,(63084 ,      750      ,   'paymentCC   ', 125.00  )
,(63085 ,      750      ,   'charge      ', -50.00  )
,(63086 ,      750      ,   'charge      ', -125.00 )
,(80829 ,      750      ,   'creditRefund', 125.00  )

Answer 2

Using a subquery to identify the start of each group grp (using lag() to only start a group once when there are sequential PaymentCC), and another to generate the group number sumgrp, and then using the sumgrp as a partition for the RunningTotal:

select 
    Id
  , UserId
  , Type
  , Amount
  , RunningTotal = sum(amount) over (partition by userid, sumgrp order by id)
  , desired_result
from (
  select *
      , sumgrp = sum(grp) over (
          partition by userid 
          order by id
        )
  from (
    select * 
      , grp = (case when type='PaymentCC' 
              and isnull(lag(type) over (
                partition by userid 
                order by id
                ),'') <> 'PaymentCC' 
            then 1 
          else 0 end) 
    from Transactions
    ) as g
) as s
where UserId = 750

rextester demo: http://rextester.com/POX67852

returns:

+-------+--------+--------------+---------+--------------+----------------+
|  Id   | UserId |     Type     | Amount  | RunningTotal | desired_result |
+-------+--------+--------------+---------+--------------+----------------+
|  2759 |    750 | charge       | -50.00  | -50.00       | -50.00         |
|  2760 |    750 | charge       | -125.00 | -175.00      | -175.00        |
|  4308 |    750 | paymentGC    | 50.00   | -125.00      | -125.00        |
|  4309 |    750 | paymentGC    | 125.00  | 0.00         | 0.00           |
| 19916 |    750 | charge       | -1.00   | -1.00        | -1.00          |
| 19917 |    750 | creditRefund | 124.00  | 123.00       | 123.00         |
| 23238 |    750 | paymentCC    | 50.00   | 50.00        | 50.00          |
| 23239 |    750 | paymentCC    | 125.00  | 175.00       | 175.00         |
| 23240 |    750 | charge       | -50.00  | 125.00       | 125.00         |
| 23241 |    750 | charge       | -125.00 | 0.00         | 0.00           |
| 41300 |    750 | creditRefund | 125.00  | 125.00       | 125.00         |
| 42054 |    750 | paymentCC    | 50.00   | 50.00        | 50.00          |
| 42055 |    750 | paymentCC    | 125.00  | 175.00       | 175.00         |
| 42056 |    750 | charge       | -50.00  | 125.00       | 125.00         |
| 42057 |    750 | charge       | -125.00 | 0.00         | 0.00           |
| 56983 |    750 | creditRefund | 125.00  | 125.00       | 125.00         |
| 63083 |    750 | paymentCC    | 50.00   | 50.00        | 50.00          |
| 63084 |    750 | paymentCC    | 125.00  | 175.00       | 175.00         |
| 63085 |    750 | charge       | -50.00  | 125.00       | 125.00         |
| 63086 |    750 | charge       | -125.00 | 0.00         | 0.00           |
| 80829 |    750 | creditRefund | 125.00  | 125.00       | 125.00         |
+-------+--------+--------------+---------+--------------+----------------+