CODEWITHSUNDEEP
sqlsql-serverconditional-statementspartitioningcumulative-sum
rachel
rachel

Reputation: 235

Calculate Running total in a new column based Adding or Subtracting condition using SQL

I am trying to calculate running total based on the value plus/minus in another column by Account and Date.

Example 

Data 
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

the total

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 ...

Upvotes: 1

Views: 907

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

The case goes inside the sum():

select ID, Date, Operation, Total, 
       sum(case when Operation = 'plus' then qty else - qty end) over
           (partition by Account order by Date) as Running_Total
From TableA  ;

This assumes only two operations. If you have more:

select ID, Date, Operation, Total, 
       sum(case when Operation = 'plus' then qty
                then Operation = 'minus' then - qty
                else 0
           end) over
           (partition by Account order by Date) as Running_Total
From TableA  ;

Upvotes: 2

Related Questions