Module Output

**SQL: [IDI_Community].[edu_school_enrolment_status].[school_enrolment_status_YYYYMM]
**SAS: libname cm_dlr_ODBC dsn=idi_community_srvprd schema=edu_school_enrolment_status; proc print data = cm_dlr.school_enrolment_status_YYYYMM ;run ;
How to access a code module in the Data Lab:Read here

Purpose

This code creates spells of school enrolment for ākonga enrolled in school in New Zealand. There are two output tables associated with this module corresponding to two use cases:

1. a table containing information about which school each student is enrolled in (enrolment spells)
2. a table containing information about whether a student is enrolled in any school (enrolment status)

This script is responsible for the second table.

Key Concepts

ENROL vs School Roll Return data sources

ENROL is a register of student enrolments. Schools are legally required to update enrolment records in ENROL within 5 school days as students enrol, change schools or leave the school system. The source of the student rolls in the school directory come from ENROL. This is updated on a monthly basis (during the school year). ENROL is also the source data for this module.

School Roll Return (SRR) is another source of roll information. There are four school roll returns every year. All schools complete a snapshot of roll information as at 1 March and 1 July. The Ministry of Education also collects 1 June and 1 September rolls from state and state-integrated schools with students in funding year levels 9 to 15. Charter Schools also complete the 1 June and 1 September returns. Most schools use a student management system (SMS) to manage their student records, and to complete the roll returns for the Ministry. SRR data is reported on the MoE education counts public facing page.

Enrolments vs Attendance

If a student is attending a school, then the school has a legal requirement to enrol that student in ENROL. Please see this public facing Ministry page.

A child can start school when they turn 5, but they are not legally required to start until they turn 6. Once they are enrolled in school they are legally required to attend until they are 16. If a student does not attend school while they are enrolled, this is dealt with based on the school’s internal policies. Once the student has been absent for 20 consecutive school days (unless they have been told that the student’s absence is only temporary) the school should then withdraw them with their last date of attendance at the school. Please see this public facing Ministry page

There are a small number of situations where a student can be enrolled at multiple schools simultaneously. These include dual enrolments with Te Kura, or off-site enrolments with Health Schools or Alternative Education Providers.

Comparison against other sources

At a given snapshot of time, the enrolment spells module can be used to re-create school roll counts. Snapshot roll counts can be summarised from the table [IDI_Clean_].[moe_clean].[school_roll_return] at two different dates every year: March 1st and July 1st. Mismatches can be investigated at the student level.

Public facing school roll summaries, derived from the snapshot School Roll Return, are also available from MoE’s EdCounts page. These public facing summaries do not always match those obtained from the [moe_clean].[school_roll_return] table.

The module tables are derived from the source table [IDI_Clean_].[moe_clean].[student_enrol] through a cleaning process. A comparison to this source can be used to understand the extent of the cleaning process.

References & Contacts

1. James Swindells (MoE)
2. Cheryl Remington (MoE)
3. Angus Prain (Stats)

Development team

Module Business Rules

The [enrolment_status] output table consists of unique combinations of (student, spell start, spell end, enrolment status) (which are snz_uid, startdate, enddate, spell_type as column names in the table). The table is unique on student, startdate and enddate, and spells are never overlaping.

The script uses the following business rules:

1. All business rules that apply to the enrolment_spells table apply
2. Starting from the enrolment_spells table, non-overlapping spells of enrolment status (enrolled or not-enrolled) are constructed

Open Issues/Comments

1. All issues indicated in the [enrolment_spells] script header apply.

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. {max_attendance_date}: the max date in student_attendance table

Dependencies

1. [school_enrolment_spells] code module
2. [data].[personal_detail]

Outputs

{targetdb}.{targetschema}.{projprefix}_school_enrolment_status

Variable Descriptions

Module Version & Change History

Code

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

/*
	:::::::::::::::::::::
	0. GLOBAL SETUP
	:::::::::::::::::::::
*/

DROP TABLE IF EXISTS #t1_complete_spells;
SELECT *
    , ROW_NUMBER() OVER(PARTITION BY snz_uid ORDER BY startdate) AS rn
INTO #t1_complete_spells
FROM $(targetdb).$(targetschema).$(projprefix)_school_enrolment_spells;

GO
 PRINT('school_enrolment_spells TABLE imported to scope');

/*
	BEGIN check

		select * from #complete_spells order by snz_uid, startdate, enddate

	END check
*/

/*
	::::::::::::::::::::::::::::::::::::::::::
	6. Generate the derived 'enrolment_status' table
	::::::::::::::::::::::::::::::::::::::::::
	This table is only enrolment statud, as per the commissioning document
*/

DROP TABLE IF EXISTS #clean_spells; ;
WITH long_dates AS(
    SELECT snz_uid
        , startdate AS date_value
        , 'startdate' AS date_type
        , CASE WHEN unique_spell_reason = 'enrolment_gap' THEN 'not_enrolled' ELSE 'enrolled' END AS spell_type
        , rn
        , sch_enddate_imputed
        , spell_end_reason
        , start_level
    FROM #t1_complete_spells 

    UNION ALL 

    SELECT snz_uid
        , enddate AS date_value
        , 'enddate' AS date_type
        , CASE WHEN unique_spell_reason = 'enrolment_gap' THEN 'not_enrolled' ELSE 'enrolled' END AS spell_type
        , rn
        , sch_enddate_imputed
        , spell_end_reason
        , start_level
    FROM #t1_complete_spells
)
    , pre_spells AS(
    SELECT snz_uid
        , date_value
        , date_type
        , spell_type
        , rn
        , start_level
        , spell_end_reason
        , sch_enddate_imputed
        , MAX(rn) OVER(PARTITION BY snz_uid) AS max_rn
        , CASE WHEN rn = MAX(rn) OVER(PARTITION BY snz_uid) AND sch_enddate_imputed <> 1
    AND date_type = 'enddate' THEN 'END' WHEN LEAD(spell_type) OVER(PARTITION BY snz_uid ORDER BY rn, date_value) <> spell_type THEN 'END' WHEN rn = 1
    AND date_type = 'startdate' THEN 'start' WHEN LAG(spell_type) OVER(PARTITION BY snz_uid ORDER BY rn, date_value) <> spell_type
    AND rn <> 1 THEN 'start' WHEN sch_enddate_imputed = 1
    AND date_type = 'enddate' THEN 'extraction_date' END AS boundary
    FROM long_dates
)
    , long_spells AS(
    SELECT *
        , CASE WHEN LEAD(rn) OVER(PARTITION BY snz_uid ORDER BY rn) >= rn THEN LEAD(date_value) OVER(PARTITION BY snz_uid ORDER BY rn) END AS enddate
        , CASE WHEN LEAD(rn) OVER(PARTITION BY snz_uid ORDER BY rn) >= rn THEN LEAD(spell_end_reason) OVER(PARTITION BY snz_uid ORDER BY rn) END AS reason_for_leaving_next
        , CASE WHEN LEAD(boundary) OVER(PARTITION BY snz_uid ORDER BY rn) = 'extraction_date' THEN 'extraction_date' END AS is_extraction_date
    FROM pre_spells
    WHERE boundary IS NOT NULL
)
SELECT a.snz_uid
    , spell_type
    , DATEDIFF(d, date_value, enddate) AS spell_length
    , date_value AS startdate
    , enddate
    , start_level
    , CASE WHEN is_extraction_date IS NOT NULL THEN 'Enddate Imputed' 
	WHEN spell_type = 'not_enrolled' THEN 'New Enrolment'
	ELSE reason_for_leaving_next END AS spell_end_reason
INTO #clean_spells
FROM long_spells a
WHERE date_type = 'startdate'; 

GO
 PRINT('#clean_spells TABLE generated'); 

/* adding age at start and end of spell */
DROP TABLE IF EXISTS #clean_spells_with_age;
SELECT a.*
    , DATEDIFF(YEAR, snz_birth_date_proxy, startdate) AS age_at_startdate
    , DATEDIFF(YEAR, snz_birth_date_proxy, enddate) AS age_at_enddate
INTO #clean_spells_with_age
FROM #clean_spells a
JOIN $(idicleanversion).[data].[personal_detail] b
ON a.snz_uid = b.snz_uid ;

GO
 PRINT('#clean_spells_with_age TABLE generated'); 

DROP TABLE IF EXISTS $(targetdb).$(targetschema).$(projprefix)_school_enrolment_status;

SELECT *

INTO $(targetdb).$(targetschema).$(projprefix)_school_enrolment_status

FROM #clean_spells_with_age;