CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Get Row based off Date and previous row

I've got the following sample data and what I'm trying to do is based off the DateChanged value is get the appropriate Amount from the linked table along with the previous row.

enter image description here

The results would then display as

enter image description here

How could this be done? What I have so far is this but wondering is there's a better way

 select
    *
from 
    #temp t
Inner Join 
(
    select 
        TempID,
        Amount,
        StartDate,
        isnull(EndDate, getdate()) as EndDate,
        LAG(Amount, 1)  OVER(partition by TempID ORDER BY row_num)  PrevValue2,
        LEAD(Amount, 1) OVER(partition by TempID ORDER BY row_num)  NextValue2
    from 
    (
      SELECT ROW_NUMBER() OVER (partition by TempID Order by TempID) row_num,
             TempAmountsID,
             TempID,
             StartDate,
             EndDate,
             Amount
      FROM   #tempAmounts
    ) t1
) t2 on 
    t.TempID = t2.TempID and
    t.DateChanged between t2.StartDate and t2.EndDate

Create Table #temp
(
    TempID int,  
    Name varchar(500),
    DateChanged date
)
insert into #temp
(
    TempID,
    Name,
    DateChanged
)
select
    901,
    'Philip',
    '8 Nov 2020'
union
select
    902,
    'John',
    '12 Nov 2020'

Create Table #tempAmounts
(
    TempAmountsID int,
    TempID int,
    StartDate date,
    EndDate date NULL,
    Amount money
)

insert into #tempAmounts
(
    TempAmountsID,
    TempID,
    StartDate,
    EndDate,
    Amount
)
select
    1,
    901,
    '01 Oct 2020',
    '5 Nov 2020',
    100
union all
select
    2,
    901,
    '6 Nov 2020',
    NULL,
    105
union all
select
    3,
    902,
    '01 Sep 2020',
    '08 Nov 2020',
    200
union all
select
    4,
    902,
    '09 Nov 2020',
    '17 Nov 2020',
    205

select * from #temp
select * from #tempAmounts

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use lag() and join:

select t.*, ta.amount, ta.prev_amount
from #temp t left join
     (select ta.*,
             lag(ta.amount) over (partition by ta.tempid order by ta.startdate) as prev_amount
      from #tempAmounts ta
     ) ta
     on t.tempid = ta.tempid and
        t.datechanged >= ta.startdate and
        (t.datechanged <= ta.enddate or ta.enddate is null);

Upvotes: 2

user9601310
user9601310

Reputation: 1086

This works for the example provided:

select a.*, b.amount , c.amount as previousamount
from #temp a
left join #tempamounts b on a.tempid = b.tempid 
       and b.startdate = (select max(startdate) from #tempamounts 
          where tempid = a.tempid and startdate < a.datechanged)
left join #tempamounts c on a.tempid = c.tempid
      and c.startdate = (select max(startdate) from #tempamounts 
          where tempid = a.tempid and startdate < b.startdate)

Upvotes: 0

Related Questions