December 1, 2006 at 11:28 am
I need to do a simple select-
SELECT O.ObservationTypeId, O.ObservationDate, O.Source, O.Status, O.Report
FROM PM_Observation O WHERE O.PatientId = @PatientId
However it is complicated by the simple requirement that I only want the most recent record for each patient/observationTypeId. My solution is as follows, but it seems there should be a more elegant way. Anyone?
DECLARE @tblReports table (ObservationTypeId int, ObservationDate datetime)
INSERT INTO @tblReports
SELECT O.ObservationTypeId, MAX(O.ObservationDate) AS ObservationDate
FROM PM_Observation O
WHERE O.PatientId = @PatientId
GROUP BY O.ObservationTypeId
SELECT O.ObservationTypeId,
O.ObservationDate,
O.Source,
O.Status,
O.Report
FROM PM_Observation O
JOIN @tblReports R ON O.ObservationTypeId = R.ObservationTypeId AND O.ObservationDate = R.ObservationDate
WHERE O.PatientId = @PatientId
December 1, 2006 at 11:57 am
No you pretty much have it. The only change I can offer is to remove the insert statement.
SELECT O.ObservationTypeId,
O.ObservationDate,
O.Source,
O.Status,
O.Report
FROM PM_Observation O
INNER JOIN
(
SELECT O.ObservationTypeId, MAX(O.ObservationDate) AS ObservationDate
FROM PM_Observation O
WHERE O.PatientId = @PatientId
GROUP BY O.ObservationTypeId
) R ON O.ObservationTypeId = R.ObservationTypeId AND O.ObservationDate = R.ObservationDate
WHERE O.PatientId = @PatientId
December 1, 2006 at 12:05 pm
Ok, thanks. Good to know I'm not missing anything obvious.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply