Outputs:

SQL: [IDI_Community].[chld_custody_order_episode].[custody_order_episode_202603]
SAS: libname cm_custody_order_episode dsn=IDI_community_srvprd schema=chld_custody_order_episode; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Purpose

This module creates a table of all custody order episodes when tamariki and rangatahi come into the care of Chief Executive of Oranga Tamariki under the Oranga Tamariki Act 1989.

Key Concepts

1. What is a custody order?

A custody order is a court issued direction that places a child or young person under the legal custody of Oranga Tamariki (the Ministry for Children).
It is created under the Oranga Tamariki Act-1989 and in the case of Youth Justice sometimes also under the Criminal Procedure Act or Corrections Act.

Key points:

• Custody vs. guardianship - Custody concerns the day to day care of the child (who looks after them, where they live, who makes routine decisions). Guardianship, which may be ordered alongside custody, deals with higher level legal decisions such as medical consent.
• Who can apply - The Ministry’s officers, acting on behalf of the child, can apply to the Family Court. Parents, guardians or other concerned parties may also apply, but the Ministry must be involved unless the court orders otherwise.
• When it is made - Usually after a care or protection application shows the child is at risk of significant harm, or in an emergency where the child must be removed immediately. It can also follow a voluntary agreement where a parent signs a caretaking arrangement that the court later formalises.
• Form of the order - The order specifies the date it takes effect, its duration (often until further order), any conditions (e.g., supervised contact, travel restrictions), and whether custody is sole (Oranga Tamariki alone) or joint (shared with a parent or other person).

2. Why is a custody order important?

• Protects the child’s safety and wellbeing - It gives the Ministry a lawful basis to remove a child from an unsafe environment and to place them in care promptly.
• Creates legal clarity - Schools, health providers, police and other agencies can confidently interact with the correct responsible party, avoiding confusion about who has authority over the child.
• Enables out of home placement - Without a custody order the Ministry cannot legally place a child with foster, kinship or residential carers.
• Ensures procedural fairness - The order is issued after a judicial hearing where evidence is considered and the child’s parents or guardians have a right to be heard and to appeal.
• Supports long term planning - Once custody is established, Oranga Tamariki can develop a formal Child’s Plan (or Individual Service Plan) that outlines steps toward reunification, permanency or other outcomes.
• Meets statutory requirements - The Act obliges the Ministry to obtain a court order before exercising custodial powers (except for short term emergencies), protecting the agency from acting beyond its legal authority.
• Facilitates coordination among services - The order serves as proof of the Ministry’s legal standing, allowing smoother collaboration with health, education, police and community organisations.

3. What can a custody order be used for?

• Child protection and care
  • Removal from the family home - Gives the Ministry the authority to take the child into state care when the home is deemed unsafe.
  • Placement with foster or kinship carers - Provides the legal basis for out of home care arrangements.
  • Supervision and monitoring - Conditions can be attached, such as requiring supervised visits with parents or regular check ins with the Ministry.
• Youth Justice processes
  • Held in detention following arrest awaiting court appearance - detained following arrest for alleged offending until appearance before the court
  • Remanded in custody while a court case continues - detained in custody under orders from the Court while their case continues. Usually used if deemed a risk of absconding, may committ further offences, may destroy evidence or interfere with witnesses, or have breached bail conditions.
  • Sentenced to remain in custody due to proven offending - upon offending being proven in court they are sentenced to serve a custodial order as a result
• Decision making authority
  • Medical consent - Allows Oranga Tamariki (or a designated caregiver) to consent to routine or emergency medical treatment when parents are unavailable or unsuitable.
  • Education - Enables the Ministry to enrol the child in a school, request special education support, or place the child in an alternative education program.
  • Travel and relocation - The order can restrict or permit domestic or international travel, permitting the child to be moved to another region for placement when necessary.
• Administrative and legal matters
  • Applying for benefits - Custody status may be required when the child or the custodial caregiver applies for Child Welfare Benefits, Disability Allowance, or other government assistance.
  • Legal representation - The Ministry can act as the child’s legal representative in other proceedings, such as guardianship, adoption, or criminal matters.
  • Data sharing - Agencies can share the child’s personal information under the Privacy Act because the custody order establishes a lawful reason for disclosure.
• Specific scenarios
  • Emergency removal - Police may take a child into protective custody for up to 24 hours; a court issued custody order is then sought to extend that period.
  • Voluntary placement with a relative - Parents sign a voluntary care agreement, which the court later formalises as a custody order, giving the relative legal standing.
  • International adoption or cross border care - A custody order (often together with a guardianship order) satisfies the legal requirements of the receiving country that the child be under state custody before the adoption can proceed.

