0

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
2
  • Please add your table schema and tag your RDBMS.
    – McNets
    Commented Jan 12, 2018 at 15:37
  • What exactly is the point of TOP (100) PERCENT here? Commented Jan 12, 2018 at 21:18

2 Answers 2

3

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;
1
  • I'm glad to help.
    – McNets
    Commented Jan 12, 2018 at 18:03
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.