sql_merge.sql

post_trial_caseloads.sql

WITH CTE_PatientStatus AS (
    SELECT
        LEFT(RIGHT(CPS.Case_Number, 7), 6) + '-' + RIGHT(CPS.Case_Number, 1) AS Patient_Num,
        CPS.Rpt_Legal_Class_Text,
        LEG.Legal_Class_Text,
        CPS.Status_Text,
        CONVERT(VARCHAR(10), ADM.DOB, 101) AS DOB,
        CPS.Rpt_LC_Start_Date,
        ADM.Admission_Date,
        CPS.Rpt_LC_End_Date,
        LEG.Effective_Date,
        LEG.End_Date,
        LEG.County_Of_Commit_Text,
        CPS.Program,
        CPS.Unit,
        ADM.Sex,
        LEG.Legal_Status_Ranking_Text,
        IIF(
            CPS.Patient_Middlename != '',
            CPS.Patient_Lastname + ', ' + CPS.Patient_Firstname + ' ' + CPS.Patient_Middlename,
            CPS.Patient_Lastname + ', ' + CPS.Patient_Firstname
        ) AS Name,
        CASE
            WHEN CPS.Program = 'I' THEN '1'
            WHEN CPS.Program = 'II' THEN '2'
            WHEN CPS.Program = 'III' THEN '3'
            WHEN CPS.Program = 'IV' THEN '4'
            WHEN CPS.Program = 'V' THEN '5'
        END AS P,
        CPS.Unit AS U,
        -- AltWriter columns combined using ISNULL
        ISNULL(T1026x.AltWriter, ISNULL(T1026.AltWriter, T2972.AltWriter)) AS Evaluator,
        CPS.Rpt_LC_County_Text AS County,
        ROW_NUMBER() OVER (PARTITION BY CPS.Case_Number ORDER BY T1026x.AltWriter DESC, T1026.AltWriter DESC, T2972.AltWriter DESC) AS RowNum
    FROM [CoRTReport24].dbo.AssignedDrs AS DRS
    RIGHT JOIN MHNODSSQL1P.ODS.dbo.Current_Patient_Status AS CPS
        ON DRS.CaseNum = CPS.Case_Number
    LEFT JOIN MHNODSSQL1P.ODS.dbo.Admission AS ADM
        ON CPS.Case_Number = ADM.Case_Number
    LEFT JOIN MHNODSSQL1P.ODS.dbo.Legal_Class AS LEG
        ON CPS.Case_Number = LEG.Case_Number
    -- Join T1026x for tbl1026Ext
    LEFT JOIN [CoRTReport24].dbo.[tbl1026Ext] AS T1026x
        ON DRS.CaseNum = T1026x.CaseNum
    -- Join T1026 for tbl1026
    LEFT JOIN [CoRTReport24].dbo.[tbl1026] AS T1026
        ON DRS.CaseNum = T1026.CaseNum
    LEFT JOIN [CoRTReport24].dbo.[tbl2972] AS T2972
        ON DRS.CaseNum = T2972.CaseNum
    WHERE
        CPS.Status_Text <> ''
        AND LEG.Legal_Status_Ranking_Text IN ('P', 'D')
        AND ADM.Discharge_Status IS NULL
        AND CPS.Program <> ''
        AND CPS.Rpt_Legal_Class_Text IN ('PC1026', '1026.5', 'MDSO', 'RO1026', 'PC2972', 'RO2972')
)

SELECT
    Patient_Num,
    CASE
        WHEN Legal_Status_Ranking_Text = 'P' THEN Rpt_Legal_Class_Text
        WHEN Legal_Status_Ranking_Text = 'D' THEN Legal_Class_Text
    END AS Class,
    Status_Text AS Location,
    DOB,
    FORMAT(Rpt_LC_Start_Date, 'MM/dd/yyyy') AS Commitment,
    CASE
        WHEN Legal_Status_Ranking_Text = 'P' THEN FORMAT(Admission_Date, 'MM/dd/yyyy')
        WHEN Legal_Status_Ranking_Text = 'D' THEN FORMAT(Effective_Date, 'MM/dd/yyyy')
    END AS Admission,
    CASE
        WHEN Legal_Status_Ranking_Text = 'P' THEN FORMAT(Rpt_LC_End_Date, 'MM/dd/yyyy')
        WHEN Legal_Status_Ranking_Text = 'D' THEN FORMAT(End_Date, 'MM/dd/yyyy')
    END AS Expiration,
    CASE
        WHEN Legal_Status_Ranking_Text = 'P' THEN FORMAT(DATEADD(MONTH, -6, Rpt_LC_End_Date), 'MM/dd/yyyy')
        WHEN Legal_Status_Ranking_Text = 'D' THEN FORMAT(DATEADD(MONTH, -6, End_Date), 'MM/dd/yyyy')
    END AS Due_Date,
    CASE
        WHEN Legal_Status_Ranking_Text = 'P' THEN MONTH(DATEADD(MONTH, -6, Rpt_LC_End_Date))
        WHEN Legal_Status_Ranking_Text = 'D' THEN MONTH(DATEADD(MONTH, -6, End_Date))
    END AS Month_Num,
    Name,
    P,
    U,
    County,
    Sex,
    Legal_Status_Ranking_Text AS Rank,
    -- Evaluator column from ISNULL
    Evaluator
FROM CTE_PatientStatus AS CPS
WHERE RowNum = 1
ORDER BY Name ASC;