4. How to identify children in Oranga Tamariki care by using custody orders?

• Care and protection
  • Custody order code - A list of custody order codes can be seen in the below SQL code.
  • 28-days rule - If a child’s custody order episode is stopped and a new one starts within 28 calendar days, the two are treated as one episode.
    Only when the gap exceeds 28 days do we create a distinct new episode.
• Youth Justice
  • Custody order code - A list of custody order codes can be seen in the below SQL code.
  • 1-day rule - If a child’s custody order episode is stopped and a new one starts within 1 calendar day, the two are treated as one episode.
    Only when the gap exceeds 1 day do we create a distinct new episode.
• Entries/Exits to custody
  • Entries/Exits statistics to either Care and Protection of Youth Justice custody are always determined by entry/exit of the custody episodes defined above and not the individual orders contained within the episodes.

Notes:

Comparison against other sources

A comparison between the result of this code module and that of an Oranga Tamariki public report on the number of children in the Care and Protection Custody of the Chief Executive has been done.
The effectiveness of this code module has been verified by the comparison result.

Data source 1 for the comparison:
Tabs: Custody of the CE

Data source 2:
Oranga Tamariki internally published SSORT dashboard
Tab: Entry to Care(CP)
Tab: Entry to Care(YJ)

References & Contacts

Code module technical information

See details below.

Community of Interest

Module Business Rules

Module business rules have been introduced in section 4. How to identify children in Oranga Tamariki care by using custody orders.

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.

Variable Descriptions

Required columns: snz_uid

Module Version & Change History

Code

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

/*Extracting custody order episodes */

USE [$(idicleanversion)];
GO

/*
1.1 Care and Protection Custody Order Episode
*/

DROP TABLE IF EXISTS #cnp_custody;
SELECT DISTINCT a.[snz_uid]
    , a.[snz_composite_event_uid]
    , b.[cyf_lsd_legal_status_code]
    , a.[cyf_lse_event_from_datetime] AS [from_date]
    , a.[cyf_lse_event_to_datetime] AS [to_date]
INTO #cnp_custody
FROM [$(idicleanversion)].cyf_clean.cyf_ev_cli_legal_status_cys_f AS a
INNER JOIN [$(idicleanversion)].cyf_clean.cyf_dt_cli_legal_status_cys_d AS b
ON a.[snz_composite_event_uid] = b.[snz_composite_event_uid]
WHERE 
/*remove orders with an end date less than their start data to clean data errors*/
a.[cyf_lse_event_from_datetime] < a.[cyf_lse_event_to_datetime]
/*select custodial orders*/
AND b.[cyf_lsd_in_custody_ind] = 'Y'
/*remove withdrawn legal orders*/
AND b.[cyf_lsd_court_result_type_code] <> 'WIT'
/*select orders in the favour of the CE of Oranga Tamariki (or MSD for historical records)*/
AND b.[cyf_lsd_in_favour_of_type_code] IN ('CEO', 'CEMSD', 'CEOT', 'CEOTMC')
/*select the care and protection specific custody orders*/
AND b.[cyf_lsd_legal_status_code] IN (
/* care agreement - voluntary temporary agreements between parents/guardians for short term custody*/
'S139', 'S140',
/* S78 - Interim custody orders pending final determination of proceedings in family court. Used in urgent situations or when immediate concerns for child's safety are present.*/
'S78', 'S781A', 'S781AW', 'S781AWO', 'S78W', 'S78WO',
/* planned entry - generally more substantive longer term care custody orders*/
'S141', 'S1412A', 'S1412B', 'S101', 'S102', 'S102A', 'S110AA', 'S110AASOLE',
/*warrant - emergency action orders, highly short term usually 5 days at most. S42 and S48 executed by the Police.*/
'S48', 'S42', 'S39', 'S40', 'S45'
);

/*Filtering out any duplicate entries*/
DROP TABLE IF EXISTS #cnp_custody_dedup;
SELECT *
INTO #cnp_custody_dedup
FROM (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY [snz_uid], [from_date], [to_date] 
									ORDER BY [snz_uid], [from_date], [to_date] ) AS [rn]
    FROM #cnp_custody
) t
WHERE rn = 1;

