Output table

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

Context

The source dataset contains training activity information for people in workplace- based training eligible for funding through the industry training and Modern Apprenticeships funds. We are going to use the prerequisites that people list when applying for this type of course as a source of data for qualifications. Note that the granularity of the qualifications is not very high so we are not going to be able to get detailed information, just basic levels.

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 qualification, then we will map it to zero
2. NCEA L1 or Equivalant we will map to just a 1
3. NCEA L2 or Equivalant we will map to just a 2
4. NCEA L3 or Equivalant we will map to just a 3
5. If they have a Post School NZ/DIP we will map this to a 5 (even though we know it may be anything between 1 and 6)
6. If they have a Bachelors or above we will map this to 7 (even though we know it may be anything between 7, 8, 9, & 10)
7. If it is unknown we will exclude as APC does
8. 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].[tec_it_learner]

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_tec_prereq];
GO

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

select distinct [snz_uid]
       ,cast('TEC_IT_HIST' as varchar(50)) as data_source
       ,[moe_itl_start_date] as [qual_recorded_date]
	   ,datefromparts(9999, 12, 31) as [qual_expiry_date]
	   ,case when [moe_itl_prev_qual_text] IN ('NO QUAL') then 0
	         when [moe_itl_prev_qual_text] IN ('NCEA L1 OR EQUIV') then 1
	         when [moe_itl_prev_qual_text] IN ('NCEA L2 OR EQUIV') then 2
	         when [moe_itl_prev_qual_text] IN ('NCEA L3 OR EQUIV') then 3
	         when [moe_itl_prev_qual_text] IN ('POST SCHOOL NC/DIP') then 5
	         when [moe_itl_prev_qual_text] IN ('BACHELORS OR ABOVE') then 7
		end as nzqflevel
		--,moe_itl_ito_edumis_id_code as provider_code --A code that uniquely identifies the industry training provider that the student provided their highest education qualification on entry to the programme.

		--,moe_itl_edumis_2016_code as provider_2016_code			 			  
from [$(idicleanversion)].[moe_clean].[tec_it_learner]
where [moe_itl_prev_qual_text] NOT IN ('UNKNOWN');