Find Last Not Null record

That works. The problem now is I need to implement it into my existing query. Ugh...



SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units]


<<<<<<<<<<<<<<<<<<<<<<<<I NEED IT RIGHT HERE>>>>>>>>>>>>>>


FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital

I implemented that and it gets a number, but not the correct one. I'm not sure where it's getting the value? Here's my updated query:

SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units], MAX(CASE WHEN c.SGOTAST IS NOT NULL
                      THEN CONVERT(nvarchar(10), c.SGOTAST) END) + '/' +
                          (SELECT     TOP (1) CONVERT(nvarchar(10), SGOTAST) AS SGOTAST
                            FROM          dbo.ITX_vw_tbl_Chemistry
                            WHERE      (SGOTAST IS NOT NULL)
                            ORDER BY DateTime DESC) AS SGOTAST
FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital

I got it! I needed to add one bit with the patient in the WHERE clause:

SELECT     TOP (100) PERCENT p.UnosId AS [UNOS ID], ro.Name AS Hospital, dr.CrossClampedOn AS CrossClamp,
                      MAX(CASE WHEN evCriteria.parentValueId = '860f6e6d-26e5-4667-8cb2-774f4dc249ae' THEN evCriteria.Description ELSE '0' END) AS [Donor Criteria Type],
                      CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS [Age/Units], p.Sex,
                      MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN ev.Description ELSE '0' END) AS Race, CONVERT(varchar(10), p.Height)
                      + '/' + p.HeightUnits AS [Height/Units], CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS [Weight/Units],
                          (SELECT     TOP (1) SGOTAST
                            FROM          dbo.ITX_vw_tbl_Chemistry AS s
                            WHERE      (SGOTAST IS NOT NULL) AND (PatientId = c.PatientId)
                            ORDER BY DateTime DESC) AS Expr1
FROM         dbo.ITX_vw_tbl_Referral AS r INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral AS dr ON r.Id = dr.ReferralId INNER JOIN
                      dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
                      dbo.ITX_vw_tbl_Organization AS ro ON r.ReferringOrganizationId = ro.Id INNER JOIN
                      dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue AS evCriteria ON evCriteria.Id = dr.OrganOutcomeDetail
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits, c.PatientId
HAVING      (p.UnosId = 'VHO228')
ORDER BY Hospital

Thanks for the help!

Thanks for the help.

Thanks for the grade.  Good luck with your project.  -Jim