How to group sets by column value
I found this: How to group / rank records based on the changing column value? which is similar to what I am looking for, but not enough to work 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 RunningTotal
to reset it every time it meets creditRefund
. I looked at the usage OVER(ROWS BETWEEN CURRENT ROW AND x FOLLOWING)
, but it doesn't work as there can be any number of lines in between, depending on what's going on in the account.
So I will need to look 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 have so far:
SELECT Id, UserId, [Type], RunningTotal = SUM(Amount) OVER (ORDER BY t.Id)
FROM Transactions
WHERE UserId = @User
Any ideas on how to do this? I feel like I need to group them in some way so that the total is reset and I can use the suggestion PARTITION BY
. But I couldn't get it to work. If it comes down to it, I think I can do it in C # after it is returned from the db, but I wouldn't want that.
source to share
Using a subquery to identify the start of each group grp
(using only start the group once when there is a sequential one ) and another to create a group number , then using as a section for : lag()
PaymentCC
sumgrp
sumgrp
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 |
+-------+--------+--------------+---------+--------------+----------------+
source to share
You can reset the use case in the current 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 )
source to share