LCNW
asked on
Compare records
I have columns with date, time, value. I want to find the last date/time and display it's corresponding value.
Thanks.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here's the 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-7 74f4dc249a e' 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-e ea698f50bc 0' 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.NA IS NOT NULL
THEN CONVERT(nvarchar(10), c.NA) END) AS 'Sodium Peak'
FROM dbo.ITX_vw_tbl_Referral AS r INNER JOIN
dbo.ITX_vw_tbl_DonorReferr al 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_Organizatio n 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_Enumeration Value AS ev ON ev.Value = CONVERT(nvarchar(50), p.Race) LEFT OUTER JOIN
dbo.ITX_vw_tbl_Enumeration Value 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
HAVING (p.UnosId = 'VHO228')
ORDER BY Hospital
I get the MAX(c.NA), but I also want the last c.NA and they have c.DateTime for timestamps.
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-7
CONVERT(varchar(10), p.Age) + '/ ' + p.AgeUnits AS 'Age/Units', p.Sex,
MAX(CASE WHEN ev.EnumerationTypeId = 'd9510b51-3677-4c39-b76a-e
+ '/' + p.HeightUnits AS 'Height/Units', CONVERT(varchar(10), p.Weight) + '/' + p.WeightUnits AS 'Weight/Units', MAX(CASE WHEN c.NA IS NOT NULL
THEN CONVERT(nvarchar(10), c.NA) END) AS 'Sodium Peak'
FROM dbo.ITX_vw_tbl_Referral AS r INNER JOIN
dbo.ITX_vw_tbl_DonorReferr
dbo.ITX_vw_tbl_Patient AS p ON r.PatientId = p.Id INNER JOIN
dbo.ITX_vw_tbl_Organizatio
dbo.ITX_vw_tbl_Chemistry AS c ON p.PatientId = c.PatientId LEFT OUTER JOIN
dbo.ITX_vw_tbl_Enumeration
dbo.ITX_vw_tbl_Enumeration
GROUP BY p.UnosId, ro.Name, dr.CrossClampedOn, p.Age, p.Sex, p.Height, p.HeightUnits, p.Weight, p.WeightUnits, p.AgeUnits
HAVING (p.UnosId = 'VHO228')
ORDER BY Hospital
I get the MAX(c.NA), but I also want the last c.NA and they have c.DateTime for timestamps.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER