Per my client, they need the last HGBA1C Value (which is the o.OBSVALUE) along with the last date that corresponds with this (o.ObsDate). In my query below, I am getting more then one record for a specific patient. How could I just return the patients one date and value versus them all? SELECT TOP 1 maybe? Not 100% sure.
---- Last HGBA1C Value / Date Last HGBA1C Value / (HGBA1C is obs.HDID = 28)SELECT o.pid, pp.PatientId, pp.PatientProfileId, dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName, MAX(o.OBSDate) AS obsdate, o.OBSVALUEFROM OBS AS o JOIN OBSHEAD AS oh ON o.HDID = oh.HDID JOIN PatientProfile AS pp ON o.PID = pp.PIdWHERE o.HDID = 28GROUP BY o.PID, pp.PatientId, pp.PatientProfileId, dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix), o.OBSVALUE
SELECT top 1 o.pid,
pp.PatientId,
pp.PatientProfileId,
dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
MAX(o.OBSDate) AS obsdate,
o.OBSVALUE
FROM OBS AS o
JOIN OBSHEAD AS oh ON o.HDID = oh.HDID
JOIN PatientProfile AS pp ON o.PID = pp.PId
WHERE o.HDID = 28
GROUP BY o.PID,
pp.PatientId,
pp.PatientProfileId,
dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix),
o.OBSVALUE
ORDER BY o.OBSVALUE DESC
Jeff S
ASKER
ewangoya -
I started out with 28 records in my data set with multiple patients (some having more then one OBSVALUE. When I use your example, I just get the most recent overall. I want the most recent per distinct patient. I am reading through Angels post and think this may be more in line with what I need, just need to read through it all.
Select Top 1 [columns you want]