We help IT Professionals succeed at work.

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

226 Views
Last Modified: 2010-03-20
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
Comment
Watch Question

EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
what output you would like to see?
what is in common between the records that will make them combined except p1.patientid"
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
.......................

Author

Commented:
I would like to have the records combined into one for sys and dia are part of that single record. So in the example output file, those that had 0 would not be included and any that do not have a matching value in either sys or dia would also not be included. The problem is finding the common value in the records and so far the only thing that is common is the labdate. Thanks for your reply.

Author

Commented:
Also, in the example output file from the previous post, the first two records would not be included because the sys value is null or 0. I need to have the output file show two records for this patient (labid 63058 & 63059) as one record and (labid 85673 & 85674) as the second record.
 
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
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Eugene:
Thank you for the example. I will give a try and will let you know of the results.

Cbads

Author

Commented:
Hi EugeneZ,
I tried your example and it worked perfectly for what I needed. I changed my Case statement to include all sys/dia values and inserted it into another table test23 and used the select statement below to pull only those that were <= 130/80.  Below is the updated Case statement and the select. Thanks for all your help.

insert into test22(patientid, labdate, labid, sys, dia)
SELECT P1.PATIENTID, P1.LABDATE, P1.LABID,
SYS = CASE WHEN P1.LABTYPEID = '4' THEN P1.LABRESULT else ''
END, DIA = CASE WHEN P2.LABTYPEID = '5' 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

select patientid, bp, count(*)
from test23
where bp <= '130\80'
group by patientid, bp
having count(*) >= '2'

Author

Commented:
Thanks.

Author

Commented:
EugeneZ,

I'm sorry but I spoke too soon regarding this solution. It seems to be working correctly but I'm noticing some of the records are reversed. If you look at the last set of records from the example below, you'll notice that it's reversed. (85674/85673) which are in reverse order and should be 130/80. Thanks.

Claude

2      2007-06-05 00:00:00.000      63060|63061      \80
2      2007-10-02 00:00:00.000      63058|63059      130\80
2      2008-01-08 00:00:00.000      63055|63054      80\
2      2008-04-08 00:00:00.000      85674|85673      80\130

Author

Commented:
I noticed a problem with the solution. Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.