/*Keeping latest legal status that start on the same day*/
DROP TABLE IF EXISTS #cnp_ranked_custody;
WITH cnp_ranked_custody AS(
    SELECT *
        , ROW_NUMBER() OVER ( PARTITION BY [snz_uid], [from_date] ORDER BY [to_date] DESC ) AS [rn_1]
    FROM #cnp_custody_dedup
)
SELECT [snz_uid]
    , [snz_composite_event_uid]
    , [cyf_lsd_legal_status_code] AS [legal_status]
    , CONVERT(DATE, [from_date]) AS [from_date]
    , CONVERT(DATE, [to_date]) AS [to_date]
INTO #cnp_ranked_custody
FROM cnp_ranked_custody
WHERE [rn_1] = 1;

/*Processing data for overlapping legal statuses to create legal episodes*/
/*Note: when the break between an end date and start date is less than 28 days, a child or young people is considered to be in the custody
of the CE continuously. A child or young people can hold multiple legal statuses that indicate their being in the custody of the CE,
but the legal status at the start of the legal episode is taken as the order they entered custody on*/

DROP TABLE IF EXISTS #cnp_group_numbers;
WITH cnp_ordered_events AS(
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [rn]
    FROM #cnp_ranked_custody
)
    , cnp_event_with_lag AS(
    SELECT e.*

        , LAG([from_date]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [prev_from_date]
        , LAG([to_date]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [prev_to_date]
    FROM cnp_ordered_events e
)
    , cnp_event_groups AS(
    SELECT *
        , DATEDIFF(DAY, [prev_to_date], [from_date]) AS [DATEDIFF]
        , IIF(DATEDIFF(DAY, [prev_to_date], [from_date]) < 28, 0, 1) AS [new_group]
    FROM cnp_event_with_lag
)
SELECT a.*
    , SUM([new_group]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date] ROWS UNBOUNDED PRECEDING) AS [group_id]
INTO #cnp_group_numbers
FROM cnp_event_groups a;

/* Remove duplicate events, make new table for combined status also only pick the min start date and max end date */
DROP TABLE IF EXISTS #cnp_custody_events;
WITH cnp_group_min AS(
    SELECT [snz_uid]
        , [group_id]
        , MIN(rn) AS min_rn
        , MIN([from_date]) AS [min_from_date]
        , MAX([to_date]) AS [max_to_date]
    FROM #cnp_group_numbers
    GROUP BY [snz_uid]
        , [group_id]
)
    , cnp_distinct_legal_status AS(
    SELECT [snz_uid]
        , [group_id]
        , STRING_AGG(
        [legal_status]
            , ';'
    ) WITHIN GROUP(
        ORDER BY from_date
        ASC
    ) AS [all_status]
    FROM (
        SELECT DISTINCT [snz_uid]
            , [group_id]
            , [legal_status]
            , from_date
        FROM #cnp_group_numbers
    ) a
    GROUP BY [snz_uid]
        , [group_id]
)
SELECT a.[snz_uid]
    , a.[snz_composite_event_uid]
    , business_area = 'CNP'
    , a.[legal_status] AS first_legal_status_in_the_episode
    , CASE 
		  WHEN a.[legal_status] IN (
		/* care agreement - voluntary temporary agreements between parents/guardians for short term custody*/
	'S139', 'S140') THEN 'Care_Agreement'
		  WHEN a.[legal_status] IN (
	/* S78 - Interim custody orders pending final determination of proceedings in family court. Used in urgent situations or when immediate concerns for child's safety are present.*/
	'S78', 'S781A', 'S781AW', 'S781AWO', 'S78W', 'S78WO') THEN 'Section_78'
		  WHEN a.[legal_status] IN (
		  /* planned entry - generally more substantive longer term care custody orders*/
	'S141', 'S1412A', 'S1412B', 'S101', 'S102', 'S102A', 'S110AA', 'S110AASOLE') THEN 'Planned_Entry'
		  WHEN a.[legal_status] IN (
	/*warrant - emergency action orders, highly short term usually 5 days at most. S42 and S48 executed by the Police.*/
	'S48', 'S42', 'S39', 'S40', 'S45') THEN 'Urgent_Entry' END AS Entry_Category
    , b.[min_from_date] AS [from_date]
    , b.[max_to_date] AS [to_date]
    , c.[all_status] AS all_legal_status_in_the_episode
INTO #cnp_custody_events
FROM #cnp_group_numbers a
INNER JOIN cnp_group_min b
ON a.[snz_uid] = b.[snz_uid]
AND a.[group_id] = b.[group_id]
INNER JOIN cnp_distinct_legal_status c
ON a.[snz_uid] = c.[snz_uid]
AND a.[group_id] = c.[group_id]
WHERE [min_rn] = [rn]
AND [from_date] ! = [to_date];

/* 2.1 Youth Justice Custody Order Episode */

DROP TABLE IF EXISTS #yju_custody;
SELECT DISTINCT a.[snz_uid]
    , a.[snz_composite_event_uid]
    , b.[cyf_lsd_legal_status_code]
    , a.[cyf_lse_event_from_datetime] AS [from_date]
    , a.[cyf_lse_event_to_datetime] AS [to_date]
INTO #yju_custody
FROM [$(idicleanversion)].cyf_clean.cyf_ev_cli_legal_status_cys_f AS a
INNER JOIN [$(idicleanversion)].cyf_clean.cyf_dt_cli_legal_status_cys_d AS b
ON a.[snz_composite_event_uid] = b.[snz_composite_event_uid]
/*remove orders with an end date less than their start data to clean data errors*/
WHERE a.[cyf_lse_event_from_datetime] < a.[cyf_lse_event_to_datetime]
/*Select custodial orders*/
AND b.[cyf_lsd_in_custody_ind] = 'Y'
/*Remove withdrawn legal orders*/
AND b.[cyf_lsd_court_result_type_code] <> 'WIT'
/*Select orders in the favour of the CE of Oranga Tamariki (or MSD for historical records)*/
AND b.[cyf_lsd_in_favour_of_type_code] IN ('CEO', 'CEMSD', 'CEOT', 'CEOTMC')
/*select the Youth Justice specific custody orders - widely included in YJ custody analysis*/
AND b.[cyf_lsd_legal_status_code] IN (
/*Arrest orders*/
'S235YJ'  /*S235 Arrested/custody CE*/
,'S235CP' /*S235 Arrested/custody CE*/
,'S235' /*S235 Arrested/custody CE*/

/*Youth court remand orders*/
,'S2381D' /*s238(1)(d) Custody pending hearing to the CEO - remanded in the custody of the CE of Oranga Tamariki and most placements are in
a YJ residence or a YJ community home, but small numbers are placed elsewhere like a forensic unit of a hospital for a mental health assessment
or even on occasion a child but not a young person can be placed with a caregiver or whanau*/
,'S2421B' /*s242(1)(b) - Police Detention up to 24 hours*/

/*Adult court remand orders*/
,'S173' /*s173 Criminal Procedure Act - Remand in Oranga Tamariki custody*/
,'S174' /*s174 Criminal Procedure Act - Remand for Assessment*/
,'S175(1A)' /*s175(1A)Procedure Act - Remand in Oranga Tamariki custody*/
,'S175(2)'/* s175(2)Procedure Act - 18/19 yo remand in Oranga Tamariki custody*/
,'S1424B' /*s142(4B) Criminal Justice Remand - historical*/
,'S1425A' /*s142(5A) Criminal Justice Remand for assessment - historical*/

/*Sentenced orders*/
,'S311S283' /*s331 & S283(n) Supervision with residence order*/
,'S311S283YSO' /*s331 & S283(n) Supervision with residence order (with YSO declaration)*/
,'S320S283YSO' /*Military-style academy order (with YSO declaration)*/
,'S3074' /* s307(4) Custody to enable program or activity to be provided - remanded in the custody of the CE of Oranga Tamariki*/
/*but they are not in a YJ custody placement as such*/

/*Prison senctence involving an under 18 yo being served in a YJ residence --- so the person is technically in the custody
of the CE of Corrections, not OT's CE --- so recommend to include the 2 order codes below if defining kids in YJ custody
and exclude them if we specifically look at YJ custody under the CE of Oranga Tamariki*/
,'S34A' /*s34A Corrections Act - Detention in YJ Residence*/
,'S142A' /* s142(A) Criminal Justice Programme - historical*/

/*Usually excluded but these TECHNICALLY can still be in CE of Oranga Tamariki custody so decided to INCLUDE in here*/																														  													  
/*,'S236' */  /*s236 Arrested Police (over 24 hours)*/												   
/*,'S214' */ /*s214 Police Arrest - Decision Pending*/
/*,'S2381E' */ /*s238(1)(e) Custody Pending hearing to the Police*/
/*,'S2381F' */ /*s238(1)(F) Custody pending hearing to the prison youth unit*/
/*,'S2381C' */ /*s238(1)(C) Custody pending hearing to the parents or guardians*/
);

/*Filtering out any duplicate entries*/
DROP TABLE IF EXISTS #yju_custody_dedup;
SELECT *
INTO #yju_custody_dedup
FROM (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY [snz_uid], [from_date], [to_date] 
									ORDER BY [snz_uid], [from_date], [to_date] ) AS [rn]
    FROM #yju_custody
) t
WHERE rn = 1;

