sql_funcs documentation

Contents

sql_funcs documentation#

General Functions#

class sql_funcs.Queries(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

Bases: Audits

__init__(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

establishes settings and runs stints for the desired time period

Parameters:

  • t1 – start date, formatted as “YYYY-MM-DD”

  • t2 – end date, formatted as “YYYY-MM-DD”

  • print_sql (Bool) – whether to print the SQL statements when run, defaults to True

  • clipboard (Bool) – whether to copy the output table to your clipboard, defaults to False

  • default_table – the source table to run queries on. defaults to “stints.neon”, can also use “stints.neon_chd”, or a participants table

  • mycase (Bool) – whether the user has a formatted MyCase SQL database, defaults to True

Examples

Set up a table of all clients in Neon in 2024

e = Queries(t1 = '2024-01-01', t2='2024-12-31')

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)
assess_assm(cutoff_score=2, score_date='min')#

Counts clients with ASSM scores in a certain range.

Parameters:

  • cutoff_score – the upper bound of score to include. Defaults to 2

  • score_date (str) – ‘min’ returns the earliest score, ‘max’ returns the latest. Defaults to ‘min’

Examples

Get a count of clients with their earliest ASSM scores between 1-2:

e.assess_assm()

Get a count of clients with their latest ASSM scores between 1-3:

e.assess_assm(cutoff_score=3, score_date='max')

Note

Assessments - ASSM Scores by Category

assess_assm_improvement(timeframe=False, isp_goals=False)#

Returns the number of clients with ASSM improvements in each category

Parameters:

  • timeframe (Bool) – whether to only look at ASSM scores in the timeframe (and a client’s original). Defaults to False

  • isp_goals (Bool) – Only look at score improvements for ISP goal domains. Defaults to False

Example

Get a breakdown of ASSM score improvements in the timeframe:

e.assess_assm_improvement(timeframe = True)

Get the number of clients whose ASSM increased in an ISP goal area:

e.assm_improvement(isp_goals = True)

Note

Assessments - ASSM Score Changes

assess_outreach_completion()#

Returns a table of outreach assessments (and total number of assessments) and their completion rates within 30 days/while working with a client/

Note

Assessments - Outreach Completion Rates

assess_cm_tracker(active_only=True, new_clients=False, summary_table=False)#

Returns a table of clients with CM assessments and their latest assessment dates.

Parameters:

  • active_only (Bool) – whether to only look at clients with active CM services. Defaults to True

  • new_clients_only (Bool) – whether to only look at clients with new CM services in the timeframe. Defaults to False

  • summary_table (Bool) – whether to return a summary table of clients with assessments. Defaults to False

Note

Assessments - Case Management Tracker

assess_score_change(timeframe=True, min_score=None)#

Returns a table of CM assessments & their score changes.

Parameters:

  • timeframe (Bool) – only looks at score changes in the timeframe. Defaults to True

  • min_score – the lowest pre-assessment score to consider. Defaults to None

Examples

Get count of assessment score changes within timeframe:

e.assess_score_change()

Get all-time count of assessment score changes for initial scores of 32+:

e.assess_score_change(timeframe=False, min_score=32)

Note

Assessments - Case Management Score Changes

assess_risk_factor_assessments(timeframe=True, distinct_clients=False)#

Counts # of risk factor, protective, and strength-based inventories for clients.

Parameters:

  • timeframe (Bool) – Whether to only count assessments in timeframe. Defaults to True

  • distinct_clients (Bool) – Whether to count multiple assessments per client. Defaults to False

Note

Assessments - Count by Inventory Type (protective, risk-factor, strength-based)

custody_status(summary_table=False)#

Returns a table of clients’ most recent custody statuses

Parameters:

summary_table (Bool) – groups clients by latest custody status. Defaults to False

Examples

Get a record of each clients’ latest custody status:

e.custody_status()

Get the number of clients with each custody status:

e.custody_status(summary_table=True)

Note

Legal - Custody Statuses (Individual or Grouped)

dem_address(new_clients=False, group_by=None)#

returns client address records.

Parameters:

  • new_clients (bool) – include only new clients. Defaults to False

  • group_by – group client records, takes ‘zip’, ‘community’, ‘region’. Defaults to None.

Examples

Get a table of each client’s address:

e.dem_address()

Get a count of the number of client’s in each neighborhood:

e.dem_address(group_by='community')

Get a count of the number of new clients in each zipcode:

e.dem_address(new_clients=True, group_by='zip')

Note

Demographics - Client Addresses

dem_age(new_clients=False, age=18)#

Returns a count of clients below/above a certain age threshold, or identifies clients as juveniles/adults

Parameters:

  • new_clients (Bool) – if true, only counts clients who began between t1 and t2. defaults to False

  • tally (Bool) – if true, returns a count of juv/adults, if false, returns a list. defaults to True

  • age – threshold at which a client is counted as a juvenile. defaults to 18

Examples

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

Demographics - Client Ages

dem_race_gender(race_gender='race', new_clients=False)#

Returns a count of client races or genders

Parameters:

  • new_clients (Bool) – if true, only counts clients who began between t1 and t2. defaults to False

  • race_gender – the category to tally, enter either “race” or “gender”. defaults to ‘race’

Examples

Get the genders of new clients:

e.dem_race_gender(new_clients=True, race_gender='gender')

Get client races:

e.dem_race_gender()

Note

Demographics - Client Races/Genders

dem_edu_employ_tracker()#

Makes a big, yucky table of education/employment data from linkages + intake

enrollment(program_type=False, service_type=False, grant_type=False)#

Returns a count of clients, with options to break down by program, service, and/or grant.

Parameters:

  • program_type (Bool) – distinguish by program, defaults to False

  • service_type (Bool) – distinguish by service, defaults to False

  • grant_type (Bool) – distinguish by grant, defaults to False

Examples

Get the total number of clients enrolled:

e.enrollment()

Get the number of clients enrolled in each program:

e.enrollment(program_type=True)

Get the number of clients receiving each service for every program:

e.enrollment(program_type=True, service_type=True)

Get the number of clients receiving each service on a grant:

e.enrollment(service_type=True, grant_type=True)

Note

Enrollment - Total Clients (overall or by program/service/grant)

enrollment_bundles(enrollment_level='program')#

Counts clients by their bundle of programs

Example

Get the number of clients enrolled in each combination of programs:

e.enrollment_bundles()
Parameters:

enrollment_level – ‘program’ or ‘grant’

Note

Enrollment - Client Program Combinations

enrollment_caseloads(if_legal=False)#
enrollment_flow()#

Counts the flow of clients in and out of LCLC in the timeframe.

Example

Get the number of clients enrolled/unenrolled in the timeframe:

e.enrollment_flow()

Note

Enrollment - Status Changes

enrollment_monthly(program_type=False, service_type=False, grant_type=False, distinct_clients=True)#

Returns a count of clients, with options to break down by program, service, and/or grant for each month in the timeframt

Parameters:

  • program_type (Bool) – distinguish by program, defaults to False

  • service_type (Bool) – distinguish by service, defaults to False

  • grant_type (Bool) – distinguish by grant, defaults to False

  • distinct_clients (Bool) – count distinct clients (True) or distinct services (False). Defaults to True

Examples

Get the total number of clients enrolled:

e.enrollment()

Get the number of clients enrolled in each program:

e.enrollment(program_type=True)

Get the number of clients receiving each service for every program:

e.enrollment(program_type=True, service_type=True)

Get the number of clients receiving each service on a grant:

e.enrollment(service_type=True, grant_type=True)

Note

Enrollment - Monthly Clients (overall or by program/service/grant)

incident_tally()#

counts incidents in timeframe, distinguishing between CPIC and non-CPIC events.

Example

Get the number of incidents in the timeframe:

e.incident_tally()

Note

Mediations/Critical Incidents - Count by Notification Type

incident_type_tally()#

counts incidents in timeframe, distinguishing between fatal and non-fatal events

Note

Mediations/Critical Incidents - Count by Incident Type

incident_response(cpic_distinguish=True, as_pct=False)#

counts incidents responded to in timeframe

Example

Get the number of incident responses in the timeframe:

e.incident_response()
Parameters:

  • cpic_distinguish (Bool) – separate CPIC and non-CPIC notifications, defaults to True

  • as_pct (Bool) – format responded_in_60 as percent, defaults to false

Note

Mediations/Critical Incidents - Count by Response Type

isp_goal_tracker()#

Breaks out client ISP goals by domain and completion

Example

Get the status of client ISP goals by domain:

e.isp_goal_tracker()

Note

ISPs - Completion Rate by Goal Area

isp_tracker(just_cm=True, summary_table=False, service_days_cutoff=45)#

Returns a table of client service plan statuses or a table summarizing overall plan completion.

Parameters:

  • just_cm (Bool) – if true, only looks at clients enrolled in case management. Defaults to True

  • summary_table (Bool) – whether to return a summary table. Defaults to False

  • service_days_cutoff – the day threshold at which a service plan ought to be complete. Defaults to 45

Examples

Get a full table of ISP statuses for all clients:

e.isp_tracker(just_cm=False)

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

ISPs - Status Tables (Individual or Grouped)

isp_discharged(missing_names=False)#

Returns a table of discharged clients’ service plan completion and groups percent of goals completed.

Parameters:

missing_names (Bool) – whether to return a list of the clients missing an ISP. defaults to False

Examples::

Get the number of discharged clients broken out by % of their service plan completed:

e.isp_discharged()

Get a list of discharged clients with no ISPs recorded:

e.isp_discharged(missing_names=True)

Note

ISPs - Completion Rates for Discharged Clients

legal_bonanza(timeframe=False, case_stage=None, ranking_method=None, grouping_cols=[], wide_col=None)#

Flexible function designed to return a table of legal data.

Parameters:

  • timeframe (Bool) – Whether to look true only looks at cases active in time period

  • case_stage (optional) – ‘started’ only looks at cases started in time period, ‘ended’ looks at cases ended

  • ranking_method (optional) – ‘highest_felony’ looks at a client’s highest pretrial charge, ‘highest_outcome’ looks at a clients highest outcome. Defaults to “None”

  • grouping_cols (str, list) – column(s) to use group_by on. The string ‘case_outcomes’ automatically includes case_outcome, sentence, and probation_type. “Total” gives total number of cases.

  • wide_col (optional) – column to break results out by: ‘fel_reduction’ - if felony reduced, ‘violent’ - if case was violent, ‘class_type’ - if case was felony/misdemeanor

Hints:

group_by column options: case_type, violent, juvenile_adult, class_prior_to_trial_plea, class_after_trial_plea, case_outcome, sentence, probation_type wide_col column options: violent, fel_reduction, class_type

Examples

Get client outcomes in the time period:

e.legal_bonanza(timeframe=True, ranking_method='highest_outcome', grouping_cols='case_outcomes')

Get types of cases begun in time period:

e.legal_bonanza(timeframe=True, case_stage = 'started', grouping_cols='case_type')

Get case outcomes grouped by violent status:

e.legal_bonanza(timeframe=True, case_stage='ended', grouping_cols='case_outcome', wide_col='violent')

Note

Legal - Flexible Master Function

legal_tally(distinct_clients=False)#

Returns a total/started/ended count of cases (or clients with a case) in a timeframe. started is defined by case_start in MyCase, ended is a case_outcome_date in NeonOne

Parameters:

distinct_clients (Bool) – True counts # clients, False counts #cases. Defaults to False

Note

Legal - Case/Client Tally

legal_rearrested(client_level=True)#

Returns a count of clients who picked up new cases cumulatively and in the timeframe

Parameters:

client_level (Bool) – True counts the number of clients, False counts the number of total cases. Defaults to True

Examples

Get the number of new cases picked up by clients:

e.legal_rearrested(client_level=False)

Get the number of clients rearrested:

e.legal_rearrested()

Note

Legal - Recidivism Rates

legal_rjcc(client_level=True, timeframe=True)#

Returns a count of clients enrolled in RJCC (according to MyCase), and a tally of case outcomes

Parameters:

  • client_level (Bool) – True counts the number of clients, False counts the number of total cases. Defaults to True

  • timeframe (Bool) – True only looks at cases in the timeframe. Defaults to True

Examples

Get RJCC cases ended in timeframe:

e.legal_rjcc(client_level=False)

Get the number of clients who completed RJCC:

e.legal_rjcc()

Note

Legal - RJCC Enrollment in MyCase

linkages_edu_completed()#

Returns completed education linkages in timeframe

Note

Linkages - Completed Education Linkages in Timeframe

linkages_edu_employ(just_cm=True, first_n_months=None, ongoing=False, age_threshold=18, new_client_threshold=45, include_wfd=True)#

Counts the number of clients enrolled/employed by age group.

Parameters:

  • just_cm (Bool) – Whether to only include clients enrolled in case management. Defaults to True

  • first_n_months (optional, int) – Only counts linkages in the first N months of program enrollment, usually 6 or 9. Defaults to None

  • ongoing (Bool) – Only include linkages with no end date. Defaults to False

  • age_threshold (int) – inclusive upper bound for ‘school-aged’ clients. Defaults to 18

  • new_client_threshold (int) – number of days required to be considered “continuing”

  • include_wfd (Bool) – whether to count workforce development linkages as employment. Defaults to True.

Examples

Get the number of case management clients enrolled/employed in their first 9 months:

e.linkages_edu_employ(first_n_months=9)

Get the number of clients currently enrolled/employed with an age cutoff of 19:

e.linkages_edu_employ(just_cm=False, ongoing=True, age_threshold=19)

Get the number of case management clients enrolled/employed excluding workforce development linkages:

e.linkages_edu_employ(include_wfd=False)

Get the number of case management clients enrolled/employed after nine months:

e.linkages_edu_employ(new_client_threshold = 275)

Note

Linkages - Big Education and Employment Table

linkages_edu_employ_new(cm_only=False)#

Returns new education/employment linkages in timeframe

Note

Linkages - New Education and Employment Connections

linkages_isp_goals(lclc_initiated=True, timeframe=False, discharged_only=False, idhs_edu_employ=False)#

Returns a table of the number of clients with an active, concluded, or unstarted linkage for an ISP goal domain. Also has a row for # of clients who have a linkage for at least one goal

Parameters:

  • lclc_initiated – only include non-client-initiated linkages. Defaults to True

  • timeframe – only include linkages made in the timeframe. Defaults to False

  • discharged_only – only include discharged clients. Defaults to False

  • idhs_edu_employ – count education linkages for employment goals. Defaults to False

Note

Linkages - Connections by ISP Goal Area

linkages_monthly(lclc_initiated=True, just_cm=False)#

Counts the number of clients linked in the current time frame, and in their first 3/6/9 months

Parameters:

  • lclc_initiated (Bool) – Only look at linkages that LCLC initiated. Defaults to True

  • just_cm (Bool) – Only look at clients receiving case management. Defaults to True

Examples::

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()

Get the number of all clients with linkages in the time period:

e.linkages_monthly(just_cm=True)

Get the number of case management clients with linkages in the time periods, including client-initiated linkages:

e.linkages_monthly(lclc_initiated=False)

Note

Linkages - Number of Clients Linked in Time Period

linkages_high_pcl(min_score=31)#

Get percent of clients with a high PCL score who had a subsequent mental health linkage

Parameters:

min_score – High PCL score threshold. Defaults to 31

Note

Linkages - Percent of High PTSD Screener Clients with a Mental Health Connection

linkages_percent(timeframe=True, new_client_threshold=45, cm_only=True, first_n_months=None)#

Get percent of clients with linkage, broken out by custody/newness

Parameters:

  • timeframe (Bool) – Only include records with a linked_date in the timeframe. Defaults to True

  • new_client_threshold (int) – number of days required to be considered “continuing”. Defaults to 45

  • cm_only (Bool) – Only count case management clients. Defaults to True

  • first_n_months (int) – only count linkages received in a clients first N months. Defaults to None

Note

Linkages - Percent of Clients with a Connection

linkages_tally(lclc_initiated=True, just_cm=False, timeframe=True, distinct_clients=False, group_by='linkage_type', link_started=False, link_ongoing=False)#

Flexible function designed to return linkage information grouped in some way

Parameters:

  • lclc_initiated (Bool) – Only look at linkages that LCLC initiated. Defaults to True

  • just_cm (Bool) – Only look at clients receiving case management. Defaults to True

  • timeframe (Bool) – Only include records with a linked_date in the timeframe. Defaults to True

  • distinct_clients (Bool) – Whether only one record should be counted per client. Defaults to False

  • group_by (str) – The column to group records by (linkage_type, internal_external, linkage_org). Defaults to ‘linkage_type’

  • link_started (Bool) – Only include linkages with a start date. Defaults to False

  • link_ongoing (Bool) – Only include linkages with no end date. Defaults to False

Examples

Get the types of linkages recorded for all clients in the timeframe:

e.linkages_tally()

Get the number of case management clients with an internal/external linkage at any time:

e.linkages_tally(just_cm=True, timeframe=False, distinct_clients=True, group_by='internal_external')

Get the number of clients with a started linkage of each type in the timeframe:

e.linkages_tally(distinct_clients=True, link_started=True)

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

Linkages - Flexible Master Function

linkages_total(lclc_initiated=True, just_cm=False, timeframe=True, distinct_clients=False, link_started=False, link_ongoing=False)#

Returns the total count of participants linked/linkages made

Parameters:

  • lclc_initiated (Bool) – Only look at linkages that LCLC initiated. Defaults to True

  • just_cm (Bool) – Only look at clients receiving case management. Defaults to True

  • timeframe (Bool) – Only include records with a linked_date in the timeframe. Defaults to True

  • distinct_clients (Bool) – Whether only one record should be counted per client. Defaults to False

  • link_started (Bool) – Only include linkages with a start date. Defaults to False

  • link_ongoing (Bool) – Only include linkages with no end date. Defaults to False

Note

Linkages - Total Connections Made

outreach_canvassing()#

Counts the total canvassing sessions/hours in the timeframe, with rows for distinct sessions and per-staff contributions

Note

Outreach - Canvassing Totals

outreach_elig_tally(outreach_only=True, new_clients=False)#

Counts the number of clients with outreach eligibility forms, and the number who answered yes to each question

Parameters:

  • outreach_only (Bool) – Only include clients currently in outreach. Defaults to True

  • new_clients (Bool) – Only include new clients. Defaults to False

Examples

Get the number of new outreach clients with eligibility screenings/number of “yes”es for each question:

e.outreach_elig_tally(new_clients=True)

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

mediation_tally(timeframe=True)#

Counts mediations

Parameters:

timeframe (Bool) – Whether to only include mediations in the timeframe. Defaults to True

Note

Mediations/Critical Incidents - Count

session_tally(session_type='Case Management', distinct_participants=True)#

Tallies the number of sessions, or number of clients in the timeframe

Parameters:

  • session_type – the type of session to count. Defaults to ‘Case Management’, but could also be ‘Outreach’

  • distinct_participants (Bool) – True counts the number of clients, False counts the number of sessions. Defaults to True

Examples

Get the number of clients with a case management session in the timeframe:

e.session_tally()

Get the number of outreach sessions in the timeframe:

e.session_tally(session_type='Outreach', distinct_participants=False)

Note

Case Sessions - CM or Outreach Session Tally

session_avg_time_per_client(session_type='Case Management')#

Finds the average time spent in sessions per client in a timeframe.

Parameters:

  • session_type –

    the type of session to count. Defaults to ‘Case Management’, but could also be ‘Outreach’

    Note:

  • Sessions (Case Sessions - Average Time Spent in)

session_frequency(session_type='Case Management')#

Calculates the regularity of client sessions.

Parameters:

session_type – the type of session to count. Defaults to ‘Case Management’, but could also be ‘Outreach’

Examples

Get the session regularity of case management clients:

e.session_frequency()

Get the session regularity of outreach clients:

e.session_frequency(session_type="Outreach")

Note

Case Sessions - CM or Outreach Session Frequency

session_time_per_topic(session_type='Case Management')#

For each focus of contact, returns the number of clients who discussed the topic, and the sum of all time spent in associated sessions within the timeframe.

Parameters:

session_type – the type of session to count. Defaults to ‘Case Management’, but could also be ‘Outreach’

Note

Case Sessions - Client/Time Totals per Focus of Contact

Specific-Use Functions#

class sql_funcs.Audits(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

Bases: Tables

__init__(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

establishes settings and runs stints for the desired time period

Parameters:

  • t1 – start date, formatted as “YYYY-MM-DD”

  • t2 – end date, formatted as “YYYY-MM-DD”

  • print_sql (Bool) – whether to print the SQL statements when run, defaults to True

  • clipboard (Bool) – whether to copy the output table to your clipboard, defaults to False

  • default_table – the source table to run queries on. defaults to “stints.neon”, can also use “stints.neon_chd”, or a participants table

  • mycase (Bool) – whether the user has a formatted MyCase SQL database, defaults to True

Examples

Set up a table of all clients in Neon in 2024

e = Queries(t1 = '2024-01-01', t2='2024-12-31')

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)
program_lacks_services()#

Returns a table of active programs with no corresponding services.

service_lacks_grant()#

Returns a table of active services without a corresponding grant.

service_lacks_staff(active_only=True)#

Returns a table of active services without a corresponding staff member.

Parameters:

active_only (Bool) – only looks at clients with active programs/services

staff_lacks_service()#

Returns a table of staff members assigned to clients without a corresponding service type

legal_audit_lawyers(active_only=True, new_cases_only=False)#

Returns several joined tables of missing legal data

Parameters:

  • active_only – Only look at active cases. Defaults to True

  • new_cases_only – Only look at new cases in the timeframe. Defaults to False

Note

Audit - Legal Data

active_session_tally(service_type='Case Management', successful_only=True)#

Counts the number of active sessions for each participant_id

Parameters:

  • service_type – what service to count sessions of. Defaults to ‘Case Management’

  • successful_only – Whether to only include successful contacts. Defaults to True

Note

Case Sessions - Number of Case Sessions per Client

cm_isp_status()#

Table of ISP status for active clients

Note

ISPs - Statuses for Active Clients

cm_linkage_totals()#

Table of linkage totals for active clients

Note

Linkages - Totals for Active Clients

outreach_missing_assessments()#

Returns a table of the count and names of missing outreach assessments for clients.

Note

Outreach - Missing Assessment Information

class sql_funcs.ReferralAsks(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True)#

Bases: Queries

__init__(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True)#

establishes settings and runs stints for the desired time period

Parameters:

  • t1 – start date, formatted as “YYYY-MM-DD”

  • t2 – end date, formatted as “YYYY-MM-DD”

  • print_sql (Bool) – whether to print the SQL statements when run, defaults to True

  • clipboard (Bool) – whether to copy the output table to your clipboard, defaults to False

  • default_table – the source table to run queries on. defaults to “stints.neon”, can also use “stints.neon_chd”, or a participants table

  • mycase (Bool) – whether the user has a formatted MyCase SQL database, defaults to True

Examples

Set up a table of all clients in Neon in 2024

e = Queries(t1 = '2024-01-01', t2='2024-12-31')

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)
highest_cases(attorneys=False)#

Finds the number of cases for each felony class by team

Parameters:

attorneys (Bool) – whether to group by attorney or team. Defaults to False

Examples

Get the number cases for each team broken out by highest charge per client:

e.highest_cases()

Get the number cases for each attorney broken out by highest charge per client:

e.highest_cases()
cm_closures()#

Returns the number of closed clients in the timeframe for each case manager

Example

Get the number of case management closures in the timeframe:

e.cm_closures()
cpic_summary()#

Returns a summary of CPIC notifications in the timeframe

Example

Get a CPIC notification summary:

e.cm_closures()
missing_isp()#

Returns a table of clients missing an ISP

Example

Get a table of ISP-less clients:

e.missing_isp()
last_30_days(successful_sessions=True)#

Returns a table of clients without a session in the last 30 days

Parameters:

successful_sessions (Bool) – Only include sessions where contact was successfully made. Defaults to True

Examples

Get a record of clients without a CM session in the last 30 days:

e.last_30_days()

Get a record of clients without a CM session attempt in the last 30 days:

e.last_30_days(False)
class sql_funcs.Grants(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False, grant_type='idhs')#

Bases: Queries

__init__(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False, grant_type='idhs')#

Establishes a Queries object with a grant-specific default table.

Parameters:

  • t1 – start date, formatted as “YYYY-MM-DD”

  • t2 – end date, formatted as “YYYY-MM-DD”

  • print_sql (Bool) – whether to print the SQL statements when run, defaults to True

  • clipboard (Bool) – whether to copy the output table to your clipboard, defaults to False

  • default_table – the source table to run queries on. defaults to “stints.neon”, can also use “stints.neon_chd”, or a participants table

  • mycase (Bool) – whether the user has a formatted MyCase SQL database, defaults to True

  • grant_types – grant for the default table. Options [‘idhs’, ‘idhs_r’, ‘r3’, ‘scan’, ‘ryds’, ‘jac’, ‘cvi’]

Example

Set default table to IDHS VP clients for CY25Q3

e = Grants(t1=’2025-07-01’, t2=’2025-09-30’, engine=neon_engine, clipboard=True, default_table=’stints.neon’,grant_type=’idhs’)

cvi_demographics()#

Returns Demographics for the ICJIA - CVI report

Note

Grants: ICJIA - CVI Demographics

cvi_mental_health_linkages()#

ICJIA - CVI Mental Health Linkages

Note

Grants: ICJIA - CVI Mental Health Linkages

cvi_post_incident()#

Returns number of people received services after homicide shooting

Example

Get # of people who received services after homicide shooting:

e.cvi_post_incident()

Note

Grants: ICJIA - CVI Recieved Services after Shooting

idhs_enrollment()#

Returns a table of enrollment numbers for IDHS

Example

Get clients enrolled in IDHS and its services:

e.idhs_enrollment()

Note

Grants: IDHS - VP Enrollment

idhs_race_gender(race_gender='race')#

Returns a table of client races or genders broken out by new client status

Parameters:

race_gender (str) – whether to tally ‘race’ or ‘gender’. Defaults to ‘race’

Examples

See IDHS client races:

e.idhs_race_gender('race')

See IDHS client genders:

e.idhs_race_gender('gender')

Note

Grants: IDHS - VP Client Races or Genders

idhs_language()#

Counts participant primary languages

Example

Get IDHS client primary languages:

e.idhs_language()

Note

Grants: IDHS - VP Client Languages

idhs_age(cvi=False)#

Returns a table of client ages broken out by new client status

Parameters:

cvi (Bool) – whether to use the age groups on the CVI form. Defaults to False

Examples

Get IDHS client ages for the CVI:

e.idhs_age(True)

Get IDHS client ages for the PPR:

e.idhs_age(False)

Note

Grants: IDHS - VP Client Ages

idhs_linkages(internal_external=False, cm_only=True)#

Returns a table of linkage information for the quarter

Parameters:

  • internal_external – whether to group by internal/external functions. Defaults to False

  • cm_only – whether to only count clients with case management funded by grant. Defaults to True

Examples

Get IDHS client linkages by linkage category:

e.idhs_linkages()

Get a breakdown of internal/external linkages for IDHS clients:

e.idhs_linkages(True)

Note

Grants: IDHS - VP Linkage Table

idhs_linkages_detailed()#

Returns a Frankensteined table of other forms of ‘detail-level services’. Currently includes in-kind services, outreach/cm assessments, and topics of cm sessions.

Example

Get a table of non-linkages services IDHS clients were connected to:

e.idhs_linkages_detailed()

Note

Grants: IDHS - VP Extended Linkage Table

idhs_incidents(CPIC=True)#

Returns incident analysis for CPIC/non-CPIC notifications

Example

Get a CPIC notification breakdown for IDHS:

e.idhs_incidents()

Get a non-CPIC notification breakdown for IDHS:

e.idhs_incidents(False)

Note

Grants: IDHS - VP Incident Tally

idhs_r_schooling_gender()#

Returns client gender counts broken out by schooling status

Example

Get client genders/school statuses for IDHS - R:

e.idhs_r_schooling_gender()

Note

Grants: IDHS - R Client Genders/School Statuses

idhs_r_age_gender()#

Returns client gender counts broken out by age range

Example

Get client genders/ages for IDHS - R:

e.idhs_r_age_gender()

Note

Grants: IDHS - R Client Ages/Genders

jac_linkages()#

Counts clients provided/referred to services within timeframe, listing all referral partners

Note

Grants: JAC - HD Service Connections Provided/Referred

r3_ages()#

Returns client ages for groups 6-11, 12-14, 15-17, 18-25, 26+

Note

Grants: ICJIA - R3 Client Ages

ryds_ages()#

Returns client ages for groups 0-11, 11-13, 14-17, 18-21, 22+

Note

Grants: IDHS - RYDS Client Ages

ryds_cirriculum(summary_table=True)#

Returns a table of cirriculum completion information for clients, by default grouped in a summary_table.

Parameters:

summary_table – True returns a table with bins matching the PPR report. False returns one row per client, with information on which units are missing.

Note

Grants: IDHS - RYDS Cirriculum Completion

victim_services_sessions(timeframe=True, services_per_client=False)#

Overview on the topics of victim services sessions

Parameters:

  • timeframe (Bool) – only include sessions in timeframe. Defaults to True

  • services_per_client (Bool) – count the total number of topics for a given client (instead of the number of clients receiving each service). Defaults to False

Note

Case Sessions - Victim Services

Setup#

class sql_funcs.Tables(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

Bases: object

__init__(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

establishes settings and runs stints for the desired time period

Parameters:

  • t1 – start date, formatted as “YYYY-MM-DD”

  • t2 – end date, formatted as “YYYY-MM-DD”

  • print_sql (Bool) – whether to print the SQL statements when run, defaults to True

  • clipboard (Bool) – whether to copy the output table to your clipboard, defaults to False

  • default_table – the source table to run queries on. defaults to “stints.neon”, can also use “stints.neon_chd”, or a participants table

  • mycase (Bool) – whether the user has a formatted MyCase SQL database, defaults to True

Examples

Set up a table of all clients in Neon in 2024

e = Queries(t1 = '2024-01-01', t2='2024-12-31')

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)
query_run(query)#

runs an SQL query (without a semicolon) format for a custom query: query_run(f”””query”””)

Parameters:

query – SQL query

query_modify(original_query, modification)#

edit a base SQL query, not particularly useful on its own

Parameters:

  • original_query – base SQL query

  • modification – string to modify it

stints_cloud_run()#

abbreviated stints run for gcloud run

stints_run()#

runs the stints code

run_report(func_dict, *args, **kwargs)#

runs a desired report :param func_dict: dictionary of functions to include, defaults to self.report_funcs. To use a different

run_projections(func_dict, *args, **kwargs)#

runs a report of grant projections function dictionary follows format{grant_name:{func_dict}}

table_update(desired_table, update_default_table=False)#
Contents