我如何获得最小值和最新日期[SQL SERVER 2008] [英] How do I get the minimum value and the latest date[SQL SERVER 2008]
本文介绍了我如何获得最小值和最新日期[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
推荐答案
我试试这个：
I'd try this:
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]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
