CODEWITHSUNDEEP
sqlsql-servert-sql
shamim
shamim

Reputation: 6778

How to minus current and previous value in SQL Server

Have one table, need to minus one column previous and current amount. Table value is below, need to write syntax for Cal-Amount column

Id Amount Cal-Amount
 1  100   0
 2  200   0
 3  400   0
 4  500   0

Cal-Amount calculation formula with sample value

Id Amount Cal-Amount
 1  100   (0-100)=100
 2  200   (100-200)=100
 3  400   (200-400)=200
 4  500   (400-500)=100

Need SQL syntax to minus column current and previous value

Upvotes: 0

Views: 595

Answers (5)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You can use the LAG function if your SQL Server >= 2012

declare @t table (id int, amount1 int)

insert into @t
values (1, 100), (2, 200), (3, 400), (4, 500)

select
    *, amount1 - LAG(amount1, 1, 0) over (order by id) as CalAmount 
from 
    @t

Upvotes: 1

KeithL
KeithL

Reputation: 5594

SQL Server 2012 or newer:

Select 
    ID, Amount, [Cal-Amount] = Amount - LAG(Amount, 1, 0) OVER (ORDER BY Id)
From 
    table

or 

Select 
    current.ID, Current.Amount, Current.Amount - Isnull(Prior.Amount, 0)
from 
    table current 
left join 
    table prior on current.id - 1 = prior.id

Upvotes: 2

George Menoutis
George Menoutis

Reputation: 7260

Well, Tim beat me to the lag(), so here's the old-school using join:

select t.Id,t.Amount,t.Amount-isnull(t2.Amount,0) AS [Cal-Amount]
from yourtable t
left join yourtable t2 on t.id=t2.id+1

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

LAG is one option if you are using SQL Server 2012 or later:

SELECT
    Id,
    Amount,
    LAG(Amount, 1, 0) OVER (ORDER BY Id) - Amount AS [Cal-Amount]
FROM yourTable;

If you are using an earlier version of SQL Server, then we can use a self join:

SELECT
    Id,
    Amount,
    COALESCE(t2.Amount, 0) - t1.Amount AS [Cal-Amount]
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t1.Id = t2.Id + 1;

But note that the self join option might only work if the Id values are continuous. LAG is probably the most efficient way to do this, and is also robust to non sequential Id values, so long as the order is correct.

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use apply :

select t.*, t.Amount - coalesce(tt.Amount, 0) as CalAmount
from table t outer apply (
     select top (1) *
     from table t1
     where t1.id < t.id
     order by t1.id desc
) tt;

Upvotes: 0

Related Questions