Output Table

SQL: [IDI_Community].[edu_quals_moe_enr_prereq].quals_moe_enr_prereq_YYYYMM
SAS: libname cm_enr_pre ODBC dsn=idi_community_srvprd schema=edu_quals_moe_enr_prereq;
How to access a code module in the Data Lab : Read here

Context

Each year information on qualification enrolments by students are provided to SNZ.
The information on qualifications is derived from course enrolments and gives all characteristics of each qualification that students were enrolled in.
In addition to the qualification characteristics, this dataset includes other characteristics of students.
This section describes all the details that are provided in the qualification enrolments data.

Technical Notes on TEC Qualification Enrolments and Completions Data Supply - 2011

1. Data is revised from 2003 to latest year for enrolments data.
2. Data is revised from 2003 to latest year for completions data.
   The missing value codes ‘9’, ‘99’, ‘999’, ‘9999’, etc. are not valid anymore, but valid for the INSTIT & QACC fields.

Stats NZ’s Administrative Population Census (APC) data uses an experimental approach to derive census information from administrative (admin) data.
Highest qualification is a variable that is available in the APC data. Prerequisites to enrol in studies including, training programmes and tertiary education programmes, are considered within APC to calculate the highest qualification. This code module uses the APC prerequisites business rules and will be another data source used towards the highest qualification code module.

Key Business Rules

From these APC tables we get this mapping.

1. If there is no formal secondary school qualification/ 0-13 NCEA Level 1 credits (‘00’), then we will map it to zero
2. If there is (‘01’, ‘12’), then we will map to just a 1
3. If there is (‘02’, ‘03’, ‘13’), then we will map to just a 2
4. If there is (‘04’, ‘05’, ‘07’, ‘15’, ‘14’), then we will map to just a 3
5. If there is (‘08’), then we will map to just a 4
6. Exclusions: (‘09’, ‘11’, ‘98’). These qualifications don’t specify the level because they are too broad
7. The spell dates are approximations. MoE qualifications data only supplies the calendar year in which a qualification was attained.
   Hence the qualification attained date is hard-coded to be the last day of the year in which the qualification was attained. The school qualifications are assumed to be valid for a lifetime, and hence the qualification expiry date is conceptually “infinite” and represented as 31-Dec-9999. The spell is effectively the time period between the qualification attainment and qualification expiry.

Inputs

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {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.
4. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
5. {populationdata}: A population table - the output spell tables/views in the spell dataset will be subset only to include the subjects in this population, further filtered to the dates specified for each subject. the table is expected to contain the ID, start date and end date columns
6. {idcolumn}: The name of ID column in the population table, that identifies a subject of the population. This column is used to filter the database objects- the output spell tables will only contain these subjects.
7. {startdatecolumn}: The name of start date column in the population table.
   The events in the spell tables are subset to those intersecting with this column for that subject. Each subject may have a different start & end date.
8. {enddatecolumn}:The name of end date column in the population table.

Outputs

Dependencies:

{idicleanversion}.[moe_clean].[enrolment] 

Version Log:

Community of Interest:

Code

***************************************************************************************************************************/

/* Establish database for writing views */
/*
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
*/

:setvar targetdb "IDI_Usercode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "tmp"
:setvar idicleanversion "IDI_Clean_202306"

use $(targetdb);

DROP VIEW IF EXISTS [$(targetschema)].[$(projprefix)_moe_quals_enr_prereq];
GO

create view [$(targetschema)].[$(projprefix)_moe_quals_enr_prereq] as

select distinct [snz_uid]
	   ,cast('MOE_ENR' as varchar(50)) as data_source
       ,datefromparts(moe_enr_year_nbr, 12, 31) as [qual_recorded_date] -- datefromparts(moe_enr_first_tertiary_year_nbr, 12, 31)
	   ,datefromparts(9999, 12, 31) as [qual_expiry_date]
	   ,case when [moe_enr_highest_sec_qual_code] = '00' then 0
	         when [moe_enr_highest_sec_qual_code] IN ('01', '12') then 1
	         when [moe_enr_highest_sec_qual_code] IN ('02', '03', '13') then 2
	         when [moe_enr_highest_sec_qual_code] IN ('04', '05', '07', '15', '14') then 3
			 when [moe_enr_highest_sec_qual_code] IN ('08') then 4
		end as nzqflevel --moe_enr_highest_sec_qual_code is Highest secondary school qualification prior to this current enrolment
from [$(idicleanversion)].[moe_clean].[enrolment]
where [moe_enr_highest_sec_qual_code] NOT IN ('09', '11', '98');