sql_funcs documentation

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

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

ASSM Score Changes

assess_missing_outreach()#

Returns a list of outreach clients missing assessments

Example

Get clients missing outreach assessments:

e.assess_missing_outreach()

Note

Outreach Clients Missing Assessments

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

Case Management Assessment 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

Assessment 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

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

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

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

Client Races or Genders

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

Total Clients Enrolled (overall or by program/service/grant)

enrollment_bundles()#

Counts clients by their bundle of programs

Example

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

e.enrollment_bundles()

Note

Client Program Combinations

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

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

Critical Incident Count

incident_response()#

counts incidents responded to in timeframe

Example

Get the number of incident responses in the timeframe:

e.incident_response()

Note

Critical Incident Response Count

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

ISP Completion by Goal Area

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

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

Parameters:

  • cm_only (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(cm_only=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

ISP Status (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

ISP Completion 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) – Options: ‘highest_felony’ - a client’s highest pretrial charge, ‘highest_outcome’ - a client’s most severe case outcome. Defaults to “None”

  • grouping_cols (str, list) – column(s) to use group_by on. Some options: case_outcomes (includes case_outcome, sentence, and probation_type), case_status, case_type, juvenile_adult, class_prior_to_trial_plea

  • wide_col (optional) – column to delineate results by. Options: violent - cases marked as violent, class_type if case was felony/misdemeanor, fel_reduction - if felony was reduced,

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 Information

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

Recidivism

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

RJCC Enrollment in MyCase

linkages_edu_completed()#

Returns completed education linkages in timeframe

Note

Completed Education Linkages

linkages_edu_employ(cm_only=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:

  • cm_only (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(cm_only=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

Education and Employment Linkage Table

linkages_edu_employ_new(cm_only=False)#

Returns new education/employment linkages in timeframe

Note

New Education and Employment Linkages

linkages_monthly(lclc_initiated=True, cm_only=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

  • cm_only (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(cm_only=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

Number of Clients Linked by Time Period

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

Percent of Clients with a Linkage

linkages_tally(lclc_initiated=True, cm_only=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

  • cm_only (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(cm_only=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

Linkage Information (Flexible)

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

Mediation 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

CM or Outreach Session Tally

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

CM or Outreach Session Frequency

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(func_dict=None)#
class sql_funcs.ReferralAsks(t1, t2, engine, print_SQL=True, clipboard=False, default_table='stints.neon', mycase=True, cloud_run=False)#

Bases: Queries

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

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

table_update(desired_table, update_default_table=False)#