CODEWITHSUNDEEP
sqlsql-server
Amanda
Amanda

Reputation: 11

Combining 3 views in a query with specific Max and Where criteria

I am currently trying to create a query that pulls from three separate views. In the first view (view a. called view_ods_assessment)I would like to pull all of the columns in this view, however, would only like to pull specific stdAssessIDs and only pull the max (or most recent AssessmentDate) for each PatientID in this view. In this query I would also like to pull only the Description column from my second view (view b. called view_ods_std_assessment) as long as stdAssessID from view a = the stdAssessID from view b. From the third view (view c. called view_ods_daily_census) I would like to pull in the PatientFirstName, PatientLastName, PatientMiddleName, FacilityName, and PayerName as long as the PatientID from view "a." = the ClientID from view "c."

By entering the following query I am able to my information, HOWEVER, this is currently pulling ALL of the assessments instead of pulling just the most recent assessments for each PatientID. I am having some issues trying to create a query that will run and pull just the max AssessmentDate for each PatientID. Can someone help with this??

SELECT a.AssessmentID, a.stdAssessID, a.PatientID, a.AssessmentDate, a.AssessmentStatus, a.AssessmentTypeKey, a.CommunicationScale, a.CognativePerformanceScale, a.DepressionRatingScale, a.PainScale, 
a.ActivityDailyLivingScore, a.MedicareCMI, a.MedicareRUG, a.MedicareNonTherapyCMI, a.MedicareNonTherapyRUG, a.StateCMI, a.StateRUG, a.StateAlternateCMI, a.StateAlternateRUG, a.Score3, a.CreatedDate, a.RevisionDate, a.Deleted, a.IncorrectAssessmentID, a.FacilityID, a.CreatedBy, a.CompletedDate, a.LockedDate, a.RevisionBy, a.DeletedBy, a.DeletedDate, a.MDSAcceptedDate, a.BatchID, b.Description, c.PatientFirstName, c.PatientLastName, c.PatientMiddleName, c.FacilityName, c.PayerName
FROM view_ods_assessment AS a 
LEFT JOIN view_ods_std_assessment AS b ON a.stdAssessID = b.StdAssessID 
LEFT JOIN view_ods_daily_census AS c ON a.PatientID = c.ClientID
WHERE a.stdAssessID IN ('1', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13')

Upvotes: 0

Views: 43

Answers (1)

Richard Hansell
Richard Hansell

Reputation: 5403

If you wanted to go down the ROW_NUMBER() route then you would probably want to use a common table expression to pre-calculate this, then use it as a filter. So something like this might work:

WITH cte AS (
    SELECT 
        a.AssessmentID, 
        a.stdAssessID, 
        a.PatientID, 
        a.AssessmentDate, 
        a.AssessmentStatus, 
        a.AssessmentTypeKey, 
        a.CommunicationScale, 
        a.CognativePerformanceScale, 
        a.DepressionRatingScale, 
        a.PainScale, 
        a.ActivityDailyLivingScore, 
        a.MedicareCMI, 
        a.MedicareRUG,
        a.MedicareNonTherapyCMI, 
        a.MedicareNonTherapyRUG, 
        a.StateCMI, 
        a.StateRUG, 
        a.StateAlternateCMI, 
        a.StateAlternateRUG, 
        a.Score3, 
        a.CreatedDate, 
        a.RevisionDate, 
        a.Deleted, 
        a.IncorrectAssessmentID, 
        a.FacilityID, 
        a.CreatedBy, 
        a.CompletedDate, 
        a.LockedDate, 
        a.RevisionBy, 
        a.DeletedBy, 
        a.DeletedDate, 
        a.MDSAcceptedDate, 
        a.BatchID, 
        b.[Description], 
        c.PatientFirstName, 
        c.PatientLastName, 
        c.PatientMiddleName, 
        c.FacilityName, 
        c.PayerName,
        ROW_NUMBER() OVER (PARTITION BY a.PatientID ORDER BY a.AssessmentDate DESC) AS patient_row_id
    FROM 
        view_ods_assessment AS a 
        LEFT JOIN view_ods_std_assessment AS b ON a.stdAssessID = b.StdAssessID 
        LEFT JOIN view_ods_daily_census AS c ON a.PatientID = c.ClientID
    WHERE 
        a.stdAssessID IN ('1', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13'))
SELECT
    * --too lazy to list all those columns again
FROM
    cte
WHERE
    patient_row_id = 1;

The key change there is the ROW_NUMBER() OVER (PARTITION BY a.PatientID ORDER BY a.AssessmentDate DESC) AS patient_row_id line, which basically says, "give me a number that starts at 1 and keeps incrementing by 1, partition this number by the Patient Id, so start back at 1 for each unique Patient Id, and order the numbering by the assessment date in reverse date order".

To get the latest assessment for each patient you then can simply filter out rows where this number equals 1.

Upvotes: 0

Related Questions