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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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