我如何获得最小值和最新日期[SQL SERVER 2008]

问题描述

我能够为每位躺在病床上的患者获取每个FindingAbbr的最新日期，但是，我无法获得那些FindingAbbr的最小值。有人可以建议我这里做错了什么？请帮助!!



以下是查询：

I am able to get the latest date for each FindingAbbr for each patient who are in bed but, I am not able to get the minimum value for those FindingAbbr. Can somebody please suggest me What am I doing wrong here? Please help!!

Below is the query:

WITH cteRankedData As
(
    SELECT DISTINCT
        a.AccountID
         ,e.FindingAbbr
         ,e.Value
         ,e.CreationTime
        ,ROW_NUMBER() OVER (PARTITION BY a.AccountID, e.FindingAbbr ORDER BY e.CreationTime DESC) As RN

FROM      dbo.PatientVisitInfo a with (nolock)
JOIN      dbo.Assessment d with (nolock) ON a.PatientVisit_oid = d.PatientVisit_oid
JOIN      dbo.Observation e with (nolock) ON e.AssessmentID = d.AssessmentID
WHERE
         a.VisitTypeCode='IP'
     AND a.VisitEndDateTime is null
     AND e.Value <> ''
     AND e.FindingAbbr IN ('A_PEEPSet','A_KR_RT_FI02%','A_FIO2%Set','A_FIO2%', 'A_Vent Mode')
     AND e.CreationTime >= DATEADD(d,-1, GETDATE()))
SELECT
         AccountID
         ,FindingAbbr
         ,Value
         ,CreationTime


FROM
    cteRankedData
WHERE
    RN = 1

ORDER BY
    AccountID
  ,CreationTime
;

感谢您的帮助!!!



我尝试过min函数但它确实不行。下面是MIN函数的查询。

Thank you for any help!!!

I did try with min function but it did not work. below is the query with MIN function.

WITH cteRankedData As
(
    SELECT DISTINCT
        a.AccountID
         ,e.FindingAbbr
         ,min(e.Value) as Value
         ,e.CreationTime
        ,ROW_NUMBER() OVER (PARTITION BY a.AccountID, e.FindingAbbr ORDER BY e.CreationTime DESC) As RN

FROM      dbo.PatientVisitInfo a with (nolock)
JOIN      dbo.Assessment d with (nolock) ON a.PatientVisit_oid = d.PatientVisit_oid
JOIN      dbo.Observation e with (nolock) ON e.AssessmentID = d.AssessmentID
WHERE
         a.VisitTypeCode='IP'
     AND a.VisitEndDateTime is null
     AND e.Value <> ''
     AND e.FindingAbbr IN ('A_PEEPSet','A_KR_RT_FI02%','A_FIO2%Set','A_FIO2%', 'A_Vent Mode')
     AND e.CreationTime >= DATEADD(d,-1, GETDATE())
     group by a.AccountID ,e.FindingAbbr, e.CreationTime)
SELECT
         AccountID
         ,FindingAbbr
         ,Value
         ,CreationTime


FROM
    cteRankedData
WHERE
    RN = 1

ORDER BY
    AccountID
  ,CreationTime
;

Result I am getting:
AccountID   FindingAbbr Value   CreationTime
1           _FIO2%        40   1/7/15 1:55 PM
2           A_FIO2%       60   1/7/15 8:20 AM
2           A_FIO2%Set    60   1/7/15 9:47 AM
2           A_PEEPSet     8    1/7/15 9:47 AM
2           A_Vent Mode   CMV  1/7/15 9:47 AM
3           A_FIO2%Set    70   1/7/15 7:21 AM
4           A_KR_RT_FI02% 30   1/6/15 2:54 PM
4           A_FIO2%       30   1/7/15 9:35 AM
4           A_FIO2%Set    45   1/7/15 10:22 AM
4           A_PEEPSet     5    1/7/15 10:22 AM
4           A_Vent Mode   CMV  1/7/15 10:22 AM


all results:

AccountID   FindingAbbr Value   CreationTime
1           A_FIO2%       40   1/7/15 1:55 PM
2           A_FIO2%       60   1/7/15 8:20 AM
2           A_FIO2%       60   1/7/15 8:20 AM
2           A_FIO2%       100  1/7/15 1:31 AM
2           A_FIO2%       100  1/7/15 3:30 AM
2           A_FIO2%   Other:70 1/7/15 4:11 AM
2           A_FIO2%Set    60   1/7/15 3:49 AM
2           A_FIO2%Set    60   1/7/15 9:45 AM
2           A_FIO2%Set    60   1/7/15 9:47 AM
2           A_FIO2%Set    100  1/7/15 1:29 AM
2           A_PEEPSet     8    1/7/15 1:29 AM
2           A_PEEPSet     8    1/7/15 9:47 AM
2           A_PEEPSet     5    1/7/15 9:45 AM
2           A_PEEPSet     8    1/7/15 3:49 AM
2           A_Vent Mode   CMV  1/7/15 3:49 AM
2           A_Vent Mode   CMV  1/7/15 9:45 AM
2           A_Vent Mode   CMV  1/7/15 9:47 AM
2           A_Vent Mode   CMV  1/7/15 1:29 AM
3           A_FIO2%Set    70   1/6/15 3:09 PM
3           A_FIO2%Set    70   1/7/15 7:21 AM
4           A_FIO2%       30   1/7/15 4:26 AM
4           A_FIO2%       30   1/7/15 9:35 AM
4           A_FIO2%Set    45   1/7/15 9:53 AM
4           A_FIO2%Set    45   1/7/15 10:22 AM
4           A_FIO2%Set    45   1/7/15 3:55 AM
4           A_FIO2%Set    45   1/6/15 7:22 PM
4           A_FIO2%Set    45   1/6/15 11:02 PM
4           A_KR_RT_FI02% 30   1/6/15 2:54 PM
4           A_PEEPSet     5    1/6/15 7:22 PM
4           A_PEEPSet     5    1/6/15 11:02 PM
4           A_PEEPSet     5    1/7/15 3:55 AM
4           A_PEEPSet     5    1/7/15 10:22 AM
4           A_PEEPSet     5    1/7/15 9:53 AM
4           A_Vent Mode   CMV  1/7/15 9:53 AM
4           A_Vent Mode   CMV  1/7/15 10:22 AM
4           A_Vent Mode   CMV  1/7/15 3:55 AM
4           A_Vent Mode   CMV  1/6/15 11:02 PM
4           A_Vent Mode   CMV  1/6/15 7:22 PM
4           A_Vent Mode   CPAP     1/6/15 2:54 PM


But the result i want is below:

AccountID   FindingAbbr  Value   CreationTime
1           A_FIO2%       40   1/7/15 1:55 PM
2           A_FIO2%       60   1/7/15 8:20 AM
2           A_FIO2%Set    60   1/7/15 9:47 AM
2           A_PEEPSet     5    1/7/15 9:45 AM
2           A_Vent Mode   CMV  1/7/15 9:47 AM
3           A_FIO2%Set    70   1/7/15 7:21 AM
4           A_KR_RT_FI02% 30   1/6/15 2:54 PM
4           A_FIO2%       30   1/7/15 9:35 AM
4           A_FIO2%Set    45   1/7/15 10:22 AM
4           A_PEEPSet     5    1/7/15 10:22 AM
4           A_Vent Mode   CMV  1/7/15 10:22 AM

推荐答案

SELECT a.AccountID, e.FindingAbbr, MIN(e.Value) as Value ,e.CreationTime
FROM      dbo.PatientVisitInfo a with (nolock)
    JOIN      dbo.Assessment d with (nolock) ON a.PatientVisit_oid = d.PatientVisit_oid
    JOIN      dbo.Observation e with (nolock) ON e.AssessmentID = d.AssessmentID
WHERE a.VisitTypeCode='IP' AND a.VisitEndDateTime is null AND e.Value <> ''
     AND e.FindingAbbr IN ('A_PEEPSet','A_KR_RT_FI02%','A_FIO2%Set','A_FIO2%', 'A_Vent Mode')
     AND e.CreationTime >= DATEADD(d,-1, GETDATE()
GROUP BY a.AccountID, e.FindingAbbr, e.CreationTime

这篇关于我如何获得最小值和最新日期[SQL SERVER 2008]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！