October 9, 2021 at 3:32 pm
I think I'm close but not close enough...and preferably, I'd like to use something different than an outer apply unless this can be optimized. This is the bottleneck of my larger query. Would love to see any other suggestions (left joins, cte's)? Appreciate your help.
Need the closest #value 'Proc_Val_date' to each row in the #pat table (before and within 3 days of the 'ProcDate'
IF OBJECT_ID('TempDB..#pat','U') IS NOT NULL
DROP TABLE #pat
create table #Pat
(PatID varchar(10)
,ProcNm varchar(10)
,ProcDate date
)
IF OBJECT_ID('TempDB..#value','U') IS NOT NULL
DROP TABLE #value
create table #value
(PatID varchar(10)
,ProcVal int
,ProcValDate date)
Insert into #Pat
values
(15,'TTK', '2020-08-05')
,(15, 'TTK','2020-08-04')
,(15, 'TTR', '2020-07-28')
,(10,'RRS', '2020-09-06')
,(10, 'RTS','2020-10-04')
,(10, 'RTS', '2020-11-28')
Insert into #value
values
(15, 25, '2020-08-03')
,(15, 24, '2020-08-02')
,(15, 35, '2020-07-27')
,(15, 34, '2020-07-24')
,(15, 33, '2020-08-01')
,(10, 32, '2020-07-05')
,(10, 34, '2020-09-05')
,(10, 14, '2020-09-04')
,(10, 23, '2020-11-25')
,(10, 45, '2020-12-04')
Select
p.PatID
,p.ProcDate
,p.ProcNm
,val.ProcValDate
,val.ProcVal
from #Pat p
outer apply (select top 1 val.ProcVal, val.ProcValDate
from #value val
where val.PatID = p.PatID and val.ProcValDate<p.ProcDate
and DATEDIFF(dd,p.ProcDate, val.ProcValDate)<=3
order by val.ProcValDate desc
) val
October 10, 2021 at 2:57 am
This was removed by the editor as SPAM
October 10, 2021 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 11, 2021 at 11:01 am
I think OUTER APPLY is the way to go.
Here is another way to write it, also using OUTER APPLY:
SELECT p.PatID,
p.ProcDate,
p.ProcNm,
val.ProcValDate,
val.ProcVal
FROM #Pat p
OUTER APPLY (SELECT ROW_NUMBER() OVER (PARTITION BY val.PatID ORDER BY val.ProcValDate DESC) rn,
val.ProcVal,
val.ProcValDate
FROM #value val
WHERE val.PatID = p.PatID
AND val.ProcValDate < p.ProcDate
AND DATEDIFF(dd, p.ProcDate, val.ProcValDate) <= 3) val
WHERE rn=1 OR rn IS NULL;
The only other way I can think of getting the results is by using a cursor, which I wouldn't recommend and would almost certainly be much slower.
I would look at adding indexes to make your the query faster.
October 11, 2021 at 1:54 pm
I too don't think OUTER APPLY is the main problem. But I would strongly urge you to get rid of the function against the table column you are searching on, because it's always a bad idea to use functions on the column being searched on (in the WHERE; btw, also applies to columns in a JOIN clause).
You'll need an index on #val ( PatID, ProcValDate ); if the actual code uses a temp table, make it the clustered index.
outer apply (select top (1) val.ProcVal, val.ProcValDate
from #value val
where val.PatID = p.PatID
and val.ProcValDate between dateadd(day, -3, p.ProcDate) and dateadd(day, -1, p.ProcDate)
order by val.ProcValDate desc
) val
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2021 at 3:00 pm
I assume you're using OUTER APPLY because the val.* results can sometimes be NULL. But if there is always a match in val, switching to CROSS APPLY is likely to give a performance boost.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply