| | | Dataset is the primary dataset for the report. It built using four CTEs (PROGS, CansDC, PscDC, AnsaDC), an inner subquery that calculates due dates and last-completed dates, and an outer query that converts those into Status labels. PROGS (CTE) — Program Assessment Requirements
Purpose: Determines which assessments apply to each Program and their due-day intervals, based on CalOMS service type and Service Area. Pulls from Programs (P), with: - INNER JOIN CustomPrograms (CP) ON P.programid = CP.programid, non-soft-deleted
Pull data based on the following criteria: - P.RecordDeleted = ‘N’
- P.Active = ‘Y’
- P.ProgramType is not 25247 or 25165 (excludes External and Pre-Admit program types)
Special Notes regarding the query: - CalOMSTypeDesc is decoded from CP.CalOMSServiceType via ssf_GetGlobalCodeNameById()
- ASAMDueDays is derived from CP.CalOMSServiceType: code 11132885 → 90 days, 11132888 → 1 day, 11132890 → 10 days, 11132884 → 90 days, 11132886 → 28 days; NULL or unmatched → NULL (N/A)
- CANSDueDays, ANSADueDays, PSC35DueDays, and CalAIMDueDays are all set to 90 days if P.ServiceAreaId = 1, otherwise NULL
- CANSSubsequenMonths, ANSASubsequenMonths, and PSC35SubsequenMonths are all set to 6 months if P.ServiceAreaId = 1, otherwise NULL
- CANSWithinDischargeDays, ANSAWithinDischargeDays, and PSC35WithinDischargeDays are all set to 30 days if P.ServiceAreaId = 1, otherwise NULL
CansDC (CTE) — Last Discharge CANS per Client
Purpose: Finds each client’s most recent discharge-type CANS assessment date. Pulls from Documents (D), with: - INNER JOIN DocumentCaliforniaCANSGenerals (C) ON D.currentdocumentversionid = C.documentversionid, non-soft-deleted, and C.AssessmentType IN (‘A’, ‘D’)
Pull data based on the following criteria: - D.DocumentCodeId = 60142 (CANS)
- D.RecordDeleted = ‘N’
- D.Status = 22 (Signed)
Special Notes regarding the query: - Results are grouped by ClientId, returning MAX(EffectiveDate) as LastDCCANS
PscDC (CTE) — Last Discharge PSC35 per Client
Purpose: Finds each client’s most recent discharge/administrative-close PSC35 assessment date. Pulls from Documents (D), with: - INNER JOIN DocumentCaliforniaPediatricSymptomCheckLists (P) ON D.currentdocumentversionid = P.documentversionid, non-soft-deleted, and P.ReasonForAssessment IN (11133005, 11133006)
Pull data based on the following criteria: - D.DocumentCodeId = 60151 (PSC35)
- D.RecordDeleted = ‘N’
- D.Status = 22 (Signed)
Special Notes regarding the query: - Results are grouped by ClientId, returning MAX(EffectiveDate) as LastPSCDC
AnsaDC (CTE) — Last Discharge ANSA per Client/Program
Purpose: Finds each client/program’s most recent discharge-type ANSA assessment date. Pulls from Documents (D), with: - LEFT OUTER JOIN DocumentCaliforniaANSAInitials (A1) ON D.currentdocumentversionid = A1.documentversionid, non-soft-deleted
- LEFT OUTER JOIN DocumentCAANSAGenerals (A2) ON D.currentdocumentversionid = A2.documentversionid, non-soft-deleted
Pull data based on the following criteria: - D.ProgramId must be in the selected @Programs parameter list
- D.DocumentCodeId IN (60138, 60209) (both ANSA versions, old and new, since the form changed post go-live)
- D.RecordDeleted = ‘N’
- D.Status = 22 (Signed)
- A1.AssessmentType = ‘D’ OR A2.GeneralType = ‘E’ (old-form discharge type or new-form exit type)
Special Notes regarding the query: - Results are grouped by ClientId and ProgramId, returning MAX(EffectiveDate) as LastDCANSA
- The filter A1.assessmenttype = ‘D’ OR A2.generaltype = ‘E’ has no parentheses around the OR condition — depending on SQL operator precedence, this may not be scoped the way it looks; worth confirming this evaluates as intended alongside the other AND conditions above it
Inner Subquery (X) — Client/Program Assessment Details
Purpose: For each enrolled Client/Program combination, calculates the client’s demographics, applicable assessment types (by age), due dates, and most recently completed assessment dates. Pulls from ClientPrograms (CP), with: - INNER JOIN PROGS ON CP.programid = PROGS.programid
- INNER JOIN Clients (C) ON CP.clientid = C.clientid
- LEFT OUTER JOIN Staff (st) ON CP.AssignedStaffId = st.StaffId
- LEFT OUTER JOIN CansDC ON CP.clientid = CansDC.clientid
- LEFT OUTER JOIN PscDC ON CP.clientid = PscDC.clientid
- LEFT OUTER JOIN AnsaDC ON CP.clientid = AnsaDC.clientid AND CP.programid = AnsaDC.clientid
Plus four correlated subqueries (one each for LastCalAIM, LastASAM, LastCANS, LastPSC35, LastANSA — detailed below). Pull data based on the following criteria: - CP.programid must be in the selected @Programs parameter list
- CP.Status = 4 (Enrolled only)
- CP.EnrolledDate <= @EndDate + 1 day
- CP.DischargedDate (or current date, if not yet discharged) >= @StartDate
- CP.RecordDeleted = ‘N’
Special Notes regarding the query: - The AnsaDC join condition reads CP.programid = AnsaDC.clientid — this compares a ProgramId to a ClientId, which appears to be a typo (likely intended to be AnsaDC.programid); worth confirming and correcting, as written this join will not match as intended
- Age is calculated from DOB using a date-integer subtraction method, divided by 10000
- CANS applies (Y/N) when Age is between 6 and 20
- ANSA applies (Y/N) when Age is 21 or older
- PSC35 applies (Y/N) when Age is between 3 and 17
- Assigned Staff is concatenated as LastName + ‘,’ + FirstName
- ASAMDue, CANSDue, ANSADue, PSC35Due, and CalAIMDue are all calculated as CP.EnrolledDate + the corresponding due-days value from PROGS
- LastCalAIM subquery: pulls the most recent signed (Status = 22), non-deleted Document with DocumentCodeId 60160 for the same client/program, ordered by EffectiveDate DESC — no discharge-type distinction exists for this document type, so simply the latest is used
- LastASAM subquery: same pattern as LastCalAIM, using DocumentCodeId 60140 — no discharge-type distinction exists for this document type either
- LastCANS subquery: pulls the most recent signed, non-deleted CANS (DocumentCodeId 60142) joined to DocumentCaliforniaCANSGenerals where AssessmentType is NOT ‘A’ or ‘D’ (i.e., excludes discharge-type assessments), and only considers documents dated on/after the client’s last discharge CANS date (from CansDC), or 2023-07-01 if none exists — this is a client-level match (not client/program-level)
- LastPSC35 subquery: same pattern as LastCANS, using DocumentCodeId 60151 and excluding ReasonForAssessment codes 11133005/11133006, filtered to dates on/after the client’s last discharge PSC35 (from PscDC) or 2023-07-01
- LastANSA subquery: same pattern, using DocumentCodeId IN (60138, 60209), excluding old-form AssessmentType = ‘D’ and new-form GeneralType = ‘E’ (discharge/exit types), filtered to dates on/after the client/program’s last discharge ANSA (from AnsaDC) or 2023-07-01 — this one is matched at the client/program level
Main Query — Final Output
Purpose: Converts the due dates and last-completed dates from subquery X into human-readable Status labels for each assessment type, and calculates the next due date for recurring assessments. Pull data based on the following criteria: - No additional filtering occurs at this level — this is a presentation/formatting layer over subquery X
Special Notes regarding the query: - ASAMStatus: If ASAMDue is NULL, returns “N/A”. If no ASAM has been completed (LastASAM is NULL) and ASAMDue has already passed, returns “LATE: [ASAMDue date]”. If no ASAM has been completed and the due date hasn’t passed yet, returns “Due: [ASAMDue date]”. Otherwise, returns “Done: [LastASAM date]”. Note: a commented-out line indicates a prior version also checked whether the completed assessment was late (LastASAM < ASAMDue) — this late-check is currently disabled, so a late-but-completed assessment will simply show as “Done.”
- CalAIMStatus: Same logic pattern as ASAMStatus, using CalAIMDue/LastCalAIM. Same note applies — the late-completion check is commented out.
- CANSStatus: If the client’s age doesn’t qualify for CANS (CANS flag = ‘N’), returns “N/A”. If CANSDue is NULL, returns “N/A”. If no CANS completed and due date has passed, returns “LATE: [CANSDue date]”. If no CANS completed and not yet due, returns “Due: [CANSDue date]”. Otherwise returns “Done: [LastCANS date]”.
- ANSAStatus: Same pattern as CANSStatus, gated by the ANSA age-eligibility flag and using ANSADue/LastANSA.
- PSC35Status: Same pattern as CANSStatus, gated by the PSC35 age-eligibility flag and using PSC35Due/LastPSC35.
- NextCANSDue: If the client isn’t CANS-eligible by age, returns NULL. Otherwise, calculated as LastCANS + CANSSubsequenMonths (from PROGS).
- NextANSADue: Same pattern, using ANSA eligibility, LastANSA, and ANSASubsequenMonths.
- NextPSC35Due: Same pattern, using PSC35 eligibility, LastPSC35, and PSC35SubsequenMonths.
- None of the Status or NextDue calculations currently reference the WithinDischargeDays fields (CANSWithinDischargeDays, ANSAWithinDischargeDays, PSC35WithinDischargeDays) calculated in PROGS — these are selected in subquery X but not used anywhere in the final output logic; worth confirming whether this is intentional or missing logic
|
