August 17, 2016 at 4:07 pm
I have this simple query to extract the blood pressure measures (SERVICE_TYPE_CD in ('DP', 'SP') from other measures in my table. How can I determine if I have a matched pair (an SP and a DP row) for each service date (SERVICE_DT)?
select distinct MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT ,SERVICE_TYPE_CD
,SERVICE_DT
from AllMetrics
where SERVICE_TYPE_CD in ('DP','SP')
order by MBR_LAST_NAME, MBR_FIRST_NAME, SERVICE_DT, SERVICE_TYPE_CD
Ideally I'd like to have only matched pairs per service date in the results. Any singletons would be filtered out.
Thanks in advance.
August 17, 2016 at 4:16 pm
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,
MAX(case when SERVICE_TYPE_CD = 'DP' then <value> end) AS DP,
MAX(case when SERVICE_TYPE_CD = 'SP' then <value> end) AS SP
from dbo.AllMetrics
where SERVICE_TYPE_CD in ('DP','SP')
group by MBR_LAST_NAME, MBR_FIRST_NAME, SERVICE_DT, BIRTH_DT
order by MBR_LAST_NAME, MBR_FIRST_NAME, SERVICE_DT
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 17, 2016 at 5:08 pm
Thanks ScottPletcher, this answer is very useful for getting the values from the separate rows pivoted into one row with diastolic and systolic blood pressures. I realize that I didn't explain well what I need to do. I have to keep the values in separate rows but screen out any that don't have a pair of rows for a given patient on a given service date. So if there is only an SP or only a DP for a patient on one date, it gets thrown out. If there were three (two DPs and one SP, say, I would save the SP and choose the MAX or MIN DP to pair up with it.
So if I have a patient with ID 78900 and one SP blood pressure for service date 1/2/2016 but no DP row with the same pat ID and sir_dt, the row isn't included in the results. If I have three rows, one SP and two DPs for one patient and service date, I'd want the SP and one of the two DPs (MAX or MIN). The case of having just one row (SP or DP) for one pt and service date is more likely to happen than having three. I guess there could be three SPs and no DP and vice versa as well.
I hope what I'm asking makes sense. Thanks.
August 18, 2016 at 8:52 am
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,
SERVICE_TYPE_CD, case when SERVICE_TYPE_CD = 'DP' then DP ELSE SP END AS BLOOD_PRESSURE
from (
select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,
MAX(case when SERVICE_TYPE_CD = 'DP' then value end) AS DP,
MAX(case when SERVICE_TYPE_CD = 'SP' then value end) AS SP
from dbo.AllMetrics
where SERVICE_TYPE_CD in ('DP','SP')
group by MBR_LAST_NAME, MBR_FIRST_NAME, SERVICE_DT, BIRTH_DT
having MAX(case when SERVICE_TYPE_CD = 'DP' then 1 else 0 end) = 1 and
MAX(case when SERVICE_TYPE_CD = 'SP' then 1 else 0 end) = 1
) as derived
cross join (
values('DP','SP')
) cj(SERVICE_TYPE_CD)
order by MBR_LAST_NAME, MBR_FIRST_NAME, SERVICE_DT
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 18, 2016 at 5:34 pm
Thanks very much for your continued help. I'm getting an error on the line that says ) cj (service_type_cxd):
Msg 8158, level 16, state 1, line n
'cj' has more columns than were specified in the column list. I'll see if I can figure it out. I'm marking this as answered though. Thanks!
August 19, 2016 at 8:09 am
DOH, sorry, typo on my part. Should be:
values('DP'),('SP')
[rather than values('DP','SP')]
Edit: added bold to the parens I added to highlight them better.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 19, 2016 at 9:38 am
Thanks ScottPlecther. Excellent. I remember that movie "Fatal Vision" and I can picture the Blackburn character saying that. I think it's been 30 year since I saw that mini-series.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply