Clever Geek Handbook

Selecting the first and last test in the Labtests group

I have a PatientLab table i have a test group as shown below

patientid  labtest       dateordered    result
100          cd4         1/1/14          500
100          cd4         1/4/14          200
100          cd4         1/5/15          800
100          vl          1/4/14          564
200          cd4         1/5/16          455
200          cd4         1/6/16          678
200          cd4         1/7/16          234
200          cd4         1/8/16          356
200          vl          1/7/16          1000

my desired result;

patientid    FirstCD4     FirstCD4Date     LastCD4     LastCD4Date
100          500          1/1/14           800         1/5/15
200          455          1/5/16           356         1/8/16

am using postgresql 9

+3


source to share


2 answers 


with CTE as
(
select p1.*, 
       row_number() over(partition by patientid order by dateordered asc) as r1, 
       row_number() over(partition by patientid order by dateordered desc) as r2 
from PatientLab p1
where labtest = 'cd4' -- Case sensitive, thanks etsa
)
select C1.PatientID, 
       C1.result as FirstCD4, 
       C1.dateordered as FirstCD4Date, 
       C2.result as LastCD4, 
       C2.dateordered as LastCD4Date
from CTE C1
inner join CTE C2
on C1.PatientID = C2.PatientID
where C1.r1 = 1
and C2.r2 = 1


+2


source


You can use min and max

select a.patientid, min(a.result) as FirstCD4, min(dateordered) as FirstCD4Date
    , max(a.result) as LastCD4, max(dateordered) as LastCD4Date 
    from (
select * from yourpatient where labtest = 'cd4' ) a
group by a.patientid;


Your table and data Code

0


source






More articles:


All Articles