How do I get the minimum value and the latest date[SQL SERVER 2008]

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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:

SQL

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
;

Thank you for any help!!!

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

SQL

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
;

CSS

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

Posted 7-Jan-15 7:13am

alicashah

Updated 7-Jan-15 11:01am

v4

Add a Solution

Comments

[no name] 7-Jan-15 16:18pm

Please Google for "SQL MIN". You will find i.e. http://www.google.ch/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CB0QFjAA&url=http%3A%2F%2Fwww.w3schools.com%2Fsql%2Fsql_func_min.asp&ei=JaKtVKKTIcmAU52XgtAC&usg=AFQjCNGgXIpIAW0gOfUpBvrUjYOxNaABKA&bvm=bv.83134100,d.bGQ[^]

After understanding MIN the chance is high that you need also GROUP BY.

alicashah 7-Jan-15 17:02pm

I did Try the MIN function in my query. I update my query with MIN function above but it did not work.
Thank you for your help.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-01-07T12:25:00

I'd try this:

SQL

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

How do I get the minimum value and the latest date[SQL SERVER 2008]

1 solution

Solution 1

Add your solution here

Preview 0

How do I get the minimum value and the latest date[SQL SERVER 2008]

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us