asked on
How do I group multiple records into one record using Case statement for table that stores records seperately
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
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
Microsoft SQL ServerSQL
Last Comment
cbads
ASKER
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.
ASKER
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Eugene:
Thank you for the example. I will give a try and will let you know of the results.
Cbads
Thank you for the example. I will give a try and will let you know of the results.
Cbads
ASKER
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'
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'
ASKER
Thanks.
ASKER
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
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
ASKER
I noticed a problem with the solution. Thanks.
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
.......................