MCEncounterDataSet | Service Note (Client) | N | Dataset is the primary dataset for the report. Builds one row per Mobile Crisis Period per client using four staging temp tables (#MCPeriod, #MCPeriodDoc, #ClientCIN, #StaffDegree), then joins them together in the main query. Report Date Range Setup
Pull data based on the following criteria: - @BeginningDateRange is set to the first day of the quarter based on the selected @Year and @Quarter (Q1 = Jan 1, Q2 = Apr 1, Q3 = Jul 1, Q4 = Oct 1)
- @EndDateRange is set to the last day of the third month following @BeginningDateRange (i.e., the last day of the selected quarter)
Special Notes regarding the query: - A 3-day padding is applied on either side of this range later in the script (in #MCPeriod) to ensure Dispatch and Follow-Up documents just outside the quarter boundary are still captured.
County Code Lookup
Pull data based on the following criteria: - The database name is parsed to extract the county name (logic differs slightly for PROD vs. non-PROD database naming conventions)
- The extracted county name is matched against a hardcoded VALUES list mapping county name to a 2-digit county code
- @CountyCode is set to the matching code
Temp Table #MCPeriod :: Identify Mobile Crisis Periods
Purpose: Identifies every Mobile Crisis Dispatch Screening encounter where the mobile crisis team was deployed. Each qualifying encounter marks the “middle” of one Mobile Crisis Period. Pulls from Documents (D), with: - INNER JOIN Services (S) ON D.ServiceId = S.ServiceId AND S.RecordDeleted = ‘N’
- INNER JOIN ProcedureCodes (PC) ON S.ProcedureCodeid = PC.ProcedureCodeid AND PC.ProcedureCodeName = ‘Mobile Crisis Encounter’
- INNER JOIN CustomDocumentCalMHSACrisisProgressNote (MCPN) ON D.CurrentDocumentVersionId = MCPN.DocumentVersionId AND MCPN.RecordDeleted = ‘N’
- INNER JOIN Programs (P) ON S.ProgramId = P.ProgramId AND P.RecordDeleted = ‘N’
- LEFT JOIN ClientPrograms (CP) ON S.Clientid = CP.Clientid AND S.Programid = CP.Programid AND CP.RecordDeleted = ‘N’, AND the Service’s Date of Service falls between the client’s Enrolled Date and Discharged Date (or Date of Service itself, if not yet discharged)
Pull data based on the following criteria: - D.RecordDeleted = ‘N’
- D.Status = 22 (Signed documents only)
- S.DateofService falls within the selected quarter, padded 3 days before @BeginningDateRange and 3 days after @EndDateRange
- P.ServiceAreaId = 1 if @ServiceArea = ‘MHP’, otherwise ServiceAreaId = 3
- Results are grouped by Client, Document Version, Date of Service, Program, Program Name, and Author. MIN(EnrolledDate) and MAX(DischargedDate) (defaulting to GETDATE() if not yet discharged) are taken per group
Special Notes regarding the query: - After the base query, LEAD() and LAG() (partitioned by ClientId, ordered by MCEncounterDateofService) calculate the Next and Previous encounter dates for each client which define the boundaries of each Mobile Crisis Period. This will be used in temp table #MCPeriodDoc
Temp Table #MCPeriodDoc :: Attach Follow-Up and Dispatch Documents to Each Period
Purpose: For each Mobile Crisis Period identified in #MCPeriod, finds the matching Follow-Up document Dispatch Screening document (within the period). Follow-Up document lookup (correlated subquery, TOP 1): - Documents (D) INNER JOIN Services (S) ON D.ServiceId = S.ServiceId AND S.RecordDeleted = ‘N’ AND S.ProgramId matches the period’s Program
- INNER JOIN ProcedureCodes (PC) ON PC.ProcedureCodeName = ‘Mobile Crisis Follow-Up’
- D.RecordDeleted = ‘N’, D.Status = 22, D.Clientid matches the period’s Client
- S.DateofService falls between the period’s MCEncounterDateofService and the Next encounter date (or, if there is no next encounter, the client’s program discharge date + 3 days padding)
- Only the single most recent match is kept, ordered by Date of Service DESC, then Created Date DESC
Dispatch Screening document lookup (correlated subquery, TOP 1): - Documents (D) INNER JOIN Services (S) ON D.ServiceId = S.ServiceId AND S.RecordDeleted = ‘N’
- INNER JOIN ProcedureCodes (PC) ON PC.ProcedureCodeName = ‘Mobile Crisis Dispatch Screening’
- D.RecordDeleted = ‘N’, D.Status = 22, D.Clientid matches the period’s Client
- S.DateofService falls between the Previous encounter date (or, if none, the client’s program enrolled date − 3 days padding) and the current period’s MCEncounterDateofService
- Only the single most recent match is kept, ordered by Effective Date DESC, then Created Date DESC
Special Notes regarding the query: - Two additional columns (MCEnrolled3DayPadding, MCDischarged3DayPadding) output the padded enrollment/discharge dates used above, included for troubleshooting purposes only
Temp Table #ClientCIN :: Client Medi-Cal CIN Lookup
Purpose: Retrieves each client’s most recently added Medi-Cal Client Identification Number (CIN). Pulls from ClientCoveragePlans (ccp), with: - LEFT JOIN CoveragePlans (cp) ON ccp.CoveragePlanId = cp.CoveragePlanId AND cp.RecordDeleted = ‘N’
Pull data based on the following criteria: - ccp.RecordDeleted = ‘N’
- cp.MedicaidPlan = ‘Y’ (Medi-Cal plans only)
- ccp.InsuredId must be at least 9 characters, start with “9”, and have a letter as the 9th character (valid CIN format)
- ccp.InsuredId must not start with “99999999” (excluding placeholder CINs)
- Distinct Client/CIN/Coverage Plan combinations are ranked per client by ClientCoveragePlanID DESC; only the top-ranked (most recent) CIN per client is kept
Temp Table #StaffDegree :: Staff Billing Degree Lookup
Purpose: Retrieves each staff member’s most recently added billable license/degree, mapped to its DHCS-reportable code. Pulls from StaffLicenseDegrees (SLD), with: - LEFT JOIN ExternalMappings (ExDegree) ON SLD.LicenseTypeDegree = ExDegree.RecordId AND ExDegree.Purpose = ‘CalMHSAMobileCrisis’ AND ExDegree.RecordDeleted = ‘N’
Pull data based on the following criteria: - SLD.RecordDeleted = ‘N’
- SLD.Billing = ‘Y’ (billable degrees only)
- The degree’s End Date (or @BeginningDateRange, if no End Date) must be on or after @BeginningDateRange
- The degree’s Start Date (or @EndDateRange, if no Start Date) must be on or before @EndDateRange
- Records are ranked per staff member by Created Date DESC; only the top-ranked (most recently created) degree per staff member is kept
Main Query :: Final Report Output
Purpose: Joins #MCPeriodDoc, #ClientCIN, and #StaffDegree temp tables to produce one final row per Mobile Crisis Period per client, mapped to the DHCS reporting schema. Core joins: - FROM #MCPeriodDoc (MCPD)
- LEFT JOIN CustomDocumentCalMHSACrisisProgressNote (CCPN) ON the Mobile Crisis Encounter document version, RecordDeleted = ‘N’
- LEFT JOIN CustomDocumentCalMHSAMobileCrisisDispatch (CMCD) ON the Dispatch document version, RecordDeleted = ‘N’
- LEFT JOIN CustomDocumentCalMHSAMobileCrisisFU (CMCF) ON the Follow-Up document version, RecordDeleted = ‘N’
- LEFT JOIN Documents/Services (MCFUDoc/MCFUSer) to retrieve the Follow-Up’s Date of Service
- LEFT JOIN Documents/Services (MCDisDoc/MCDisSer) to retrieve the Dispatch’s Date of Service
- LEFT JOIN #ClientCIN, #StaffDegree (x3, for up to 3 responders) on Client/Staff ID
- LEFT JOIN GlobalCodes (multiple aliases) to decode Dispatch Channel, Crisis Location, Location Type, Law Enforcement Involvement, Transport Destination, Transport Type, Provider Telehealth Location/Type, Specialist/Interpreter Telehealth Type, Disposition, No-Follow-Up Reason, Follow-Up Result, and Referral. All of which are filtered to RecordDeleted = ‘N’
- INNER JOIN Clients (C) ON MCPD.Clientid = C.Clientid AND C.RecordDeleted = ‘N’
Pull data based on the following criteria: - MCPD.MCEncounterDateofService must fall within @BeginningDateRange and @EndDateRange (Unpadded. This is the final trim back to the exact quarter, after the padded windows were used to find related documents)
- Client record must be non-soft-deleted
- All descriptive lookup joins are LEFT JOINs, so a missing/unmatched code does not exclude the record — it simply returns blank/default values
Special Notes regarding the query: - locationOfMobileCrisisServiceEncounter: Maps CCPN.LocationType (via LocType.ExternalCode2) to DHCS display text: Bus → “Business”, Home → “Home”, Camp → “Homeless Encampment”, Shelter → “Homeless Shelter”, School → “School”, Work → “Workplace”, Other → “Other”; any unmatched code returns blank.
- standardResponseTime: Based on CrisisLoc.ExternalCode1 (Urban/Rural classification of the crisis location): “Urban” maps to the DHCS Standard Response Time 1 goal text (<60 min, for areas >50 people/sq mi); “Rural” maps to the Standard Response Time 2 goal text (<120 min, for areas <50 people/sq mi); any other/missing value returns blank.
- lawEnforcementInvolvementOtherExplanation: Only populated when LEInv.CodeName = ‘Yes, Other (explain)’; pulls the free-text CCPN.LEInvolvedOther field (cleaned via csf_CustomCalMHSAWhitespaceCleanup, quotes normalized). Otherwise blank.
- transportationDestination: If CCPN.TransportDestination is NULL, defaults to “None – Member Stabilized” (i.e., no transport occurred). Otherwise maps TranDest.ExternalCode2 to display text: CRT → “Crisis Residential Treatment Facility”, CSU → “Crisis Stabilization Unit”, ED → “Hospital ED”, Jail → “Jail”, IP → “Psychiatric Inpatient Facility”, None → “None – Member Stabilized”, Other → “Other”; unmatched returns blank.
- transportationDestinationOtherExplanation: Only populated when TranDest.ExternalCode2 = ‘Other’; pulls the free-text CCPN.TransportDestinationOther field (cleaned/normalized same as above). Otherwise blank.
- transportationVehicleUsed: If CCPN.TransportType is NULL, defaults to “None”. Otherwise maps TranType.ExternalCode2: EMS → “Ambulance”, LE → “Law Enforcement”, MCRT → “Mobile Crisis Response Team (MCRT)”, MCP → “Medi-Cal Managed Care Plan (MCP) transport”, Taxi → “Taxi”, Uber → “Uber / Lyft”, None → “None”, Other → “Other”; unmatched returns blank.
- telehealthUsed: Checks whether the second or third responder’s location (Pro2Loc/Pro3Loc CodeName) was “Telehealth” or “Onsite.” If either responder shows “Telehealth,” the value is “Yes”; if either shows “Onsite” (and neither shows Telehealth), the value is “No”; otherwise blank.
- telehealthType: If Pro2Loc = “Telehealth,” pulls Pro2LocType.CodeName (the specific telehealth modality for responder 2). Else if Pro3Loc = “Telehealth,” pulls Pro3LocType.CodeName for responder 3. If neither responder used telehealth, defaults to “None.”
- specialistAndOrInterpreterTelehealthUsed: Based on CCPN.SpecConsultTH (specialist consult) and CCPN.InterpConsultTH (interpreter consult) flags: both ‘Y’ → “Yes – Specialist consult and Interpreter service”; only SpecConsultTH ‘Y’ → “Yes – Specialist consult”; only InterpConsultTH ‘Y’ → “Yes – Interpreter service”; both ‘N’ → “No”; any other combination (e.g., NULLs) returns blank.
- specialistAndOrInterpreterTelehealthType: When both SpecConsultTH and InterpConsultTH are ‘Y’: if the specialist’s telehealth type (SpecConTeleType.CodeName) matches the interpreter’s (InterpConTeleType.CodeName), returns that shared value; if they differ, returns “Both”. If only one of the two flags is ‘Y’, returns that one’s telehealth type. If both flags are ‘N’, returns “None”. Otherwise blank.
- specialistAndOrInterpreterTelehealthTypeDetail: When both flags are ‘Y’, or when only SpecConsultTH is ‘Y’, returns InterpSpecCon.CodeName (the specialist consult type detail). When only InterpConsultTH is ‘Y’, returns the literal “Interpreter service”. When both are ‘N’, returns “None”. Otherwise blank.
Note: this logic returns InterpSpecCon.CodeName even in the “only specialist” branch, worth confirming this isn’t meant to reference a specialist-specific code column instead, since the column name (Interp-prefixed) reads as interpreter-related. - specialistAndOrInterpreterTelehealthTypeOtherExplanation: Only populated when InterpSpecCon.CodeName = ‘Other (explain)’; pulls the free-text CCPN.SpecTypeOther field (cleaned/normalized). Otherwise blank.
- dispostionOfEncounter: Maps CCPN.Disposition (via MCDisp.ExternalCode2) to DHCS display text: ED → “Admitted to Emergency Dept”, Community → “Resolved in Community Setting”, Alt → “Transported to Alternative Setting”, Warm Handoff → “Warm handoff”, Refusal → “Refusal of Service”; unmatched returns blank.
- followUp72HourAttempted: Directly reflects CMCF.FollowUpAttempted: ‘Y’ → “Yes”, ‘N’ → “No”, otherwise blank.
- followUp72HourNotAttempted: Only populated when FollowUpAttempted = ‘N’. If the no-follow-up reason (NoFURea.CodeName) is “Other,” returns the free-text CMCF.NoFUReasonOther (cleaned/normalized) instead of the code’s display name; otherwise returns NoFURea.CodeName directly. Blank if FollowUpAttempted = ‘Y’.
- followUp72HourResult: Only populated when FollowUpAttempted = ‘Y’; returns FURes.CodeName (the follow-up outcome). Blank otherwise.
- referralsToOngoingServices: Maps CMCF.OngoingSrvsReferral (via FURef.ExternalCode2) to full DHCS category descriptions (e.g., PCP → “Primary care providers”, OP BH → “Outpatient behavioral health treatment providers…”, Crisis → “Crisis receiving and stabilization facilities”, etc., 10 categories total). Defaults to “None” for any unmatched/missing code (note: this differs from most other CASE blocks above, which default to blank instead of “None”).
- MemberEndData: Uses LEAD(MCEncounterDateofService) partitioned by ClientId, ordered by encounter date, to detect whether a row is a client’s last Mobile Crisis Period in the dataset. Returns 1 if there is no later encounter for that client (i.e., this is their most recent period), 0 otherwise.
- LastDataRecord: Uses LEAD(ClientId) over the entire result set (ordered by ClientId) to flag the single last row of the whole report output. Returns 1 for the final row, 0 for all others.
- “Other” or free-text fields (e.g., LEInvolvedOther, TransportDestinationOther, NoFUReasonOther) are cleaned via csf_CustomCalMHSAWhitespaceCleanup() and have double quotes replaced with single quotes
- MemberEndData flags the last record per client (via LEAD() on Date of Service, partitioned by client)
- LastDataRecord flags the very last record in the entire result set (via LEAD() on ClientId, unpartitioned)
- Final output columns are aliased to match the DHCS Mobile Crisis reporting schema field names exactly (e.g., memberCIN, dateOfMobileCrisisServiceEncounter, dispatchChannel)
- All temp tables (#MCPeriod, #MCPeriodDoc, #ClientCIN, #StaffDegree) are dropped at the end of the script
|
