Retrieve Latest (max) date

Question

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

Answer 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;

Answer 2

Another approach (easier for me to follow, but YMMV) is to use a CTE:

;WITH MostRecentAssessment AS
(
  SELECT PATIENT_ID, 
         ASSESSMENT_DATE,
         QUESTION_TEXT,
         ANSWER_TEXT,
         rn = ROW_NUMBER() OVER 
         (
           PARTITION BY PATIENT_ID 
           ORDER BY ASSESSMENT_DATE DESC
         )
  FROM dbo.VW_ASSESSMENT_DATA
  WHERE ANSWER_TEXT LIKE '%Level %'
    AND ASSESSMENT_DATE > '20171201'
    AND QUESTION_TEXT LIKE '%Plan/Class%'
)
SELECT p.PATIENT_CODE,
       p.NAME_FULL,
       m.ASSESSMENT_DATE, 
       m.QUESTION_TEXT, 
       m.ANSWER_TEXT
  FROM dbo.PT_BASIC AS p
  INNER JOIN MostRecentAssessment AS m
  ON p.PATIENT_ID = m.PATIENT_ID
  WHERE m.rn = 1 --derp
  ORDER BY p.NAME_FULL;