CODEWITHSUNDEEP
sqlsql-servert-sql
Derk Arts
Derk Arts

Reputation: 3460

Fastest way to check if the the most recent result for a patient has a certain value

Mssql < 2005

I have a complex database with lots of tables, but for now only the patient table and the measurements table matter.

What I need is the number of patient where the most recent value of 'code' matches a certain value. Also, datemeasurement has to be after '2012-04-01'. I have fixed this in two different ways:

SELECT
COUNT(P.patid)
FROM T_Patients P
WHERE P.patid IN (SELECT patid
                        FROM T_Measurements M WHERE (M.code ='xxxx'  AND result= 'xx')  
                AND datemeasurement = 
                    (SELECT MAX(datemeasurement) FROM T_Measurements 
                     WHERE datemeasurement > '2012-01-04' AND patid = M.patid
                     GROUP BY patid
                        GROUP by patid)

AND:

SELECT
            COUNT(P.patid)
            FROM T_Patient P


WHERE 1 = (SELECT TOP 1 case when result = 'xx' then 1 else 0 end 
        FROM T_Measurements M 
    WHERE (M.code ='xxxx')  AND datemeasurement > '2012-01-04' AND patid = P.patid
    ORDER by datemeasurement DESC
)

This works just fine, but it makes the query incredibly slow because it has to join the outer table on the subquery (if you know what I mean). The query takes 10 seconds without the most recent check, and 3 minutes with the most recent check.

I'm pretty sure this can be done a lot more efficient, so please enlighten me if you will :).

I tried implementing HAVING datemeasurment=MAX(datemeasurement) but that keeps throwing errors at me.

Upvotes: 0

Views: 104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The fastest way may be to use row_number():

SELECT COUNT(m.patid)
from (select m.*,
             ROW_NUMBER() over (partition by patid order by datemeasurement desc) as seqnum
      FROM T_Measurements m
      where datemeasurement > '2012-01-04'
     ) m
where seqnum = 1 and code = 'XXX' and result = 'xx'

Row_number() enumerates the records for each patient, so the most recent gets a value of 1. The result is just a selection.

Upvotes: 1

Kyle Hale
Kyle Hale

Reputation: 8120

So my approach would be to write a query just getting all the last patient results since 01-04-2012, and then filtering that for your codes and results. So something like

select 
    count(1) 
from
    T_Measurements M 
    inner join (
            SELECT PATID, MAX(datemeasurement) as lastMeasuredDate from
            T_Measurements M
            where datemeasurement > '01-04-2012'
            group by patID
            ) lastMeasurements 
    on lastMeasurements.lastmeasuredDate = M.datemeasurement
       and lastMeasurements.PatID = M.PatID
where
    M.Code = 'Xxxx' and M.result = 'XX'

Upvotes: 1

Related Questions