本文介绍了如何在SQL SERVER中获取最新值和最新日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
select PatientAccountID,FindingAbbr,Value,LatestCreationTime from
(SELECT
a.PatientAccountID
,e.FindingAbbr
,e.Value
,max(e.CreationTime) as LatestCreationTime
FROM PatientVisitInfo a with (nolock)
INNER JOIN Assessment d with (nolock)
ON a.PatientVisit_oid = d.PatientVisit_oid
INNER JOIN Observation e with (nolock)
ON e.AssessmentID = d.AssessmentID
WHERE
a.PatientAccountID= '11'
AND a.VisitTypeCode='IP'
AND a.VisitEndDateTime is null
AND e.Value <> ''
AND e.FindingAbbr IN ('A_IV1 Site', 'A_IV2 Site', 'A_IV3 Site', 'A_IV4 Site')
Group BY a.PatientAccountID
,e.FindingAbbr,e.Value) as ab
Order by PatientAccountID, FindingAbbr,Value
Results I am getting:
PatientAccountID FindingAbbr Value LatestCreationTime
34990226 A_IV1 Site L Forearm 2014-11-11 08:01:00
34990226 A_IV1 Site R Forearm 2014-10-31 20:57:00
34990226 A_IV2 Site R Antecubital 2014-10-31 20:57:00
34990226 A_IV2 Site R Forearm 2014-11-11 08:01:00
34990226 A_IV3 Site R Forearm 2014-11-11 08:01:00
34990226 A_IV3 Site R Upper Arm 2014-10-31 20:57:00
34990226 A_IV4 Site L Forearm 2014-10-31 20:57:00
34990226 A_IV4 Site R Antecubital 2014-11-11 08:01:00
Results I am looking for is for each FindingAbbr the latest value and the latest date (only one to be displayed):
PatientAccountID FindingAbbr Value LatestCreationTime
34990226 A_IV1 Site L Forearm 2014-11-11 08:01:00
34990226 A_IV2 Site R Forearm 2014-11-11 08:01:00
34990226 A_IV3 Site R Forearm 2014-11-11 08:01:00
34990226 A_IV4 Site R Antecubital 2014-11-11 08:01:00
推荐答案
WITH cteRankedData As
(
SELECT
a.PatientAccountID,
e.FindingAbbr,
e.Value,
e.CreationTime,
ROW_NUMBER() OVER (PARTITION BY e.FindingAbbr ORDER BY e.CreationTime DESC) As RN
FROM
PatientVisitInfo a with (nolock)
INNER JOIN Assessment d with (nolock)
ON a.PatientVisit_oid = d.PatientVisit_oid
INNER JOIN Observation e with (nolock)
ON e.AssessmentID = d.AssessmentID
WHERE
a.PatientAccountID= '11'
AND
a.VisitTypeCode='IP'
AND
a.VisitEndDateTime is null
AND
e.Value <> ''
AND
e.FindingAbbr IN ('A_IV1 Site', 'A_IV2 Site', 'A_IV3 Site', 'A_IV4 Site')
)
SELECT
PatientAccountID,
FindingAbbr,
Value,
CreationTime As LatestCreationTime
FROM
cteRankedData
WHERE
RN = 1
ORDER BY
PatientAccountID,
FindingAbbr,
Value
;
select PatientAccountID,FindingAbbr,Value,LatestCreationTime from
(
SELECT
a.PatientAccountID
,e.FindingAbbr
,e.Value
,e.CreationTime as LatestCreationTime
,ROW_NUMBER() Over (Partition by PatientAccountID Order by CreationTime desc) As OrderID
FROM PatientVisitInfo a with (nolock)
INNER JOIN Assessment d with (nolock)
ON a.PatientVisit_oid = d.PatientVisit_oid
INNER JOIN Observation e with (nolock)
ON e.AssessmentID = d.AssessmentID
WHERE
a.PatientAccountID= '11'
AND a.VisitTypeCode='IP'
AND a.VisitEndDateTime is null
AND e.Value <> ''
AND e.FindingAbbr IN ('A_IV1 Site', 'A_IV2 Site', 'A_IV3 Site', 'A_IV4 Site')
Group BY a.PatientAccountID
,e.FindingAbbr,e.Value
) as ab
Where OrderID=1
Order by PatientAccountID, FindingAbbr,Value
这篇关于如何在SQL SERVER中获取最新值和最新日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!