troubleshooting Question

How do I group multiple records into one record using Case statement for table that stores records seperately

Avatar of cbads
cbads asked on
Microsoft SQL ServerSQL
9 Comments1 Solution230 ViewsLast Modified:
Hello,
I need help with writing a SQL statement that brings together multiple records into one. I'm using a lab table which contains two entries for patient blood pressures (one systolic and one diastolic) and a patient table that contains patientid and patient information. The foreign key from the lab table is the patientid and the primary key is labid. The labtypeid 4 is systolic and labtypeid 5 is diastolic. I'm using a case statement to filter on systolic <= 130 and diastolic <= 80 and need to combine both records into one so I can list which patient had a value that is within the measure of 130/80. Once I have the single records, I need to list which patients have at least two records that are within the measure stated above. (130/80). Here's the statement I'm using but I'm having trouble combining the output. Thanks.
Cbads

SELECT P1.PATIENTID, P1.LABDATE, P1.LABID, P2.LABDATE, P2.LABID,
SYS = CASE WHEN P1.LABTYPEID = '4' AND P1.LABRESULT <= '130' THEN P1.LABRESULT ELSE ''
END, DIA = CASE WHEN P2.LABTYPEID = '5' AND P2.LABRESULT <= '80' THEN P2.LABRESULT ELSE ''
END
FROM #PTLAB AS P1
LEFT OUTER JOIN #PTLAB AS P2
ON P1.LABID=P2.LABID
WHERE P1.PATIENTID = '2'
GROUP BY P1.PATIENTID, P1.LABDATE, P1.LABID, P2.LABDATE,
P2.LABID, P1.LABTYPEID, P1.LABRESULT, P2.LABTYPEID, P2.LABRESULT
ORDER BY P1.PATIENTID  

Here's the output
p1.patientid  p1.labdate                          p1.labid    p2.labdate                              p2.labid    sys           dia
2      2007-06-05 00:00:00.000      63060      2007-06-05 00:00:00.000      63060      0      0
2      2007-06-05 00:00:00.000      63061      2007-06-05 00:00:00.000      63061      0      80
2      2007-10-02 00:00:00.000      63058      2007-10-02 00:00:00.000      63058      130      0
2      2007-10-02 00:00:00.000      63059      2007-10-02 00:00:00.000      63059      0      80
2      2008-01-08 00:00:00.000      63054      2008-01-08 00:00:00.000      63054      0      0
2      2008-01-08 00:00:00.000      63055      2008-01-08 00:00:00.000      63055      0      80
2      2008-04-08 00:00:00.000      85673      2008-04-08 00:00:00.000      85673      130      0
2      2008-04-08 00:00:00.000      85674      2008-04-08 00:00:00.000      85674      0      80
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros