Module Output

SQL:[IDI_Community].[chld_assess_investig_abuse].[assess_investig_abuse_YYYYMM]
SAS: libname cm_ot OBDC dsn=IDI_community_srvprd schema=chld_assess_investig_abuse; run ; proc print data = cm_ot.assess_investig_abuse_YYYYMM; run ;
How to access a code module in the Data Lab:Read here

Purpose

This module creates a table of Child Family Assessments (CFA)/Investigations and Abuse Findings by Care and Protection system.
CFA/Investigation and Abuse Findings are critial components of child protection system and helps support and consistencies about understanding of Children at risk.

Key Concepts

A CFA is an outcome and response option appropriate when a report of concern involves an allegation that indicates the care, safety or wellbeing of the child or young person may be at risk, but does not indicate that the response should involve Police or require a medical or evidential response. For more information on Reports of Concern (and code to identify these) see Oranga Tamariki - Reports of Concern (ROC) .

An investigation response is appropriate when the notification involves an allegation of serious child abuse that may require Police involvement.

An Investigation/CFA event follows a Client Intake Event (also called a notification or Report of Concern (ROC)) when the outcome of the Intake is “Further Action Required” (FAR).
It is a phase of work where a client is ‘rolled’ into the phase from the Intake and certain actions take place (stored as records within the phase). A key worker is assigned to an Investigation/CFA phase and an assessment is carried out in which the investigation/CFA findings are recorded.
The investigation may result in a “finding” of a form of abuse or ‘Not Found’ in which case the recorded outcome will be ‘No Further Action’.

An Abuse Finding Event records the assessment that a social worker makes about whether or not a client has suffered abuse.
This is a Point in Time event and Event_From_DateTime and Event_To_DateTime will be the same as“ the “Assessment Date” of the related assessment.
Findings include:

• Sexual_Abused
• Physical_Abused
• Emotional_Abused
• Neglect
• Selfharm_Suicidal
• Behavioural_Relationship_Issues
• No_abuse_found

Notes:

• All CFA/investigation events are Care and Protection business area. Youth Justice notification proceed directly to an intervention phase where notification findings are FAR.
• There will often be multiple Abuse Findings Event records for a child because there may be multiple notifications for a client, each requiring an investigation.
  The same child may have more than one type of abuse within the same period (physically and sexually abused). Similarly, a child may have same type of abuse more than once for the same notification, as a result of more than one perpetrator subjecting the child to the same abuse. For example, a child is neglected by both parents.
• If there is more than one child involved in an investigation/CFA, there may be more than one assessment carried out with different combinations of participants in each assessment.
• At one point, whenever an abuse finding of physical abuse was recorded, social workers also entered an emotional abuse finding. They were told not to do this from around 2013.
  Prior to 2013, it’s better to only count if any abuse finding was made. From 2014, you can do analysis on the individual types.
• If an individual has records in the investigation event table but not in abuse finding table and their cyf_ive_event_to_date_wid_date is ‘9999-12-31’, it means that their investigation has not been finished yet. So, their investigation result is unknown.
• If an individual has records in the investigation event table but not in abuse finding table and their cyf_ive_event_to_date_wid_date is not ‘9999-12-31’, it means that their investigation is finished but the result is unknown.
• So, for the individuals who have records in the investigation event tables but not in abuse finding tables, we can label their investigation result as ‘unknown’. Note that there is only 0.01% of the rows in the investigation table their investigation result is ‘unknown’.

Comparison against other sources

Since there is no public available resource to compare, an comparison between the result of this code module and that of an Oranga Tamariki internal abuse finding dashboard has been done.
The effectiveness of this code module has been verified by the comparison result.

Development Team

Module Business Rules

To select between the care and protection system we use this variable:
cyf_ive_event_type_wid_nbr: ‘3’ = Investigation, ‘4’ = Child Family Assessment

Open Issues/Comments

Researchers should also note that, data extracted from our operational case management system records are subject to change as more information becomes available or lagged data entry occurs.
As a result figures produced from this operational data may sometimes differ from official published results or reports to some degree as those were based on the time the data was extracted and collated.

Parameters

The following parameters should be supplied to this module to run it in the database:

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
3. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
4. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.
5. {idimetaversion}: The IDI metadata version that the spell datasets need to be based on.

Dependencies

1. {idicleanversion}.[cyf_clean].[cyf_investgtns_event]; 
2. {idicleanversion}.[cyf_clean].[cyf_investgtns_details]; 
3. {idicleanversion}.[cyf_clean].[cyf_abuse_event]; 

Outputs

[IDI_Community].[cm_read_OT_Assess_Investig_Abuse_STATUS].[ot_assess_investig_abuse_YYYYMM]

Variable Descriptions

Module Version & Change History

Code

:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
:setvar idimetaversion "{idimetaversion}"

