Most Recent Date after Date

Question

I am working on a SQL query to extract patient data. I have 3 tables. One contains unique records for patient data (ie: First Name, Last Name, Date of Birth...). The second table is the Surgery table, that captures the date and results that the patient has surgery. The third table is the investigations table, that captures the investigations done after surgery. The key in the Patient table is called HUN, and it links the Surgery and Investigation tables in a 1 to many relationship.

What I need to find out, is what the most recent date of the investigation is after each surgery. One patient can have multiple surgeries, and I need to know the most recent investigation date following each.

Here is my data (Note: this is fictional data). There are 2 surgery dates: May 11, 2005 and November 22, 2010. Within these I need to know the most recent investigation date following the surgery date. The result should be 2 records:

242424 11-May-05 2011-07-19

and...

242424 22-Nov-10 2011-02-07

HUN     Surgery Date Investigation Date    
242424  11-May-05   2005-01-22     
242424  11-May-05   2006-03-29     
242424  11-May-05   2007-03-05     
242424  11-May-05   2008-04-01     
242424  11-May-05   2009-06-04     
242424  11-May-05   2009-06-19     
242424  11-May-05   2010-05-21     
242424  11-May-05   2011-02-07     
242424  11-May-05   2011-02-15     
242424  11-May-05   2011-07-19     
242424  11-May-05   2012-06-12     
242424  11-May-05   2012-09-18     
242424  11-May-05   2013-04-04     
242424  11-May-05   2013-10-30     
242424  11-May-05   2014-10-07     
242424  11-May-05   2015-09-09     
242424  22-Nov-10   2005-01-22     
242424  22-Nov-10   2006-03-29     
242424  22-Nov-10   2007-03-05     
242424  22-Nov-10   2008-04-01     
242424  22-Nov-10   2009-06-04     
242424  22-Nov-10   2009-06-19     
242424  22-Nov-10   2010-05-21     
242424  22-Nov-10   2011-02-07     
242424  22-Nov-10   2011-02-15     
242424  22-Nov-10   2011-07-19     
242424  22-Nov-10   2012-06-12     
242424  22-Nov-10   2012-09-18     
242424  22-Nov-10   2013-04-04     
242424  22-Nov-10   2013-10-30     
242424  22-Nov-10   2014-10-07     
242424  22-Nov-10   2015-09-09   

Answer 1

To get records of the most recent data you can do the following.

     $sql = 'select column_name from investigation_tbl order by tbl_field_name desc limit 2 ';
     //you can also get id specific details by passing a patient id by using WHERE in the query .

Answer 2

Based on information provided, here is the script.

CREATE TABLE #Investigation
    (
      HUN INT
    , Surgery_Date DATE
    , Investigation_Date DATE
    );

INSERT  INTO #Investigation
        ( HUN, Surgery_Date, Investigation_Date )
VALUES  ( 242424, '11-May-05', '2005-01-22' )
,       ( 242424, '11-May-05', '2006-03-29' )     
,       ( 242424, '11-May-05', '2007-03-05' )     
,       ( 242424, '11-May-05', '2008-04-01' )     
,       ( 242424, '11-May-05', '2009-06-04' )     
,       ( 242424, '11-May-05', '2009-06-19' )     
,       ( 242424, '11-May-05', '2010-05-21' )     
,       ( 242424, '11-May-05', '2011-02-07' )     
,       ( 242424, '11-May-05', '2011-02-15' )     
,       ( 242424, '11-May-05', '2011-07-19' )     
,       ( 242424, '11-May-05', '2012-06-12' )     
,       ( 242424, '11-May-05', '2012-09-18' )     
,       ( 242424, '11-May-05', '2013-04-04' )     
,       ( 242424, '11-May-05', '2013-10-30' )     
,       ( 242424, '11-May-05', '2014-10-07' )     
,       ( 242424, '11-May-05', '2015-09-09' )     
,       ( 242424, '22-Nov-10', '2005-01-22' )     
,       ( 242424, '22-Nov-10', '2006-03-29' )     
,       ( 242424, '22-Nov-10', '2007-03-05' )     
,       ( 242424, '22-Nov-10', '2008-04-01' )     
,       ( 242424, '22-Nov-10', '2009-06-04' )     
,       ( 242424, '22-Nov-10', '2009-06-19' )     
,       ( 242424, '22-Nov-10', '2010-05-21' )     
,       ( 242424, '22-Nov-10', '2011-02-07' )     
,       ( 242424, '22-Nov-10', '2011-02-15' )     
,       ( 242424, '22-Nov-10', '2011-07-19' )     
,       ( 242424, '22-Nov-10', '2012-06-12' )     
,       ( 242424, '22-Nov-10', '2012-09-18' )     
,       ( 242424, '22-Nov-10', '2013-04-04' )     
,       ( 242424, '22-Nov-10', '2013-10-30' )     
,       ( 242424, '22-Nov-10', '2014-10-07' )     
,       ( 242424, '22-Nov-10', '2015-09-09' );

SELECT  *
FROM    #Investigation;

SELECT  HUN
      , Surgery_Date
      , MAX(Investigation_Date) AS LatestInvestigation
      , MIN(Investigation_Date) AS EarliestInvestigation
FROM    #Investigation
GROUP BY HUN
      , Surgery_Date;

Answer 3

If your SQL Server supports Common Table Expressions (CTE) than the best way is:

;with t as (
select HUN,Surgery_Date,Investigation_Date,
ROW_NUMBER() over(partition by hun,Surgery_Date order by  Investigation_Date ) rn
from #Investigation
where Investigation_Date>Surgery_Date
)
select * from t
where rn=1
order by Surgery_Date desc

Note: Created and tested on MS SQL Server.

Answer 4

I believe that aggregating on your patient id and surgery columns will get you what you need:

SELECT DISTINCT T_EncounterSurgery.HUN, T_EncounterSurgery.SurgDate, MIN(T_EncounterIntervention.InvestigationDate)
FROM T_EncounterSurgery 
INNER JOIN T_EncounterIntervention ON 
    T_EncounterSurgery.HUN = T_EncounterIntervention.HUN 
WHERE (((T_EncounterSurgery.SurgDate)<=(SELECT MAX(InvestigationDate) FROM T_EncounterIntervention t WHERE t.HUN = T_EncounterSurgery.HUN)))
GROUP BY 1, 2