Retrieve Latest (max) date

by KenW   Last Updated January 12, 2018 16:06 PM

Using SQL Server 2008R2, I am trying to pull the latest assessment date for a patient. I have it pulling the correct data I need, but it is pulling all dates. For instance if a patient has an assessment on 1/1, 1/5, 1/8, and 1/10 I am currently getting all records when I really only want the last one (in this example, 1/10).

SELECT     TOP (100) PERCENT dbo.PT_BASIC.PATIENT_CODE, 
           dbo.PT_BASIC.NAME_FULL, 
           dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE, 
           dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT, 
           dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT
FROM       dbo.VW_ASSESSMENT_DATA 
INNER JOIN dbo.PT_BASIC 
ON         dbo.VW_ASSESSMENT_DATA.PATIENT_ID = dbo.PT_BASIC.PATIENT_ID
WHERE      (dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT LIKE '%Level %') 
AND        (dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102)) 
AND        (dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT LIKE '%Plan/Class%')
ORDER BY   dbo.PT_BASIC.NAME_FULL
Tags : sql-server sql


Answers 1


Try by using a CROSS/OUTER APPLY solution:

It should return one record ordered by ASSESMENT_DATE DESCfor each record of main table dbo.PB_BASIC

SELECT      TOP (100) PERCENT 
            B.PATIENT_CODE, 
            B.NAME_FULL, 
            A.ASSESSMENT_DATE, 
            A.QUESTION_TEXT, 
            A.ANSWER_TEXT
FROM        dbo.PT_BASIC B
CROSS APPLY (SELECT   TOP 1
                      PATIENT_ID,
                      ASSESSMENT_DATE,
                      QUESTION_TEXT,
                      ANSWER_TEXT
             FROM     dbo.VW_ASSESSMENT_DATA
             WHERE    PATIENT_ID = B.PATIENT_ID
             AND      (ANSWER_TEXT LIKE '%Level %') 
             AND      (ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102)) 
             AND      (QUESTION_TEXT LIKE '%Plan/Class%')
             ORDER BY ASSESSMENT_DATE DESC) A
ORDER BY   B.NAME_FULL;
McNets
McNets
January 12, 2018 15:45 PM

Related Questions


Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM