CODEWITHSUNDEEP
sqlsql-serversumwindow-functionscumulative-sum
rachel
rachel

Reputation: 235

Reset Running Total in MS SQL Server based on condition

Calculate running total based on the value plus/minus or another table B (that resets the total) in another column by Account and Date.

Example

TableA - 1st Scenario 

ID     Account  Date         Operation         Qty              Running_Total 
1       A       01/01/2018       plus           10              10
2       A       01/02/2018       plus           20              30 
3       A       01/03/2018       minus           5              20
4       A       01/03/2018       minus           5              20
5       A       01/04/2018       plus           30              50
6       B       01/01/2018       plus           15              15
7       B       01/02/2018       Use Table B    90              40 -- resets 
8       B       01/03/2018       plus           10              50

TableA - 2nd Scenario 

ID     Account  Date         Operation         Qty              Running_Total 
1       A       01/01/2018       plus           10              10
2       A       01/02/2018       plus           20              30 
3       A       01/03/2018       minus           5              20
4       A       01/03/2018       minus           5              20
5       A       01/04/2018       plus           30              50
6       B       01/01/2018       plus           15              15
7       B       01/02/2018       Use Table B    90              40-- resets (currently 80 using below solution)
7       B       01/02/2018       Use Table B    90              40-- resets (currently 80 using below solution)
8       B       01/03/2018       plus           10              50
9       B       01/04/2018       plus           20              75-- new row
10      B       01/04/2018       plus           5               75-- new row

TableB 

ID     Account  Date         RunningQty             
7      B       01/02/2018       40

Original Code: 

select ID, Date, Operation, Total, 
              case when Operation = 'Use Table B' then TableB.RunningTotalQty 
              else
              SUM( case when Operation = 'plus' then  Qty 
                   else case when Operation = 'minus' then -Qty end) 
              OVER (PARTITION BY Account ORDER BY Date)  end
            From TableA A left Join TableB B 
on A.ID = B.ID ...

Solution Applied: (@UZI's Code)

select
ID, Account, Date, Operation, Qty
   , Running_Total = sum(isnull(RunningQty, Qty * iif(Operation = 'plus', 1, -1))) over (partition by grp order by ID)
from (
select 
    a.*, b.RunningQty, grp = sum(isnull(b.RunningQty, 0)) over (order by a.ID)
from 
    @tableA a
    left join @tableB b on a.ID = b.ID and a.Account = b.Account  and a.Date = b.Date
) t

Upvotes: 1

Views: 938

Answers (1)

uzi
uzi

Reputation: 4146

Two running totals might solve your issue:

declare @tableA table (ID int, Account char(1), Date date, Operation varchar(100), Qty int)
insert into @tableA
values 
(1, 'A', '01/01/2018', 'plus', 10)
, (2, 'A', '01/02/2018', 'plus', 20)
, (3, 'A', '01/03/2018', 'minus', 5)
, (4, 'A', '01/03/2018', 'minus', 5)
, (5, 'A', '01/04/2018', 'plus', 30)
, (6, 'B', '01/01/2018', 'plus', 15)
, (7, 'B', '01/02/2018', 'Use Table B', 90)
, (8, 'B', '01/03/2018', 'plus', 10)

declare @tableB table (ID int, Account char(1), Date date, RunningQty int)
insert into @tableB values (7, 'B', '01/02/2018', 40)

select
    ID, Account, Date, Operation, Qty
    , Running_Total = sum(isnull(RunningQty, Qty * iif(Operation = 'plus', 1, -1))) over (partition by grp order by ID)
from (
    select 
        a.*, b.RunningQty, grp = sum(isnull(b.RunningQty, 0)) over (order by a.ID)
    from 
        @tableA a
        left join @tableB b on a.ID = b.ID and a.Account = b.Account  and a.Date = b.Date
) t

Output:

ID  Account Date    Operation   Qty Running_Total
-------------------------------------------------
1   A   2018-01-01  plus        10  10
2   A   2018-02-01  plus        20  30
3   A   2018-03-01  minus       5   25
4   A   2018-03-01  minus       5   20
5   A   2018-04-01  plus        30  50
6   B   2018-01-01  plus        15  65
7   B   2018-02-01  Use Table B 90  40
8   B   2018-03-01  plus        10  50

EDIT: You just need to change first running total a bit. sum() over (order by...) sums by range by default, so you need to change it to add by rows

select
    ID, Account, Date, Operation, Qty
    , Running_Total = sum(isnull(RunningQty, Qty * iif(Operation = 'plus', 1, -1))) over (partition by grp order by ID)
from (
    select 
        a.*, b.RunningQty, grp = sum(isnull(b.RunningQty, 0)) over (order by a.ID rows unbounded preceding)
    from 
        @tableA a
        left join @tableB b on a.ID = b.ID and a.Account = b.Account  and a.Date = b.Date
) t

Upvotes: 1

Related Questions