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.

The results would then display as

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

Answer

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);

Advertisement

Answer