/*Keeping latest legal status that start on the same day*/
DROP TABLE IF EXISTS #yju_ranked_custody;
WITH yju_ranked_custody AS(
    SELECT *
        , ROW_NUMBER() OVER ( PARTITION BY [snz_uid], [from_date] ORDER BY [to_date] DESC ) AS [rn_1]
    FROM #yju_custody_dedup
)
SELECT [snz_uid]
    , [snz_composite_event_uid]
    , [cyf_lsd_legal_status_code] AS [legal_status]
    , CONVERT(DATE, [from_date]) AS [from_date]
    , CONVERT(DATE, [to_date]) AS [to_date]
INTO #yju_ranked_custody
FROM yju_ranked_custody
WHERE [rn_1] = 1;

/*Processing data for overlapping legal statuses to create legal episodes*/
/*Note: when the break between an end date and start date is less than 2 days, a child or young people is considered to be in the custody
of the CE continuously. A child or young people can hold multiple legal statuses that indicate their being in the custody of the CE,
but the legal status at the start of the legal episode is taken as the order they entered custody on*/

DROP TABLE IF EXISTS #yju_group_numbers;
WITH yju_ordered_events AS(
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [rn]
    FROM #yju_ranked_custody
)
    , yju_event_with_lag AS(
    SELECT e.*

        , LAG([from_date]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [prev_from_date]
        , LAG([to_date]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date]) AS [prev_to_date]
    FROM yju_ordered_events e
)
    , yju_event_groups AS(
    SELECT *
        , DATEDIFF(DAY, [prev_to_date], [from_date]) AS [DATEDIFF]
        , IIF(DATEDIFF(DAY, [prev_to_date], [from_date]) < 2, 0, 1) AS [new_group]
    FROM yju_event_with_lag
)
SELECT a.*
    , SUM([new_group]) OVER (PARTITION BY [snz_uid] ORDER BY [from_date] ROWS UNBOUNDED PRECEDING) AS [group_id]
INTO #yju_group_numbers
FROM yju_event_groups a;

/* Remove duplicate events, make new table for combined status also only pick the min start date and max end date */
DROP TABLE IF EXISTS #yju_custody_events;
WITH yju_group_min AS(
    SELECT [snz_uid]
        , [group_id]
        , MIN(rn) AS min_rn
        , MIN([from_date]) AS [min_from_date]
        , MAX([to_date]) AS [max_to_date]
    FROM #yju_group_numbers
    GROUP BY [snz_uid]
        , [group_id]
)
    , yju_distinct_legal_status AS(
    SELECT [snz_uid]
        , [group_id]
        , STRING_AGG(
        [legal_status]
            , ';'
    ) WITHIN GROUP(
        ORDER BY from_date
        ASC
    ) AS [all_status]
    FROM (
        SELECT DISTINCT [snz_uid]
            , [group_id]
            , [legal_status]
            , from_date
        FROM #yju_group_numbers
    ) a
    GROUP BY [snz_uid]
        , [group_id]
)
SELECT a.[snz_uid]
    , a.[snz_composite_event_uid]
    , business_area = 'YJU'
    , a.[legal_status] AS first_legal_status_in_the_episode
    , CAST(NULL AS VARCHAR(10)) AS Entry_Category
    , b.[min_from_date] AS [from_date]
    , b.[max_to_date] AS [to_date]
    , c.[all_status] AS all_legal_status_in_the_episode
INTO #yju_custody_events
FROM #yju_group_numbers a
INNER JOIN yju_group_min b
ON a.[snz_uid] = b.[snz_uid]
AND a.[group_id] = b.[group_id]
INNER JOIN yju_distinct_legal_status c
ON a.[snz_uid] = c.[snz_uid]
AND a.[group_id] = c.[group_id]
WHERE [min_rn] = [rn]
AND [from_date] ! = [to_date];

Combine the Care & Protection table and Youth Justice table

DROP TABLE IF EXISTS #custody_events;

SELECT *
INTO #custody_events
FROM (
    SELECT *
    FROM #cnp_custody_events
    UNION ALL
    SELECT *
    FROM #yju_custody_events
) AS combined;
 USE [$(targetdb)];
GO

DROP TABLE IF EXISTS [$(targetschema)].[$(projprefix)_custody_order_episode];
GO

SELECT * 

INTO [$(targetschema)].[$(projprefix)_custody_order_episode]

FROM #custody_events;

GO