Runing Total sum minus different condition

Question

I looked at some SQL Server running total examples, but I can't manage thing like this.

I have a table where I have columns id, name, type of operation, date, value. I want to calculate balance for each record. Balance should be calculated like this:

Starting balance must be 0 and then if operation type is IN there will be plus, if operation is OUT there will be minus. Each next record should see previous record balance and then +value or -value depending on operation Type.

This operation should be ordered by date (not Id).

For example, if the table looks like this:

ID Name  Op_Type Date       Value
1  box   Out     2017-05-13 15
2  table In      2017-04-31 65
3  box2  In      2017-05-31 65

then result should look like this

ID Name  Op_Type Date       Value Balance
2  table In      2017-04-31 65     65
1  box   Out     2017-05-13 15     50
3  box2  In      2017-05-31 65     115

result of this code :

select *,
       sum(case when Op_Type = 'Out' then -Value else Value end)Over(Order by [Date]) as Balance
From Yourtable

is:

ID  Date        Type        Value       Balance
143 2016-12-31  In          980         664.75
89  2016-12-31  Out         300         664.75
90  2016-12-31  Out         80          664.75

But I expect the following result:

ID  Date        Type        Value       Balance
143 2016-12-31  In          980         980
89  2016-12-31  Out         300         680
90  2016-12-31  Out         80          600

Please use punctuation, this question is one long sentence, which is difficult to read. Have you tried anything? There are many tutorials online on how to calculate running totals and conditional aggregation. Also, which version of sql-server do you use? — HoneyBadger, Jun 22 '17 at 08:41
yah i have tried somthing but in most case i am getting wrong numbers in some rows or somthing like that :/ and it's not calculating with prev rows — Wuhu, Jun 22 '17 at 08:43
@Wuhu - All the dates are same. What is the result you are expecting / — Pரதீப், Jun 22 '17 at 10:33
How you are getting that order ? All the dates are same right — Pரதீப், Jun 22 '17 at 10:38
it is example there will be datee changed like 2017-05-03 this it is just ordering by date 3 record change with 2017-05-03 it is just first 3 records for example — Wuhu, Jun 22 '17 at 10:49
The solution suggested by @Prdp only works if the `order by` attribute (combination) is *unique*. Your `Date` column is not unique, so the result is unpredictable. It's important here to remember that data stored in a table in inherently unsorted, so the order you see doesn't mean anything if you haven't specified an `order by` — HoneyBadger, Jun 22 '17 at 12:57
@Wuhu - In database you can never say *just first 3 records*. To identify first 3 records there should be some order. — Pரதீப், Jun 22 '17 at 13:08
you don't get i don't meaning first 3 recored it is just examplee mann just example .... the result of query there is tones of recoreds and just i give you 3 for examplee — Wuhu, Jun 22 '17 at 13:12

Vladimir Baranov · Accepted Answer · 2017-06-22T13:52:06.193

1

The problem with answer by Prdp is that SUM(...) OVER (ORDER BY ...) by default uses RANGE option instead of ROW.

This is why you see unexpected results when dates are not unique. This is how the default RANGE option works.

To get results that you expect spell it our explicitly:

SELECT
    *
    ,SUM(CASE WHEN Op_Type = 'Out' 
        THEN -Value ELSE Value END)
    OVER(ORDER BY [Date], Op_Type, ID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance
FROM YourTable
ORDER BY [Date], Op_Type, ID;

I also added Op_Type into the ORDER BY to add positive values first in cases when there are several rows with the same date.

I added ID into the ORDER BY to make results stable in cases when there are several rows with the same date.

edited Jun 22 '17 at 13:52

answered Jun 22 '17 at 13:36

Vladimir Baranov

31,799
5
53
90

1 question it is work fine but 1 question i have when i am pointing time period like SELECT * ,SUM(CASE WHEN Op_Type = 'Out' THEN -Value ELSE Value END) OVER(ORDER BY [Date], Op_Type, ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance FROM YourTable ORDER BY [Date], Op_Type, ID where date>=2017-01-05 AND date<=2017-01-17 it is not starting calculating balance from bigining of the time it is starting from 2017-01-05 i want to display this period but calc from begining – Wuhu Jun 22 '17 at 14:21
@Wuhu, essentially you need to run two queries. First will be a simple `SUM` (not running total) from beginning of time till `2017-01-05`. Remember the calculated sum and then add it to each row in the main query with the running total. – Vladimir Baranov Jun 22 '17 at 14:24

Runing Total sum minus different condition

1 Answers1