Find row closest to date

  • boehnc

    SSC Eights!

    Points: 809

    Just having trouble getting this to work as I hoped. Trying to extract entire row from one table with a date closest and before another date in the 1st table.  Sample table and 1st try below. Any help is appreciated

    Trying to get:

    1,    2020-08-05,  2020-08-03,   25

    2,    2020-08-04,  2020-08-03,   34

    3,   2020-07-28,     2020-07-26,   34

    create table #Colo

    (Colo_ID int

    ,Colo_Procdate date

    )

    create table #Res

    (Res_id int

    ,Res_value int

    ,Res_Date date)

    Insert into #Colo

    values

    (1, '2020-08-05')

    ,(2, '2020-08-04')

    ,(3, '2020-07-28')

    Insert into #Res

    values

    (1, 25, '2020-08-03')

    ,(1, 24, '2020-08-02')

    ,(1, 35, '2020-08-01')

    ,(2, 34, '2020-08-03')

    ,(2, 33, '2020-08-01')

    ,(2, 32, '2020-07-05')

    ,(3, 34, '2020-07-26')

    ,(3, 14, '2020-06-25')

    ,(3, 23, '2020-08-22')

    ,(3, 45, '2020-08-04')

    Select

    c.Colo_ID

    ,c.Colo_Procdate

    ,x.Res_Date

    ,x.Res_value

    from #colo c

    left join ( select r.Res_id, r.Res_Date, r.Res_value

    from #Res r

    inner join (select r2.Res_id, max(r2.Res_Date) Date

    from #Res r2

    group by r2.Res_id) res2 on res2.Res_id=r.Res_id and res2.Date=r.Res_Date

    ) x

    on x.Res_id=c.Colo_ID and x.Res_Date<c.Colo_Procdate

     

     

     

  • Ken McKelvey

    SSCoach

    Points: 18329

    SELECT C.Colo_ID, C.Colo_Procdate, X.Res_Date, X.Res_value
    FROM #Colo C
    CROSS APPLY
    (
    SELECT TOP(1) R.Res_Date, R.Res_value
    FROM #Res R
    WHERE R.Res_id = C.Colo_ID
    AND R.Res_Date <= C.Colo_Procdate
    ORDER BY R.Res_date DESC
    ) X;
  • boehnc

    SSC Eights!

    Points: 809

    this is great, Ken. Works as planned, however it's taking about an hour to complete. Anyway I can possibly optimize this? Thanks again for your help.

  • pietlinden

    SSC Guru

    Points: 62904

    It would likely depend on how the indexes on the tables in the query.

    How many records are you talking about?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply