Oranga Tamariki - Child Placement spell

jiabin.lin
19 December 2025

Outputs:

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

Inputs:

Dependency Dependency Type
[IDI_Metadata_202603].[ot_cyf].[plcmt_type_code] Source
[IDI_Clean_202603].[cyf_clean].[cyf_placements_details] Source
[IDI_Clean_202603].[cyf_clean].[cyf_placements_details_extra] Source
[IDI_Clean_202603].[cyf_clean].[cyf_placements_event] Source

Purpose

This module creates a table of all placement spell 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 child placement?

Placement is the legal arrangements Oranga Tamariki makes for a child in its care, which can include whÄ<81>nau/family, residential, or foster care.
These placements are important because they ensure a child is safe and has their needs met, either by remaining with their immediate family (if safe) or by being placed with a suitable caregiver.
The specific placement depends on the child’s individual situation and needs.

2. Why is a child placement important?

3. What can a child placement spell be used for?

4. How to identify children in Oranga Tamariki placement?

Children in Oranga Tamariki placements can be identified by using the following business rules:

Notes:

Comparison against other sources

A comparison between the result of this code module and that of an Oranga Tamariki internal report on the number of children and young people in the Care and Protection or Youth Justice placement has been done.
The effectiveness of this code module has been verified by the comparison result.

Data source:
Oranga Tamariki internally published SSORT dashboard
Tabs: Out of Home Placement (CNP), Rangatahi in Custody (YJ)

References & Contacts

Code module technical information

See details below.

Community of Interest

Role Organisation Name
Lead Developers Oranga Tamariki Jiabin Lin
Oranga Tamariki Thuong Nguyen
Peer reviewers(code) Nicholson Consulting Todd Nicholson
Peer reviewers(documentation) Oranga Tamariki(former) Jacquelyn Kirkland
Oranga Tamariki(former) Steve Murray
Oranga Tamariki Duncan McCann
Analyst advisors N/A
Data suppliers Oranga Tamariki
Policy experts N/A
Module steward Oranga Tamariki Duncan McCann

Module Business Rules

Module business rules have been introduced in section 4. How to identify children in Oranga Tamariki placement.

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

Aspect Variables Description
Entity snz_uid* IDI based unique person id
Event information business_area Business area, Care and Protection (CNP) or Youth Justice (YJU)
placement_type The placement type
placement_type_label The placement type label
in_or_out_home In-Home or Out-Home placement
payment_only whether is payment only
carer_id The id of caregiver
Period from_date, to_date Period of custody order episode

Module Version & Change History

Date Version Comments
11 July 2025 Initial Version based on specifications from Commissioning document.

Code

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

/* Create a placement events table */
DROP TABLE IF EXISTS #placement_events;

SELECT snz_uid
    , snz_composite_event_uid
    , cyf_ple_source_uk_var1_text AS placement_id
    , cyf_ple_event_from_date_wid_date AS from_date
    , cyf_ple_event_to_date_wid_date AS to_date
INTO #placement_events
FROM [$(idicleanversion)].[cyf_clean].[cyf_placements_event];

/* Create a placement details table */
DROP TABLE IF EXISTS #placement_details;

SELECT snz_composite_event_uid
    , cyf_pld_full_time_ind
    , cyf_pld_open_suspension_ind
    , cyf_pld_open_suspension_date
    , cyf_pld_business_area_type_code
    , cyf_pld_placement_type_code
    , cyf_pld_output_class_code
INTO #placement_details
FROM [$(idicleanversion)].[cyf_clean].[cyf_placements_details];

/* Create a placement details extra table */
DROP TABLE IF EXISTS #placement_details_extra;

SELECT [cyf_plc_respite_ind]
    , [cyf_plc_rsrc_id_nbr] AS carer_id
    , [cyf_plc_source_uk_var1_text] AS placement_id
INTO #placement_details_extra
FROM [$(idicleanversion)].[cyf_clean].[cyf_placements_details_extra];

/* Join the placement events, details, and details extra*/
DROP TABLE IF EXISTS #combined;

SELECT DISTINCT a.snz_uid
    , a.snz_composite_event_uid
    , a.placement_id
    , b.cyf_pld_business_area_type_code
    , b.cyf_pld_placement_type_code
    , a.from_date
    , a.to_date
    , b.cyf_pld_full_time_ind
    , b.cyf_pld_open_suspension_ind
    , b.cyf_pld_open_suspension_date
    , b.cyf_pld_output_class_code
    , c.cyf_plc_respite_ind
    , c.carer_id
INTO #combined
FROM #placement_events a
INNER JOIN #placement_details b
ON a.snz_composite_event_uid = b.snz_composite_event_uid
INNER JOIN #placement_details_extra c
ON a.placement_id = c.placement_id;

/* Remove respite placement*/

DROP TABLE IF EXISTS #combined_1;

SELECT *
INTO #combined_1
FROM #combined
WHERE cyf_plc_respite_ind = 'N';

/* If placements with the same resource have the same start date we only keep those with the later end date*/

DROP TABLE IF EXISTS #combined_1_1;

WITH ranked AS(
    SELECT *
        , ROW_NUMBER() OVER (
		PARTITION BY snz_uid, cyf_pld_business_area_type_code, cyf_pld_placement_type_code, carer_id, from_date
		ORDER BY to_date DESC
		) AS rn
    FROM #combined_1
)
SELECT *
INTO #combined_1_1
FROM ranked
WHERE rn = 1;

/* Apply 1-day rule to combine records*/

DROP TABLE IF EXISTS #combined_2;

WITH numbered AS(
    SELECT snz_uid
        , cyf_pld_business_area_type_code
        , cyf_pld_placement_type_code
        , carer_id
        , from_date
        , to_date
        , CASE
		WHEN LAG(to_date) OVER
			(PARTITION BY snz_uid, cyf_pld_business_area_type_code, cyf_pld_placement_type_code, carer_id
			ORDER BY from_date) IS NULL
			OR (DATEDIFF(DAY, LAG(to_date) OVER
			(PARTITION BY snz_uid, cyf_pld_business_area_type_code, cyf_pld_placement_type_code, carer_id
			ORDER BY from_date), from_date)) >= 2
		THEN 1 ELSE 0 END AS new_group_flag
    FROM #combined_1_1
)
    , grouped AS(
    SELECT *
        , SUM(new_group_flag) OVER (PARTITION BY snz_uid, cyf_pld_business_area_type_code, cyf_pld_placement_type_code, carer_id
		ORDER BY from_date
		rows BETWEEN unbounded preceding AND current row) AS grp
    FROM numbered
)
SELECT snz_uid
    , cyf_pld_business_area_type_code
    , cyf_pld_placement_type_code
    , carer_id
    , MIN(from_date) AS from_date
    , MAX(to_date) AS to_date
INTO #combined_2
FROM grouped
GROUP BY snz_uid
    , cyf_pld_business_area_type_code
    , cyf_pld_placement_type_code
    , carer_id
    , grp
ORDER BY snz_uid
    , cyf_pld_business_area_type_code
    , cyf_pld_placement_type_code
    , carer_id
    , from_date;

/* Add placement label */

DROP TABLE IF EXISTS #combined_3;

SELECT a.*
    , b.label
INTO #combined_3
FROM #combined_2 a
LEFT JOIN [$(idimetaversion)].ot_cyf.plcmt_type_code b
ON a.cyf_pld_placement_type_code = b.code;

/*
	Care and Protection placement
*/

/*
1. Filter CNP business area.
2. Create a indicator to indentify in-home or out-home placement.
*/

DROP TABLE IF EXISTS #cnp_plac_spell;

SELECT *
    , CASE WHEN label IN ('Return Home', 'Remain Home', 'Independent Living') THEN 'IN Home'
		ELSE 'Out of Home' END AS in_or_out_home
INTO #cnp_plac_spell
FROM #combined_3
WHERE cyf_pld_business_area_type_code IN ('CNP');

/* 1. Remove records where the from_date and to_date are the same. */

DROP TABLE IF EXISTS #cnp_plac_spell_1;

SELECT *
INTO #cnp_plac_spell_1
FROM #cnp_plac_spell
WHERE from_date <> to_date;
  1. Create a payment_only indicator
    In some cases, there will be a Regular Payment or ETRR: Rangatahi placement open alongside another placement type.
    Regular Payment usually exists for administrative purposes, to allow payments to caregiver or young person.
    However, some family/whanau placements are recorded as regular payment unitl the caregiver approval process is complete.
    ETRR: Rangatahi should only over be recorded in tandem with an ETRR: Caregiver Placement, and are used when board
    top-ups are paid directly to rangatahi. The below creates a new column called payment_only to indicate the placement is
    just being used to allow payment, and does not indicate a true placement. A “1” will be added to the column for all ETRR: Rangatahi placements,
    as well as Regular Payment placements that exists alongside another non-Regular Payment placement.
  2. Remove records where payment_only = 1

DROP TABLE IF EXISTS #cnp_plac_spell_2;

WITH t_payment_only_indic AS(
    SELECT all_pcmts.*
        , CASE
	      WHEN all_pcmts.label IN ('ETRR: Rangatahi', 'ETRR: TSS Support Arrangements') THEN 1
		  WHEN
		       all_pcmts.label = 'Regular Payment'
			   AND EXISTS (
			   SELECT 1
			   FROM #cnp_plac_spell_1 AS concurrent_pcmts
			   WHERE
			   concurrent_pcmts.snz_uid = all_pcmts.snz_uid
			   AND concurrent_pcmts.label != 'Regular Payment'
			   AND concurrent_pcmts.from_date <= all_pcmts.from_date
			   AND concurrent_pcmts.to_date >= all_pcmts.to_date
			   )
			   THEN 1 ELSE 0
			   END AS payment_only
    FROM #cnp_plac_spell_1 AS all_pcmts
)
SELECT *
INTO #cnp_plac_spell_2
FROM t_payment_only_indic
WHERE payment_only = 0;

Youth Justice placement


/* 
Select records where business area is YJU
Apply the same rules as above
*/

DROP TABLE IF EXISTS #yju_plac_spell;

SELECT *
    , 'NULL' AS in_or_out_home
    , NULL AS payment_only
INTO #yju_plac_spell
FROM #combined_3
WHERE(
    cyf_pld_business_area_type_code IN ('YJU')
    OR cyf_pld_placement_type_code IN ('RESYJ', 'RESCJP', 'DPC')
)
AND cyf_pld_placement_type_code NOT IN ('RETHM', 'REMHM', 'INDEP', 'CYP', 'WHA', 'REG', 'YOOC', 'BAILHOME');
/*Bail Home placements are not for those in custody, they are intended for those placed on bail orders by the court as a means
providing a safe address to be bailed to when they may have been denied bail otherwise. These have been excluded from this
module which is designed to produce placements for those in YJ custody primarily*/
/* 
Numerous placement records data quality isses which are not YJ relevant but can occur and so should be removed,
following placement types represent the majority of the erroneous records 'Return Home', 'Remain Home', 'Independent Living', 'Foster Carer Placement',
'Family/Whanau Placement', 'Regular Payment', 'YSS One-to-One Care Placement'
*/

DROP TABLE IF EXISTS #cnp_yju_plac_spell_2;

WITH combined_plac_spell_2 AS(
    SELECT *
    FROM #cnp_plac_spell_2
    UNION ALL
    SELECT *
    FROM #yju_plac_spell
)
SELECT snz_uid
    , cyf_pld_business_area_type_code AS business_area_type
    , cyf_pld_placement_type_code AS placement_type
    , in_or_out_home
    , payment_only
    , label AS placement_type_label
    , carer_id
    , from_date
    , to_date
INTO #cnp_yju_plac_spell_2
FROM combined_plac_spell_2;

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

SELECT * 

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

FROM #cnp_yju_plac_spell_2;

GO