sql_funcs reference table

Query Examples

Category	I want to…	Function	Documentation
Setup	Set up a table of all clients in Neon in 2024	e = Queries(t1 = ‘2024-01-01’, t2=’2024-12-31’)	__init__
Setup	Set up a table of CHD clients in Q3 of 2024	e = Queries(t1= ‘2024-07-01’, t2= ‘2024-09-30’, default_table = stints.neon_chd)	__init__
Assessments	Get a count of clients with their earliest ASSM scores between 1-2	e.assess_assm()	assess_assm
Assessments	Get a count of clients with their latest ASSM scores between 1-3	e.assess_assm(cutoff_score=3, score_date=’max’) Note: ASSM Scores by Category	assess_assm
Assessments	Get a breakdown of ASSM score improvements in the timeframe	e.assess_assm_improvement(timeframe = True)	assess_assm_improvement
Assessments	Get the number of clients whose ASSM increased in an ISP goal area	e.assm_improvement(isp_goals = True) Note: ASSM Score Changes	assess_assm_improvement
Assessments	Get clients missing outreach assessments	e.assess_missing_outreach() Note: Outreach Clients Missing Assessments	assess_missing_outreach
Assessments	Get count of assessment score changes within timeframe	e.assess_score_change()	assess_score_change
Assessments	Get all-time count of assessment score changes for initial scores of 32+	e.assess_score_change(timeframe=False, min_score=32) Note: Case Management Assessment Score Changes	assess_score_change
Custody Statuses	Get a record of each clients’ latest custody status	e.custody_status()	custody_status
Custody Statuses	Get the number of clients with each custody status	e.custody_status(summary_table=True) Note: Custody Statuses (Individual or Grouped)	custody_status
Demographics	Get a table of each client’s address	e.dem_address()	dem_address
Demographics	Get a count of the number of client’s in each neighborhood	e.dem_address(group_by=’community’)	dem_address
Demographics	Get a count of the number of new clients in each zipcode	e.dem_address(new_clients=True, group_by=’zip’) Note: Client Addresses	dem_address
Demographics	Get the number of clients currently under 19	e.dem_age(age=19) Get the number of new clients under 18: e.dem_age(new_clients=True) Note: Client Ages	dem_age
Demographics	Get the genders of new clients	e.dem_race_gender(new_clients=True, race_gender=’gender’)	dem_race_gender
Demographics	Get client races	e.dem_race_gender() Note: Client Races or Genders	dem_race_gender
Client Enrollment	Get the total number of clients enrolled	e.enrollment()	enrollment
Client Enrollment	Get the number of clients enrolled in each program	e.enrollment(program_type=True)	enrollment
Client Enrollment	Get the number of clients receiving each service for every program	e.enrollment(program_type=True, service_type=True)	enrollment
Client Enrollment	Get the number of clients receiving each service on a grant	e.enrollment(service_type=True, grant_type=True) Note: Total Clients Enrolled (overall or by program/service/grant)	enrollment
Client Enrollment	Get the number of clients enrolled in each combination of programs	e.enrollment_bundles() Note: Client Program Combinations	enrollment_bundles
Client Enrollment	Get the number of clients enrolled/unenrolled in the timeframe	e.enrollment_flow() Note: Enrollment Status Changes	enrollment_flow
Critical Incidents	Get the number of incident responses in the timeframe	e.incident_response() Note: Critical Incident Response Count	incident_response
Critical Incidents	Get the number of incidents in the timeframe	e.incident_tally() Note: Critical Incident Count	incident_tally
ISPs	Get the number of discharged clients broken out by % of their service plan completed	e.isp_discharged()	isp_discharged
ISPs	Get a list of discharged clients with no ISPs recorded	e.isp_discharged(missing_names=True) Note: ISP Completion for Discharged Clients	isp_discharged
ISPs	Get the status of client ISP goals by domain	e.isp_goal_tracker() Note: ISP Completion by Goal Area	isp_goal_tracker
ISPs	Get a full table of ISP statuses for all clients	e.isp_tracker(cm_only=False)	isp_tracker
ISPs	Get the count of case management clients missing a service plan after 60 days	e.isp_tracker(summary_table=True, service_days_cutoff=60) Note: ISP Status (Individual or Grouped)	isp_tracker
Legal	Get client outcomes in the time period	e.legal_bonanza(timeframe=True, ranking_method=’highest_outcome’, grouping_cols=’case_outcomes’)	legal_bonanza
Legal	Get types of cases begun in time period	e.legal_bonanza(timeframe=True, case_stage = ‘started’, grouping_cols=’case_type’)	legal_bonanza
Legal	Get case outcomes grouped by violent status	e.legal_bonanza(timeframe=True, case_stage=’ended’, grouping_cols=’case_outcome’, wide_col=’violent’) Note: Legal Information	legal_bonanza
Legal	Get the number of new cases picked up by clients	e.legal_rearrested(client_level=False)	legal_rearrested
Legal	Get the number of clients rearrested	e.legal_rearrested() Note: Recidivism	legal_rearrested
Legal	Get RJCC cases ended in timeframe	e.legal_rjcc(client_level=False)	legal_rjcc
Legal	Get the number of clients who completed RJCC	e.legal_rjcc() Note: RJCC Enrollment in MyCase	legal_rjcc
Linkages	Get the number of case management clients enrolled/employed in their first 9 months	e.linkages_edu_employ(first_n_months=9)	linkages_edu_employ
Linkages	Get the number of clients currently enrolled/employed with an age cutoff of 19	e.linkages_edu_employ(cm_only=False, ongoing=True, age_threshold=19)	linkages_edu_employ
Linkages	Get the number of case management clients enrolled/employed excluding workforce development linkages	e.linkages_edu_employ(include_wfd=False)	linkages_edu_employ
Linkages	Get the number of case management clients enrolled/employed after nine months	e.linkages_edu_employ(new_client_threshold = 275) Note: Education and Employment Linkage Table	linkages_edu_employ
Linkages	Get the number of case management clients with lclc-initiated linkages in the current time period and their first 3/6/9 months	e.linkages_monthly()	linkages_monthly
Linkages	Get the number of all clients with linkages in the time period	e.linkages_monthly(cm_only=True)	linkages_monthly
Linkages	Get the number of case management clients with linkages in the time periods, including client-initiated linkages	e.linkages_monthly(lclc_initiated=False) Note: Number of Clients Linked by Time Period	linkages_monthly
Linkages	Get the types of linkages recorded for all clients in the timeframe	e.linkages_tally()	linkages_tally
Linkages	Get the number of case management clients with an internal/external linkage at any time	e.linkages_tally(cm_only=True, timeframe=False, distinct_clients=True, group_by=’internal_external’)	linkages_tally
Linkages	Get the number of clients with a started linkage of each type in the timeframe	e.linkages_tally(distinct_clients=True, link_started=True)	linkages_tally
Linkages	Get the number of clients linked to organizations at any time, with the linkage currently ongoing	e.linkages_tally(distinct_clients=True, group_by=’linkage_org’, link_started=True, link_ongoing=True) Note: Linkage Information (Flexible)	linkages_tally
Outreach Assessments	Get the number of new outreach clients with eligibility screenings/number of “yes”es for each question	e.outreach_elig_tally(new_clients=True)	outreach_elig_tally
Outreach Assessments	Get the number of all clients with eligibility screenings//number of “yes”es for each question	e.outreach_elig_tally(outreach_only=False) Note: Outreach Eligibility Form Responses	outreach_elig_tally
Case Sessions	Get the session regularity of case management clients	e.session_frequency()	session_frequency
Case Sessions	Get the session regularity of outreach clients	e.session_frequency(session_type=”Outreach”) Note: CM or Outreach Session Frequency	session_frequency
Case Sessions	Get the number of clients with a case management session in the timeframe	e.session_tally()	session_tally
Case Sessions	Get the number of outreach sessions in the timeframe	e.session_tally(session_type=’Outreach’, distinct_participants=False) Note: CM or Outreach Session Tally	session_tally