Access Previous Value of Row and use it in Calculations of SQL query

Question

I have a table

ID  OpeningBal      Type
1   1000             -
2   100             IN
3   200             OUT
4   100             OUT
5   300             IN

I want Output in sql query without lag function like this

ID  OpeningBal      Type    Closing Bal
1   1000             -             0
2   100             IN           1100
3   200             OUT          900
4   100             OUT          800
5   300             IN           1100

What is the reason for not wanting lag, is it because you have an older version of sql-server ? If you include the version, this would make more sense — t-clausen.dk, Aug 07 '15 at 08:26
What did you try ? I would have expected 1000 as the value for Closing Bal with ID 1 — t-clausen.dk, Aug 07 '15 at 08:30
It looks like running sum. In SQL Server 2012+ it is done by `SUM() OVER()`. — Vladimir Baranov, Aug 07 '15 at 11:09
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — James Z, Aug 08 '15 at 06:58

score 1 · Answer 1 · answered Aug 07 '15 at 12:32

1

This will work with sql-server 2012:

DECLARE @t 
  table(ID int, OpeningBal int, Type varchar(3))
INSERT @t values
(1,1000,'-'),(2,100,'IN'),(3,200,'OUT'),(4,100,'OUT'),(5,300,'IN')

SELECT 
  *, 
  CASE WHEN Type = '-' 
    THEN 0 
  ELSE 
    sum(case when Type = 'Out' THEN -OpeningBal ELSE OpeningBal END) over (order by id) 
  END ClosingBal
FROM @t

Result:

ID   OpeningBal   Type  ClosingBal
1    1000         -              0
2     100         IN          1100
3     200         OUT          900
4     100         OUT          800
5     300         IN          1100

answered Aug 07 '15 at 12:32

t-clausen.dk

43,517
12
59
92

Super Sir your are great :) could you please little explain `THEN -OpeningBal ELSE OpeningBal END` – wiretext Aug 07 '15 at 12:41
it is just a simple [CASE](http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(CASE_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true) statement to decide whether to add or subtract – t-clausen.dk Aug 07 '15 at 12:52
how it Add and Subtract previous row because it is not belong to that row – wiretext Aug 07 '15 at 12:56
1

@tinka That is a running total, so it calculates all the rows up to that point, not just the previous one. You might want to lookup a tutorial / video on SQL Server window functions, for example https://www.youtube.com/watch?v=tUK3ohs7uDI – James Z Aug 08 '15 at 06:55

Access Previous Value of Row and use it in Calculations of SQL query

1 Answers1