Link to home
Start Free TrialLog in
Avatar of LCNW
LCNWFlag for United States of America

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.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LCNW

ASKER

I just need the MAX(DateTime) but then grab the corresponding value field.
Avatar of LCNW

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-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.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_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
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial