Shash Shash - 5 months ago 8
SQL Question

How to Sum the 1st record of one column with the 2nd record of another column?

I am trying the Sum the 2nd record of one column with the 1st record of another column and store the result in a new column

Here is the example SQL Server table

Emp_Code Emp_Name Month Opening_Balance
G101 Sam 1 1000
G102 James 2 -2500
G103 David 3 3000
G104 Paul 4 1800
G105 Tom 5 -1500


I am trying to get the output as below on the new
Reserve
column

Emp_Code Emp_Name Month Opening_Balance Reserve
G101 Sam 1 1000 1000
G102 James 2 -2500 -1500
G103 David 3 3000 1500
G104 Paul 4 1800 3300
G105 Tom 5 -1500 1800


Actually the rule for calculating the
Reserve
column is that


  1. For
    Month-1
    it's the same as
    Opening Balance

  2. For rest of the months its
    Reserve for Month-2
    =
    Reserve for Month-1
    +
    Opening Balance for Month-2


Answer

You seem to want a cumulative sum. In SQL Server 2012+, you would do:

select t.*,
       sum(opening_balance) over (order by [Month]) as Reserve
from t;

In earlier versions, you would do this with a correlated subquery or apply:

select t.*,
       (select sum(t2.opening_balance) from t t2 where t2.[Month] <= t.[Month]) as reserve
from t;