DataSet1 | Clients Information (Clients)
California CANS (Clients)
| Y | Dataset is the primary dataset for the report. Built using one CTE (ClientList) plus a main query that joins in CANS sub-assessment tables and calculates domain flags. ClientList (CTE) — Enrolled Clients with Most Recent CANS
Purpose: Identifies clients aged 6–20 currently enrolled in selected Programs, and finds each client’s single most recent CANS document version. Pulls from ClientPrograms (CP), with: - INNER JOIN Clients (C) ON CP.clientid = C.clientid, non-soft-deleted
- INNER JOIN Programs (P) ON CP.programid = P.programid, non-soft-deleted
- INNER JOIN StaffClients(SC) ON SCStaffId = @ExecutedByStaffId AND C.Clientid = SC.Clientid
- Correlated subquery (docversion): for each client, finds the top 1 DocumentVersionId from DocumentCaliforniaCANSGenerals joined to Documents (non-soft-deleted), ordered by DateOfAssessment DESC, then CreatedDate DESC
Pull data based on the following criteria: - CP.Status = 4 (currently enrolled)
- CP.ProgramId must be in the selected @Programs parameter list
- CP.RecordDeleted = ‘N’
- Client’s current age (calculated from DOB) must be between 6 and 20
Special Notes regarding the query: - Age is calculated using DATEDIFF(year, DOB, current date), adjusted down by 1 if the birthday hasn’t yet occurred this year (a more precise age calculation than a simple year subtraction)
- Results are grouped by ClientId; ClientName, DOB, and CurrentAge are carried through via MAX() (safe here since they’re constant per client), CurrentPrograms concatenates all matching Program names via STRING_AGG, and maxdoc takes the MAX() of the single per-client docversion subquery result
Main Query — Domain Scoring and Filtering
Purpose: For each client in ClientList, pulls their most recent CANS assessment detail (age 6+ version) and calculates five domain-level Y/N flags (Behavioral/Emotional, Caregiver, Life Functioning, Risk Behaviors, Strengths) based on individual item scores, plus a “triggers” text field listing which specific items drove each flag. Final result is filtered to clients meeting a specific combination of domain flags. Pulls from ClientList, with: - LEFT OUTER JOIN DocumentCaliforniaCANSGenerals (G) ON ClientList.maxdoc = G.DocumentVersionId, non-soft-deleted, AND G.Age > 5 (restricts to the 6+ version of the CANS form)
- LEFT OUTER JOIN DocumentCaliforniaCANSGeneralBehavioralEmotions (GBEH) ON G.DocumentVersionId = GBEH.DocumentVersionId, non-soft-deleted
- LEFT OUTER JOIN DocumentCaliforniaCANSGeneralLFDomains (GLF) ON G.DocumentVersionId = GLF.DocumentVersionId, non-soft-deleted
- LEFT OUTER JOIN DocumentCaliforniaCANSGeneralRiskBehaviors (GRSK) ON G.DocumentVersionId = GRSK.DocumentVersionId, non-soft-deleted
- LEFT OUTER JOIN DocumentCaliforniaCANSGeneralStrengthsDomains (GSTR) ON G.DocumentVersionId = GSTR.DocumentVersionId, non-soft-deleted
- LEFT OUTER JOIN DocumentCaliforniaCANSCaregiverResourcesNeeds (CARE) ON G.DocumentVersionId = CARE.DocumentVersionId, non-soft-deleted, AND CARE.DocumentCaliforniaCaregiverNeedId equals the MIN() caregiver-need ID for that document version (limits to just the first/lowest-ID caregiver record, since a CANS can have multiple caregivers)
Pull data based on the following criteria (outer WHERE, applied after domain flags are calculated): - BEH_DOMAIN = ‘Y’
- CARE_DOMAIN = ‘Y’
- At least one of: LIFE_DOMAIN = ‘Y’, RISK_DOMAIN = ‘Y’, or STR_DOMAIN = ‘Y’
Special Notes regarding the query: - dateofassessment / DaysSince: Pulled from G.DateOfAssessment; DaysSince calculates the number of days between the assessment date and today.
- BEH_DOMAIN: Flags ‘Y’ if any of 9 Behavioral/Emotional items (Psychosis, Impulsivity/Hyperactivity, Depression, Anxiety, Anger Control, Adjustment to Trauma, Oppositional, Conduct, Substance Use) score 2 or higher; otherwise ‘N’.
- BEH_TRIGGERS: Concatenates a text list of only the items that scored 2+ (item name, score, and a line break), so the field shows which specific item(s) drove the BEH_DOMAIN flag.
- CARE_DOMAIN: Flags ‘Y’ if any of 10 Caregiver items (Supervision, Involvement With Care, Knowledge, Social Resources, Residential Stability, Medical/Physical, Mental Health, Substance Use, Developmental, Safety) score 2 or higher; otherwise ‘N’.
- CARE_TRIGGERS: Same pattern as BEH_TRIGGERS — lists only the Caregiver items scoring 2+.
- LIFE_DOMAIN: Flags ‘Y’ if EITHER (a) at least one of 11 Life Functioning items (Family Functioning, Living Situation, Social Functioning, Developmental/Intellectual, Decision Making, School Behavior, School Achievement, School Attendance, Medical/Physical, Sexual Development, Sleep) scores 3 or higher, OR (b) at least two of those same 11 items score 2 or higher. Otherwise ‘N’.
- LIFE_TRIGGERS: If any item scored 3+, lists only those items (name, score, line break). If no item scored 3+ (i.e., the flag was driven only by the “two or more 2s” criterion), falls back to listing the items that scored 2+ instead.
- RISK_DOMAIN: Flags ‘Y’ if EITHER (a) at least one of 8 Risk Behavior items (Suicide Risk, Non-Suicidal Self-Injurious Behavior, Other Self-Harm, Danger to Others, Sexual Aggression, Delinquent Behavior, Runaway, Intentional Misbehavior) scores 2 or higher, OR (b) at least three of those same 8 items score 1 or higher. Otherwise ‘N’.
- RISK_TRIGGERS: If any item scored 2+, lists only those items. If none scored 2+ (i.e., flag driven only by the “three or more 1s” criterion), falls back to listing items that scored 1+ instead.
- STR_DOMAIN: Flags ‘Y’ if 5 or more of 9 Strengths items (Family Strengths, Interpersonal, Educational Setting, Talents and Interests, Spiritual/Religious, Cultural Identity, Community Life, Natural Supports, Resilience) score 2 or higher; otherwise ‘N’.
- STR_TRIGGERS: Lists only the Strengths items scoring 2+.
- Score values (e.g., GBEH.Psychosis, CARE.Supervision) are concatenated directly into trigger strings with + without an explicit CAST to varchar — this relies on implicit conversion and may error or behave unexpectedly depending on the column’s actual data type; worth confirming these columns are already stored as a string-compatible type.
- In STR_TRIGGERS, the CommunityLife entry is built as ‘CommunityLife’ + GSTR.CommunityLife — missing the trailing hyphen (-) that every other item in this field includes (e.g., “FamilyStrengths-2”), so this one entry will read differently (e.g., “CommunityLife2” instead of “CommunityLife-2”). Worth confirming this is a typo.
- Large blocks of individual raw score columns (e.g., GBEH.Psychosis as BEH_Psychosis) are commented out throughout the query and not included in the final output — only the calculated DOMAIN flags and TRIGGERS text fields are returned.
|
