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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy