Closest to date within 3 days

Question

Post reply

Closest to date within 3 days

boehnc

Ten Centuries

Points: 1001
More actions
October 9, 2021 at 3:32 pm

#3939125

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 100952 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Phil Parkin SSC Guru Points: 246723 More actions · Answer 4

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.