Any brilliant minds for this sql question (1 Viewer)

[OuterApply]

I'm going to have a dataset in a temp table with columns:

PatientID (varchar)
LabValue (float)
CollectionDateTime (datetime)

A patient ID may repeat 1, 5, or 100 times in this table, with various collection dates and lab values.

I need to end up with the following information:

For each distinct patient ID, I need to determine (Y/N) whether or not at any point in time, the LabValue increased by .3 in 48 hours or 50% in 7 days.

I'm pretty confident I can figure out how to do this with a cursor or two and a loop.

However, I'm wondering if there is any super ultra brilliant person who can think of a way to do it set-based & no loop etc. If so I will be very impressed thank you!

 

[plog]

Set based? You mean via SQL only? Here's one method--

Create a query based on your temp table (Alias it A) with all your fields in the SELECT. Create a SELECT field that is a query that returns the absolute value of the largest difference between the A record LabValue and its LabValue for all records within 48 ours of the A record's CollectionDateTime.

Something like so (I'm freestyling SQL here, its gonna be wrong, but the idea is sound):

SELECT A.PatientID, A.LabValue, A.CollectionDateTime
  , (SELECT MAX(ABS(A.LabValue - B.LabValue)) FROM YourTable AS B
    WHERE B.PatientID = A.PAtientID AND ABS(A.CollectionDateTime - B.CollectionDateTime)<2
  ) AS Range2Days
  , (SELECT MAX(ABS(A.LabValue - B.LabValue)) FROM YourTable AS B
    WHERE B.PatientID = A.PAtientID AND ABS(A.CollectionDateTime - B.CollectionDateTime)<7
  ) AS Range7Days
FROM YourTable AS B

Then you build a Totals query on top of that to find those with the appropriate thresholds for your RangeDays fields.

 

[OuterApply]

I just realized I can just make a correlated subquery like

case when exists (select 1 from sametable t where t.id=maintable.id and date is within range and value is greater such and such) then y else n
and then aggregate them on max y/n

 

[OuterApply]

Awesome! I just saw your post after I posted mine - thank you - that looks like it will work.

Not sure why at first I thought this was so complicated but now it seems simpler huh - sometimes just asking the question is helpful.

Thanks again

 

[Pat Hartman]

@plog Is there a typo in your query? Should the last line be:

FROM YourTable AS A

 

[plog]

I'm freestyling SQL here, its gonna be wrong, but the idea is sound

I'd still be surprised if that was the only one.

 

[OuterApply]

Thanks again for jogging my brain.

I did end up doing something like:

case
    when exists (select 1 from labdetails LaterLab where LaterLab.id=mainLab.id
            and LaterLab.collectiondate between MainLab.collectiondate and dateadd(hour,48,mainLab.collectiondate)
            and LaterLab.LabValue>=(mainLab.labvalue*1.3)
            )
    or exists()...the second thing which was similar, .5 in 7 days
    then 1
    else 0
end as Result

...

from labdetails mainLab

and then

aggregating them by patient id, with a case when sum(result)>0 then Y else N

Thanks again