/* Combine the two tables [cyf_intakes_event] and [cyf_intakes_details]*/

/*
Child Family Assessment/Investigation:
Select individuals with CFA/Investigation from the cyf_investgtns_event table
*/

DROP TABLE IF EXISTS #cfainv;

SELECT DISTINCT a.snz_uid
    , a.cyf_ive_event_from_date_wid_date AS cfainv_from_date
    ,  /* CFA/Investigation event from date */
	a.cyf_ive_event_to_date_wid_date AS cfainv_to_date
    ,  /* CFA/Investigation event to date */
	b.cyf_ivd_assessment_id_nbr AS cfainv_assessment_id
    ,  /* CFA/Investigation event id */
	CASE WHEN a.cyf_ive_event_type_wid_nbr = '3' THEN 'Investigation'
		 WHEN a.cyf_ive_event_type_wid_nbr = '4' THEN 'Child Family Assessment'
	ELSE 'ELSE' END AS cfainv_event_type               /* CFA/Investigation event type */
INTO #cfainv
FROM [$(idicleanversion)].[cyf_clean].[cyf_investgtns_event] a
INNER JOIN [$(idicleanversion)].[cyf_clean].[cyf_investgtns_details] b
ON a.snz_composite_event_uid = b.snz_composite_event_uid
AND a.snz_cyf_social_work_phase_uid = b.snz_cyf_social_work_phase_uid
WHERE a.cyf_ive_event_type_wid_nbr IN ('3','4');

/*
Abuse findings
Select individuals with abuse findings from the cyf_abuse_event table
*/

DROP TABLE IF EXISTS #abuse_finding;

SELECT DISTINCT snz_uid
    , [cyf_abe_source_uk_var1_text] AS abe_assessment_id
    ,  /* CFA/Investigation event id */
	[snz_uk_var3_uid] AS perpetrator_id
    ,                 /* perpetrator id */
	[cyf_abe_event_from_date_wid_date] AS finding_date
    ,  /* abuse finding event date */
	[cyf_abe_source_uk_var2_text] AS abuse_finding_code
    , CASE 
		WHEN [cyf_abe_source_uk_var2_text] = 'BRD' THEN 'Behavioural_Relationship_Issues'
		WHEN [cyf_abe_source_uk_var2_text] = 'EMO' THEN 'Emotional_Abused'
		WHEN [cyf_abe_source_uk_var2_text] = 'NEG' THEN 'Neglect'
		WHEN [cyf_abe_source_uk_var2_text] = 'PHY' THEN 'Physical_Abused'
		WHEN [cyf_abe_source_uk_var2_text] = 'SEX' THEN 'Sexual_Abused'
		WHEN [cyf_abe_source_uk_var2_text] IN ('SHM','SHS', 'SUC') THEN 'Selfharm_Suicidal'
		ELSE 'No_abuse_found'
	END AS abuse_finding								  /* abuse finding */
INTO #abuse_finding
FROM [$(idicleanversion)].[cyf_clean].[cyf_abuse_event]
WHERE [cyf_abe_event_type_wid_nbr] = '12'; /* Note that for all the rows in the [cyf_abuse_event] table their [cyf_abe_event_type_wid_nbr]s are all '12'. */

/*
Remove duplicate data for the #abuse_finding table,
for a snz_uid and an abe_assessment_id, if there are multiple abe_to_date having the same perpetrator_id and abuse_finding,
we just use the earliest one.
*/

DROP TABLE IF EXISTS #abuse_finding_2;

SELECT snz_uid
    , abe_assessment_id
    , perpetrator_id
    , abuse_finding
    , MIN(finding_date) AS finding_date
INTO #abuse_finding_2
FROM #abuse_finding
GROUP BY snz_uid
    , abe_assessment_id
    , perpetrator_id
    , abuse_finding;

/*
Join the CFA/Investigation events and abuse findings
Note that sometimes the abe_date may be later than the cfainv_to_date for the same assessment_id and snz_uid
*/

DROP TABLE IF EXISTS #cfainv_abuse_finding;

SELECT a.snz_uid
    , a.cfainv_event_type
    , a.cfainv_from_date
    , a.cfainv_to_date
    , a.cfainv_assessment_id
    , b.finding_date AS abuse_finding_date
    , b.perpetrator_id
    , b.abuse_finding
INTO #cfainv_abuse_finding
FROM #cfainv a
INNER JOIN #abuse_finding_2 b
ON a.snz_uid = b.snz_uid
AND a.cfainv_assessment_id = b.abe_assessment_id;

/* For individuals who are in the assessment_investigation table but not in the abuse_finding table, their abuse finding results are unknown. 
   Given that the number of these individuals is small, we didn't include them in the #cfainv_abuse_finding table. This is why we used 'inner join' in the code.
*/

SELECT *
FROM #cfainv_abuse_finding