I have a Patient table:
PatientId Admitted
--------- ---------------
1 d/m/yy hh:mm:ss
2 d/m/yy hh:mm:ss
3 d/m/yy hh:mm:ss
I have a PatientMeasurement table (0 to many):
PatientId MeasurementId Recorded Value
--------- ------------- --------------- -----
1 A d/h/yy hh:mm:ss 100
1 A d/h/yy hh:mm:ss 200
1 A d/h/yy hh:mm:ss 300
2 A d/h/yy hh:mm:ss 10
2 A d/h/yy hh:mm:ss 20
1 B d/h/yy hh:mm:ss 1
1 B d/h/yy hh:mm:ss 2
I am trying to create a result set that resembles:
PatientId Numerator Denominator
--------- -------- -----------
1 1 1
2 1 1
3 0 1
Essentially, a patient will have a 1 in the numerator if the have at least one value for measurement A and one value for measurement B. In this example, patient 1 has 3 A measurements and 2 B measures, so the numerator is 1. Patient 2 has 2 A measurements, but no B measurements, so the numerator is 0. Patient has neither an A measurement nor a B measurement, so the numerator is 0.
My query thus far is:
SELECT PatientId, CASE WHEN a.cnt+b.cnt>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM patient p
LEFT OUTER JOIN (
SELECT PatientId, count(*) cnt
FROM PatientMeasurement pm
WHERE MeasurementId= A
--AND Recorded <= dateadd(hh, 12, Admitted)
GROUP BY PatientId
) a ON p.PatientId=a.PatientId
LEFT OUTER JOIN (
SELECT PatientId, count(*) cnt
FROM PatientMeasurement pm
WHERE MeasurementId= B
--AND Recorded <= dateadd(hh, 12, Admitted)
GROUP BY PatientId
) b ON p.PatientId=b.PatientId
This works as expected as long as I don t include the correlated, date restriction (Recorded < dateadd(hh, 12, Admitted). Unfortunately, correlating an inline view in this manner is not syntactically valid.
This has forced me to re-write the SQL to:
SELECT PatientId, CASE WHEN v.a+v.b>2 THEN 1 ELSE 0 END Numerator, 1 Denominator
FROM (
SELECT PatientId,
(
SELECT PatientId, count(*) cnt
FROM PatientMeasurement pm
WHERE PatientId=p.PatientId
AND MeasurementId= A
AND Recorded <= dateadd(hh, 12, Admitted)
GROUP BY PatientId
) a,
(
SELECT PatientId, count(*) cnt
FROM PatientMeasurement pm
WHERE PatientId=p.PatientId
AND MeasurementId= B
AND Recorded <= dateadd(hh, 12, Admitted)
GROUP BY PatientId
) b
FROM Patient p
) v
My question: Is there a better, more-efficient way to do this?
Thanks for your time.