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
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;
September 23, 2020 at 3:34 pm
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.
September 23, 2020 at 5:36 pm
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