CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Getting previous row Amount field

I have a table with a few columns PersonID, Amount and StartDate, have provided 1 person as a sample. What I'm trying to do is calculate the BeforeAmountCalculatedColumn automatically, basically picking up what the previous rows (by date) amount was. How could I do this? 

Create Table #Results
(
    PersonID int,
    Amount money,
    StartDate datetime,
    BeforeAmountCalculatedColumn money
)

insert into #Results
(
    PersonID,
    Amount,
    StartDate,
    BeforeAmountCalculatedColumn
)
select
    1,
    '163.45',
    '30 Jan 2019',
    '202.70'
union all
select
    1,
    '202.70',
    '23 Nov 2018',
    '189.45'
union all
select
    1,
    '189.45',
    '28 Aug 2018',
    '0'

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Are you looking for lag()?

select r.*,
       lag(amount) over (partition by personid order by startdate) as BeforeAmountCalculatedColumn
from #Results r;

Upvotes: 2

Related Questions