# 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_valueFROM #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)