Click here to Skip to main content
16,018,818 members
Search within:


How to get the latest value and latest date in SQL SERVER
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Add a Solution

2 solutions

You haven't specified which DBMS you're using. Assuming Microsoft SQL Server 2005 or later, the ROW_NUMBER ranking function[^] should work:
SQL
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
;
 
Share this answer
 
Posted
Comments
alicashah 11-Nov-14 11:28am    
Thank you!!!
alicashah 11-Nov-14 11:33am    
One more thing what do I do if I have to do this for all patient who are in bed?
if I used the below code then I get only 4 rows back. It worked for that patient id '11' but it didnot give me the results for all patient. also I am using SQL SERVER 2008. Thank you.

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.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
;
Richard Deeming 11-Nov-14 11:37am    
You'll need to add the PatientAccountID to the PARTITION BY clause:

ROW_NUMBER() OVER (PARTITION BY e.PatientAccountID, e.FindingAbbr ORDER BY e.CreationTime DESC) As RN
alicashah 11-Nov-14 11:47am    
Thank you !!!
Try this

SQL
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
 
Share this answer
 
Posted
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
1. Find the starting INITIAL BEFORE VALUE and the LATEST AFTER VALUE
Get record with latest date on SQL
How to get the latest record against particular id in SQL
How to retrieve latest 25 data from a SQL server table in VB.NET
How do I get the minimum value and the latest date[SQL SERVER 2008]
Latest Entry in the database
Get all the employees latest document date in SQL server
How to get latest date of a transaction
To get latest record
Sql query to fetch latest record

Advertise
Privacy
Cookies
Terms of Use
Last Updated 11 Nov 2014
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900