Module Output

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

Purpose of the Migration Spells module

The purpose of this module is to construct migration spells for individuals who are not citizens of New Zealand, based on movements across the border and the visas held by them during the period of stay in NZ. These spells can be used to produce estimates of non-New Zealand citizens who are lawfully visiting, studying, working and living in New Zealand at a given point of time. The output of this code module is one row per person per spell start date, which can also be defined as one row per person per (approved) visa application per arrival date in NZ.

A migration spell is defined as any lawful period of stay in New Zealand by a person who is not a New Zealand citizen. The period starts either by:

• a person entering the country having a valid visa, or
• being in the country and being granted a new visa during their stay, or
• being born in the country on or after 1 January 2006 to parents who are not citizens and do not hold a visa that allows them to reside in New Zealand indefinitely.

The migration spell ends when:

• a person on a valid visa leaves the country, or
• their visa expires or gets cancelled or transferred to another visa, or
• the person dies.

Note the following exceptions in the output of this code module:

1. The spells do not include movements of individuals who came to New Zealand for short visits from visa-waiver countries or transit visas. Note that visa-waiver countries may change through time.
2. The spells do not include individuals who are New Zealand citizens; or any spells after a person acquires New Zealand citizenship. It will only contain spells before a person acquires citizenship, or after a person relinquishes it.
3. While this dataset contains migration spells from 1 July 1997, it is best suited for identifying spells from 1 January 2000 onwards due to data quality and completeness considerations.
4. A migration spell is based on lawful visits, but this spell dataset also retains unlawful stays in New Zealand wherever those were identified by the MBIE Immigration source systems. This will not cover all unlawful stays, only cases where such a stay was identified and so is not representative of all unlawful spells.
5. This dataset should not be used as a proxy for overseas spells, as it is not limited to travel across borders, and does not cover citizens.
6. This dataset will not contain immigration spells from visitor visa-free countries (including Australia) - unless they applied for study, work, extension of visitor visa, residence visa. In such cases they will be part of the migration spells module output.

Key concepts

Regarding border movements

Border movements data contains all individuals who have a record of movement across New Zealand’s borders. This includes migrants, international visitors and New Zealand citizens. This data is collected by New Zealand Customs Service. This data contains movements across air and seaports, and covers the direction of movement (i.e., arrival or departure), port of embarkation and disembarkation, nationality, etc. The data will include any movements captured by NZ Customs, including pilots, cabin crew, etc.

Regarding visa applications and grants

Immigration New Zealand handles all visa applications, processing and approvals/declines. Every visa application has a specific application number assigned to it as an identifier. Several individuals may be part of an application, and one individual may have several visa applications - but the combination of person and application number is unique for the purposes of tracking a visa application. For Individuals who come to New Zealand from a visitor visa-free country (or for purposes that do not require a visa), will not have visa application numbers and so are not covered in the application data.

The AMS Application Management System for electronic management of visa applications came into existence in 1997. This forms the basis for the data collected by Immigration NZ and is why the current data inside the IDI is reliable only from 1 July 1997 onwards. The visa application data contains all visa applications decided by Immigration New Zealand since this period, and the data was collected from visa application forms (both paper and electronic).

Changes due to the Immigration Act 2009

The Immigration Act 2009 came into force 29 November 2010. This can be expected to cause a break in the data time series, with new application and visa types introduced and older ones retired. Some notable changes are:

1. Permits and Exemption application types were retired.
2. An Interim visa type was introduced to cover the time that a foreign national stays in NZ while they’ve applied for a new visa, to ensure the validity of their stay in NZ during the time the new application is being decided upon.
3. Returning Resident Visas (both indefinite and non-indefinite) were retired. Permanent Residency replaced the indefinite Returning Resident Visa. A Resident Visa with travel conditions replaced the non-indefinite RRV. There will not be any permanent resident data before 2010 owing to this reason.

Temporary changes to the Immigration Act as part of COVID response

On 20 March 2020, New Zealand borders were closed to everyone, except for arrivals into NZ for permanent residents and citizens (even residents were disallowed entry). For any exceptional entry into NZ, a critical purpose had to be demonstrated by individuals. COVID critical purposes visa was created for this purpose during this period of border closure. Note that there were other kinds of COVID visas also created in the post-COVID period.

Administrative Population Census (APC) comparison

General APC information

The APC captures a related but different concept of ‘migrant’ and ‘resident’. The APC data identifies the ‘usually resident population’ who lives in New Zealand at a given reference date. It uses the 12/16-month rule to identify residency status: to become a ‘usual resident’ an individual moving to NZ must spend 12 out of 16 months in the country.

There is a variable within the APC time series data, which is also asked as a question in the usual census, that identifies a migrant individual. The variable is called years_since_arrival_in_nz. This is defined as the number of years for a person born outside of New Zealand first arrived in New Zealand as a permanent or long-term resident as determined by the 12/16 rule.

APC tables in IDI

The variable years_since_arrival_in_nz can be found within the APC table data.apc_time_series. For a given reference year, a person will exist in this table for each reference year they are classified as a ‘usual resident’. Details of a person and the persons arrival in to New Zealand (including year and month of arrival, and country of birth) can be found within the APC table data.apc_constants.

Key difference between APC vs Migration spells code module

APC data: A migrant is based on outcomes-based 12/16-month rule and being born overseas (this can include overseas-born New Zealand citizens and Australian citizens).

Migration spells code module: A migrant is a Non-New Zealand citizen (or Non-Australian citizen) that arrives into New Zealand on a work, resident or permanent resident visa.

APC data does not identify if the person is a New Zealand citizen or not. For example, if a person was born overseas however has New Zealand citizenship through their parents, the measures years_since_arrival_in_nz may have values if they have been identified with a migrant status (based on the outcomes-based 12/16-month rule) and is identified as part of the ‘usual resident population’.

APC vs Migration spells code module - some examples of use cases

Practical notes

Within the IDI data, a few issues have been noted:

1. The movements data (dol_clean.movements) sometimes shows successive arrivals or departures, which does not seem logically possible. These instances were removed as part of the code module. The Immigration subject matter experts report that this is a common occurrence when it comes to coverage of people who arrive or depart by cruise ships. For example, when a person arrives in NZ by cruise ship, but departs through international airline; the Customs data will capture departure data but may miss the arrival by cruise ship. Similarly, Antarctic expeditions may also miss specific arrivals/departures.

2. There are duplicate rows in the visa decisions dataset (dol_clean.decisions). These are not exact duplicate rows - only duplicate occurrences of the business key (snzuid + visa application number) for this dataset. In order to ensure only a single row is picked for each combination of snz_uid + application number, certain business rules have been added into this code module.

3. There are duplicates in dol_clean.immidata_visas on snz_uid and visa application number. In order to ensure only a single row is picked, certain business rules have been added into this code module.

4. There are cases where records have missing visa end dates (which subject matter experts at Immigration NZ have confirmed should not happen). Imputation has been performed in this code module to fix such issues.

5. The output from the code modules is comparable to the numbers published from the Migration Data Explorer - however, it should be noted that additional filters would need to be applied to the code module output to match the Recent Residents visa type. The published numbers define recent residents as individuals who obtained their residency less than five years before the reference date of reporting.

6. There are a large number of exact row duplicates in the Unlawful Stays dataset ([dol_clean].[unli_history]). This has been handled by taking a distinct set of rows from this table in this code module.

7. When comparing numbers generated by this code module to externally published data, take note that the best comparison would be to use the Migration data explorer. The numbers cannot be directly compared to immigration statistics published by Statistics NZ because they use the [12/16 month rule] (http://www/stats.govt.nz/about-us/what-we-do/current-projects/migration-data-transformation-project#faq) - i.e., an overseas resident who arrives in NZ and cumulatively spends 12 out of the next 16 months in NZ is treated as a “migrant arrival”. The data source for immigration statistics published by StatsNZ is also different from what is used here.

References & Contacts

1. Immigration statistics
2. Migration data explorer

Community of Interest

Domain	Agency	Person
Lead Subject Matter Expert	MBIE	Nafees Anwar
IDI Experts	MBIE	Nafees Anwar
Data Supply Experts	MBIE	Andrew Hoy
Policy/Operational Experts	MBIE	Jess Williamson
Other Parties	MSD	Sam Mortlock
	Productivity Commission	Hamed Shafiee
		Hilary Devine
	MPI	Mark Hampshire
	Otago University	Frederieke Sanne Petrovic-van der Deen
	MoE	David Earle
	SWA	Penny Mok

Module Business Rules

Creating Movement Spells

1. The movements dataset in the IDI (dol_clean.movements) holds the border movements in and out of New Zealand for each person (regardless of whether they are a citizen), and the date-time when this movement occurred. From this dataset, we sort the movements for each person based on ascending order of movement date-time. We retain records for each person where the movements alternate between arrival and departure (i.e., an arrival follows a departure and vice-versa). It is highly unlikely that a person can have successive arrivals or departures (essentially crossing the border in the same direction successively) and this is likely a data quality or missingness issue rather than a conceivable real-world scenario (as described earlier). In cases where there are successive records with the same movement type, retain the record with the latest “movement posted date”; the assumption here is that in case of successive records with the same movement type, the record with the latest posted date would be a correction issued to previously entered incorrect records. Migration data is reliable only from the year 2000 onwards, but we retain the movements data from 1990 to reduce any errors in obtaining movement sequences.

2. If the first movement of a person is a departure from NZ, this movement is removed. The scope of this module is to identify migration spells, so if the first movement of a person is a departure from NZ, it is unlikely that they are a migrant at the time of the move, unless they were born in NZ to migrant parents under various visa criteria. Such births can be examined separately (but are currently not captured by this code). It is possible that subsequent movements for these persons into NZ can be made after they’ve relinquished NZ citizenship, hence all subsequent movements are retained. All movement sequences now start with an arrival into NZ.

3. Next, we create arrival-departure pairs from movements. In cases where a departure has not occurred (i.e., the individual is still in NZ), the departure details are left blank. The default departure date in such cases are set as “9999-12-30”.

Creating Approved Visa spells

1. From the visa decisions data available in the IDI (dol_clean.decisions), we retain only the approved visas, and also exclude any visas where the application criteria is Transit (for transiting through New Zealand to a different country), Confirmation (a process for a migrant already in NZ to confirm their immigration status) or Sponsor Process (a process where another individual/organisation in New Zealand submits an sponsorship application for an individual - in such cases the individual will have a separate visa application). Any duplicates on a person and visa application number are also removed here by prioritising on the latest decision date, visa expiry date, etc. This visa decision dataset is then joined to the visa details dataset (dol_clean.immidata_visas) on the basis of person and visa application number, for obtaining all the necessary details regarding the visa application. Note that only applications that belong to the Visa, Group Visa or Permit application categories are retained and all other applications (like Appeals, Exemptions, etc. which are administrative processes and not visas) are discarded. Note that the visa data is available from 1997 onwards, but is reliable only from the year 2000 onwards.

2. There are several cases where multiple overlapping visa applications may be approved for the same person at a point in time. A specific set of business rules is used to remove such duplicates and to prioritise the actual visa type that the person is on, during their stay in New Zealand. These rules are described below:

   • If there are 2 (or more) approved visa applications with the same visa start date, with one under the ‘Visa’ category and another under the ‘Permit’ category, then the Permit record holds precedence, and the Visa category application records are discarded.

   • If a person is on a visa issued under the “Section 61” criteria, and on any other visas with the same visa start date, then the “Section 61” record is discarded. Note- Section 61 of the Immigration Act allows for special temporary or resident visa to be issued if the current visa of an individual expires, under special conditions.

   • If a person is on a Visitor type Visa, but also on a Work/Resident/Permanent Resident Visa with the same visa start date, then the Visitor Visa record is discarded.

   • If a person is on a Work Visa, but also on a Resident/Permanent Resident Visa with the same visa start date, then the Work Visa record is discarded.

   • If a person is on an Interim visa, and on any other visas with the same visa start date, then the Interim visa record is discarded.

   • If a person is on a visa issued under “Crew of foreign fishing vessel Archive” criteria, and on any other visas with the same visa start date, then the “Crew of foreign fishing vessel Archive” record is discarded.

   • If a person is on an approved visa application under the category of Returning Residents Visa (RRV), and another approved visa application record under the criteria of “Grant of Second or Subsequent Resident Visa” exists with the same visa start date, then the RRV record is discarded.

   • If a person has 2 (or more) approved visa applications of the same visa type, where one visa has a visa start date after the decision entered date of the second approved application, then the first application record is discarded and the second record is retained. The assumption here is that since the visa types are the same and their validity dates overlap, the second approved visa supersedes the first.

   • If there are later approved visa applications for an individual where the application grounds and visa end date are the same (and not an “infinite” end date), then treat the later records as superseding the earlier visa application record and discard the earlier application. Note, when a visa replacement occurs, the application grounds will be different; hence there is minimal risk of deletion of an earlier visa record that the individual moved to NZ on.

3. The approved visa records are now arranged in ascending sequence of the application completed date (the date the visa label is entered), decision date entered, decision date, visa start date, visa end date. If all of these values are the same, the application number is used as a tie-breaker to arrange the sequence of approved visas. Based on this sequence, if any record is missing a visa end date, the subsequent visa record’s start date -1 day is used as a proxy end date (if it exists). Also, if the current records visa end date exceeds the next record’s visa start date, the same logic is applied to fix the end date. Similar corrections are applied to the visa start date as well, if it exceeds the next record’s visa start date. The intent of this exercise is to get non-overlapping periods of visa records for individuals.

4. Finally, we correct the visa grounds for each approved visa record, wherever the application grounds is of ‘Interim’ or ‘Replacement’. In such cases, the visa grounds from the closest previous visa record of the same visa type is applied (assuming those are not interim or replacement grounds). If there is no previous visa record of the same type, then the closest grounds that are not ‘Interim’ or ‘Replacement’ are used.

Combining Movement spells and Approved Visa spells to create a migration spell

1. We now combine the movement records and visa spells on the basis of the individual’s snz_uid, such that the visa decision date is before or equal to departure date, and visa start date is before departure date, and visa end date is equal to or after arrival date. We also ensure that at least one of the following conditions is true -

   • arrival date is between visa start and end dates, OR
   • departure date is between visa start and end dates, OR
   • departure date is between visa start and expiry date of stay (the last day a visa holder can stay in NZ), OR
   • arrival is not after expiry date of travel (i.e., final date when applicant could travel to NZ to take up their visa) and expiry date of stay.

2. A migration spell is defined as a term of stay in NZ under a valid visa. This means that an arrival, or a departure, or a change in visa status can trigger a new migration spell for the individual. To define the migration spell start and end dates, the following logic is used:

   • If the current record is a fresh arrival, then arrival date is the start of this spell.
   • If the current record is part of an earlier arrival-departure spell, but the visa start date is on or after arrival, then a visa status change has happened during the individual’s stay in NZ, and the spell start date becomes the visa start date. In case the visa start date is set in the future (after the current date), use the decision entered date instead.
   • If the departure date of the record is after the visa end date (and the visa end date is not set after the current date), then a change in visa status has ended the current spell. Use visa end date as the spell end date. Else, use the departure date as the spell end date of the current record.

3. Any overlapping spells from the reassignment of spell start and end dates are corrected and the spell end of the current record is set to one day before the spell start of the subsequent record after ordering the spells based on arrival date, visa start date, visa end date, spell start, spell end and application_number.

4. If the individual passed away during their migration spell, the (proxy) date of death is used to replace the spell end date, and any subsequent spells of the person is discarded.

5. Finally, any unlawful spells the individual might have had are added into the migration spells dataset. These spells are obtained from [dol_clean].[unli_history]. Any overlapping spells due to this addition are corrected, and if an unlawful spell is subsumed by another lawful spell starts on the same day as the unlawful spell and ends on or after the unlawful spell end, then such unlawful spells are discarded.

6. This final table is then linked with occupation data for skilled migrants (primary applicants) to obtain the job offer occupation type under which skilled migrants were approved for a visa.

Adding births to migrant parents on specific visa statuses

This has not been currently implemented as part of migration spells dataset, but is planned as a future enhancement.

Open issues/comments

1. Migration spells that start owing to births in NZ are not currently included in the code module output. These individuals will only appear in the output once they have completed an arrival into NZ.

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. {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. {idimetadataversion}: The IDI Metadata version that the lookup tables need to be based on.

Dependencies

{idicleanversion}.[dol_clean].movements
{idicleanversion}.[dol_clean].decisions
{idicleanversion}.[dol_clean].immidata_visas
{idicleanversion}.[dol_clean].unli_history
{idicleanversion}.[dia_clean].deaths
{idicleanversion}.[dol_clean].[occupations]
[IDI_Metadata_YYYYMM].[mbie_dol].[grounds_code]

Outputs

This module has been deployed within the Data Lab and is available as refresh-timestamped tables under the following database and schema:

[IDI_Community].[MBIE_MIGRATION_SPELLS]

Alternatively, users who wish to run the script themselves will find output created at:

{targetdb}.{targetschema}.{projprefix}_migration_spells

Variable descriptions

Column name	Description
data_source	A tag signifying a source dataset description (hard-coded to “NIR_TARGET”)
snz_uid	The unique STATSNZ person identifier for the the individual
identity_code	The immigration/customs identifier for a person.
application_number	The identifier for a unique visa application made by an individual.
spell_start	The start of a migration spell, triggered by arrival or change in visa status.
spell_end	The end of a migration spell, triggered by departure or change in visa status or death.
arrival_date	The date of arrival for the current migration spell. Note that this may be earlier than the spell_start date if the current spell is due to a change in visa status.
depart_date	The departure date from NZ (if one exists) for the current migration spell. Will be NULL for unlawful spells. For spells that are currently active (i.e., the person is still in NZ), the value would be 30-12-9999.
v_startdate	The visa validity start date.
v_enddate	The visa end date. Note that indefinitely valid visas (like permanent residency) will have 30-12-9999 as the end date
spell_duration	Duration of the migration spell (as defined by arrival/departure or visa status change)
days_out_after_dep	Days spent outside of NZ after the current record’s departure date
days_out_after_spell	Days spent outside of NZ after the current record’s spell end date
application_grounds	The original reason/grounds code for the visa application that the spell is part of.
spell_grounds	The reason/grounds code for the visa application, but corrected and imputed for the migration spell under specific business rules. Hence this will be different from application grounds in some cases.
spell_app_number	The application number from which the spell record inherits its spell grounds if it was imputed or corrected.
visa_type	The type of visa the individual is on, during the migration spell. Derived based on the spell grounds code. Examples - Work, Resident, Visitor, etc.
application_substream	A subclassification that describes the type of visa under the visa_type
application_criteria	Specific immigration policy or category against which the visa application is decided
application_category	Broad category of the application (e.g. Border, Visa, Permit, Sponsor)
application_type	Broad type of visa applied for
first_res_date	The date a person first gained residency in New Zealand. Useful for identifying recent residents which can be used to compare with numbers published by Immigration NZ.
occupation_code	NZSCO or ANZSCO code of the occupation of the job offer for the migrant under which the visa was granted. This only exists for the primary applicant, for those who applied for a visa using the Skilled Migrant category. Note, this attribute changes through time. The NZSCO was in use from December 2002 onwards until February 2008, when the ANZSCO became the standard. Hence the information held by this column changes through time and users of the module need to be aware of this while looking at earlier migration data. Use the occupation_standard column to check the type of coding for occupations.
occupation_standard	Defines the classification standard used to code the occupation (ANZSCO or NZSCO).
occupation_name	The name of the occupation under which a visa was granted.
occupation_priority	Indicator for whether this is a priority occupation. 1-Yes
occupation_skill_level_code	ANZSCO occupational skill level for applicant specifying an occupation. Associated with the primary applicant only.

Module Version & Change History

Code

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

:setvar targetdb "IDI_Sandpit"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "[IDI_Clean_202306]"
:setvar idimetadataversion "{idimetadataversion}"


/* Assign the target database to which all the components need to be created in. */
use $(targetdb);

/* Delete the database object if it already exists */
drop table if exists $(targetschema).$(projprefix)_spell_moves;
go


/* We start with defining arrival-departure spells; this will provide spells of time spent within NZ borders.*/
/* 1. Set cutoff date for the IDI refresh */
with cutoff_date as (
	select top 1 cast(create_date as date) as cutoff_date from $(idicleanversion).sys.tables where name = 'movements'
)
/* 2. Clean movement spells */
,movements_clean as (
	select 
		*
	from (
		select 	
			m.snz_uid as snz_uid
			,m.snz_dol_uid as identity_code
			,m.[dol_mov_carrier_date] as carrier_date
			,m.[dol_mov_carrier_datetime] as carrier_datetime
			,m.[dol_mov_movement_ind] as movement_indicator
			,m.[dol_mov_visa_type_code] as permit_type_code
			,m.[dol_mov_visa_issued_date] as permit_date_issued
			,m.[dol_mov_visa_expiry_date] as permit_date_expiry
			,m.[dol_mov_posted_datetime] as posted_datetime
			/* Only retain successive records where the movement indicators are different, i.e., a departure follows an arrival and vice-versa. Successive records with the same
				movement type is quite likely an error, attempt at correcting a data entry error, or data quality problem. We use descending sort by [dol_mov_posted_datetime] because 
				later records posted for the same move may be corrections issued to previous posted records.
			*/
			,case 
				when lag(m.dol_mov_movement_ind) over (partition by m.snz_uid order by m.[dol_mov_carrier_datetime] asc, [dol_mov_posted_datetime] desc) is null 
					or lag(m.dol_mov_movement_ind) over (partition by m.snz_uid order by m.[dol_mov_carrier_datetime] asc, [dol_mov_posted_datetime] desc) <> m.dol_mov_movement_ind 
				then 1 
				else 0 
			end as retain_flag
		from $(idicleanversion).[dol_clean].[movements] m 
		where m.dol_mov_carrier_date >= cast('1990-01-01' as date) /* Cut-off date for good quality movements data*/
	)x
	where retain_flag = 1 /* Delete "incorrect" movement records. */
)
/* 3. Remove first movement if it is a departure from NZ */
,entry_exit_moves as (
	select *
	from (
		select
			*
			/* Add a sequence number to each successive move */
			,row_number() over (partition by snz_uid order by carrier_datetime asc, posted_datetime desc) as rn
		from movements_clean
	)moves
	where not(rn  = 1 and movement_indicator = 'D') 
	/* Removes the first movement of a person if it is a departure from NZ. For people with only departure, they will be completely removed.*/
)
/* 4. Convert into arrival-departure pairs. Every record is an arrival and its paired departure. If departure has not happened, then this will be null.*/
select 
	snz_uid
	,identity_code
	,permit_type_code
	,permit_date_issued
	,permit_date_expiry
	,arrival_date
	,coalesce(depart_date,'9999-12-30') as depart_date
	/* Calculate duration of spell based on arrival and departure dates. If there is no departure, use IDI refresh date as the cutoff date */
	,datediff(dd, arrival_date, coalesce(depart_date, (select top 1 cutoff_date from cutoff_date) )) as spell_duration
	,datediff(dd, coalesce(depart_date, (select top 1 cutoff_date from cutoff_date)), coalesce(next_arrival_date, (select top 1 cutoff_date from cutoff_date))) as days_out
into [IDI_Sandpit].$(targetschema).$(projprefix)_spell_moves
from (
	/* Join each pair of arrival and departure, null departure if there are still in NZ. */
	select 
		arrival.snz_uid
		,arrival.identity_code
		,arrival.permit_type_code
		,arrival.permit_date_issued
		,arrival.permit_date_expiry
		,arrival.carrier_datetime as arrival_date
		,departure.carrier_datetime as depart_date
		,lead(arrival.carrier_datetime) over (partition by arrival.snz_uid order by arrival.carrier_datetime) as next_arrival_date
	from 
	(select * from entry_exit_moves where movement_indicator = 'A') arrival
	left join (select * from entry_exit_moves where movement_indicator = 'D') departure
		on (arrival.snz_uid= departure.snz_uid and arrival.rn + 1 = departure.rn)
	where arrival.carrier_datetime <= coalesce(departure.carrier_datetime, '9999-12-30') 
)x;



/* Delete the database object if it already exists */
drop table if exists $(targetschema).$(projprefix)_nonoverlap_visa;


/* Next, we create visa spells to identify time periods an individual was allocated a specific visa for a legally valid stay in NZ. */
/* 1. Pick approved visas from visa decisions, and obtain visa details.*/
with double4 as (
	select
		a.snz_uid as snz_uid
		,a.snz_application_uid as Application_number
		,a.snz_dol_uid as identity_code
		,a.dol_dec_decision_date as decision_date
		,b.decision_date_entered
		,a.dol_dec_completed_date as completed_date
		,application_grounds_code
		,Visa_Type as Visa_Type
		,application_category as application_category
		,application_type as application_type
		,a.dol_dec_application_criteria_text as application_criteria
		,a.dol_dec_nationality_code as nationality
		,a.dol_dec_sex_snz_code as gender
		,startdate as V_Startdate
		,enddate as V_Enddate
		,months_allowed
		,ExpiryDateTravel
		,ExpiryDateStay
		,a.dol_dec_visa_expiry_date as DatePermitExpiry
		,DatePermitIssue
		,datevisacreated
		,case 
			when visa_type = 'Permanent Resident' then 1
			when visa_type = 'Resident' then 2
			when visa_type = 'Visitor' then 4
			else 3 
		end as visa_priority
	/* remove duplicates on person-application ID combination.*/
	from (select * from 
			(select
				*
				,row_number() over (partition by snz_dol_uid, snz_application_uid order by dol_dec_decision_date desc, dol_dec_completed_date desc, dol_dec_application_criteria_text,dol_dec_nationality_code, dol_dec_applicant_type_code, dol_dec_visa_expiry_date, dol_dec_birth_year_nbr) as rn
			from $(idicleanversion).dol_clean.decisions
			where dol_dec_decision_type_code = 'A' /* Only pick approved visa records - because these form the basis of migration spells.*/
				and dol_dec_application_criteria_text not in ('CONFIRMATION', 'SPONSOR PROCESS', 'TRANSIT')
			)x
			where rn = 1) a 
	inner join (select * from 
				(select
					*
					,row_number() over (partition by snz_dol_uid, snz_application_uid order by decision_date_entered desc, datevisacreated desc, application_grounds_code, Visa_Type, application_type, startdate, enddate, months_allowed, ExpiryDateStay, DatePermitIssue ) as rn
				from $(idicleanversion).dol_clean.immidata_visas
				where application_category in ('VISA', 'GROUP VISA', 'PERMIT')/*application_category & [dol_dec_application_cat_code] should have the same values in theory, but a very small number may have one or the other as NULL.	We use immidata_visas as the master for application category information, based on subject matter expert's advice..*/
				)x
				where rn = 1) b 
	on (a.snz_application_uid=b.snz_application_uid 
		and a.snz_dol_uid=b.snz_dol_uid)
)
/* 2. Identify visa spell records to be removed based on the following business rules (documented earlier). */
,redundant_visas as (
	select  snz_uid, Application_number  
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.application_category = 'Permit')
		and a.application_category = 'Visa'
	union all
	select snz_uid, Application_number 
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.application_criteria <> 'Section 61')
		and a.application_criteria = 'Section 61'
	union all
	select snz_uid, Application_number 
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.visa_type in ('Work', 'Resident', 'Permanent Resident'))
		and a.visa_type = 'Visitor'
	union all
	select snz_uid, Application_number  
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.visa_type in ('Resident', 'Permanent Resident'))
		and a.visa_type = 'Work'
	union all
	select snz_uid, Application_number  
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.application_criteria = 'Grant of Second or Subsequent Resident Visa')
		and a.application_type = 'Returning Resident''s'
	union all 
	select snz_uid, Application_number 
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.visa_type <> 'Interim')
		and a.visa_type = 'Interim'
	union all 
	select snz_uid, Application_number 
	from double4 a
	where exists (select 1 from double4 b where  a.snz_uid = b.snz_uid and a.V_Startdate = b.V_Startdate and b.application_criteria <> 'Crew of foreign fishing vessel Archive')
		and a.application_criteria = 'Crew of foreign fishing vessel Archive'
	union all 
	/* Remove visa records for an individual where a later record exists with the same visa type, later decision completed & entered dates, but where the visa start date of the current record is greater than decision entered date
	of subsequent records. This is likely because the subsequent visa records make the current one redundant.*/
	select 
		a.snz_uid, a.Application_number
	from double4 a
	inner join double4 b 
		on (a.snz_uid = b.snz_uid and a.application_number <> b.application_number and a.visa_type = b.visa_type and a.completed_date <= b.completed_date and a.decision_date_entered <= b.decision_date_entered and a.v_startdate > b.decision_date_entered)
	union all
	/* if there are later records for an individual where the visa end date is the same (and not an "infinite" end date) and the application grounds are the same, then treat the later records as superseding the current record and
		mark the current record as redundant. Note this may make it look like the person has no visa record in the interim*/
	select 
		a.snz_uid, a.Application_number
	from double4 a
	inner join double4 b on (a.snz_uid = b.snz_uid and a.application_number <> b.application_number and a.v_enddate = b.v_enddate and a.v_Enddate < '9999-12-30' and a.application_grounds_code = b.application_grounds_code 
		and a.completed_date <= b.completed_date and a.decision_date_entered <= b.decision_date_entered and a.decision_date <= b.decision_date and a.V_Startdate <= b.V_Startdate)
)
/* 3. Remove redundant visa spells re-engineer start and end dates based on previous and subsequent spells*/
,overlap_visa2 as (
	select
		snz_uid
		,Application_number
		,identity_code
		,decision_date
		,decision_date_entered
		,completed_date
		,application_grounds_code
		,Visa_Type
		,case when Visa_Type = 'Permanent Resident' then 'Resident' else Visa_Type end as visa_type_temp
		,application_category
		,application_type
		,application_criteria
		,nationality
		,gender
		,months_allowed
		,ExpiryDateTravel
		,ExpiryDateStay
		,DatePermitExpiry
		,DatePermitIssue
		,datevisacreated
		, visa_priority
		,case 
			when V_startdate >= lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) 
				and lead(v_enddate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) is not null			
			then dateadd(dd, -1, lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) )
			else V_startdate 
		end as V_startdate
		,case 
			when V_enddate > lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) 
				and V_startdate <= lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) 
				and lead(v_enddate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) is not null
			then dateadd(dd, -1, lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) )
			else V_enddate 
		end as V_enddate
	from (		
		select 
			snz_uid
			,Application_number
			,identity_code
			,decision_date
			,decision_date_entered
			,completed_date
			,application_grounds_code
			,Visa_Type
			,application_category
			,application_type
			,application_criteria
			,nationality
			,gender
			,months_allowed
			,ExpiryDateTravel
			,ExpiryDateStay
			,DatePermitExpiry
			,DatePermitIssue
			,datevisacreated
			,visa_priority
			,V_Startdate
			/* If there is no end date for a specific visa, and a subsequent visa record exists, pick the (start date - 1 day) of the next record as the end date for the current record. This is because the next 
				visa record supersedes the current one from its start date. Although, is this true for all cases? What if the next visa has a lower priority rank than the current one?					
				*/
			,coalesce(v_enddate, dateadd(dd, -1, lead(v_startdate) over (partition by snz_uid order by completed_date, decision_date_entered, decision_date, v_startdate, v_enddate, application_number))) as v_enddate	
		from double4 a
		where not exists (select 1 from redundant_visas b where a.snz_uid = b.snz_uid and a.application_number = b.application_number)
	)y
)
,nonoverlap_visa as (
	select 
		snz_uid
		,Application_number
		,identity_code
		,decision_date
		,decision_date_entered
		,completed_date
		,application_grounds_code
		,Visa_Type
		,visa_type_temp
		,application_category
		,application_type
		,application_criteria
		,nationality
		,gender
		,months_allowed
		,ExpiryDateTravel
		,ExpiryDateStay
		,DatePermitExpiry
		,DatePermitIssue
		,datevisacreated
		,visa_priority
		,V_Startdate
		,case 
			when V_enddate > lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate)
				and lead(v_enddate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) is not null		
			then dateadd(dd, -1, lead(V_Startdate) over (partition by snz_uid order by completed_date, decision_date_entered, V_Enddate, visa_priority desc, application_number, V_Startdate) )
			else V_enddate
		end as V_enddate
		,row_number() over (partition by snz_uid order by completed_date, decision_date_entered, V_startdate, V_enddate, application_number) as rn
	from overlap_visa2
)
select 
	*
into IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa
from nonoverlap_visa;

create unique nonclustered index nc_nonoverlap_visa on IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa(snz_uid, rn);
create unique nonclustered index nc_nonoverlap_visa_2 on IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa(snz_uid, application_number);
create unique nonclustered index nc_nonoverlap_visa_3 on IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa(snz_uid, Visa_Type_temp, rn);


/*4. Re-allocate visa grounds for INTERIM and REPLACEMENT type visa records by looking back at the original grounds under which an individual was granted a visa;
	and use these as the actual grounds for being granted an interim/replacement. Also obtain the original application number from which these visa grounds are 
	inherited.
*/

/* Delete the database object if it already exists */
drop table if exists $(targetschema).$(projprefix)_spell_visas;

with groundscodes as (
	select 'INT' as codetype, Grounds_code from $(idimetadataversion).[mbie_dol].[grounds_code] where Spells_Stream like '%Interim%'
	union all
	select distinct 'RPL' as code_type, Grounds_Code
	from $(idimetadataversion).[mbie_dol].[grounds_code]
	where (Application_criteria like '%Replace%' or
			Application_criteria like '%Conditions%' or
			Application_criteria like '%Vary conditions%' or
			Application_criteria like '%Replacement%' or 
			Application_criteria like '%VOC%' or
			Application_criteria like '%Returning Resident%' or
			Application_criteria like '%RRV%' or
			Application_criteria like	'%Second or Subsequent Resident Visa%' or
			/*one-off hardcoded for permanent resident/returning residents and conversion visa type (don't have solution at the moment)*/
			Grounds_Code in ('ABEBPA','ABEBPB','ABEBPC','BBBBBX','ABEBPY','CBBBBN','ZZHZDA','ZZIZDA','ABBAGM')
			)
			and (
			Application_criteria not like '%Special%' and 
					Application_criteria not like '%Vocational%' and 
					Application_criteria not like '%Revocation%' 
			)
)
select
	z.snz_uid
	,Application_number
	,identity_code
	,decision_date
	,decision_date_entered
	,completed_date
	,application_grounds_code
	,case 
		when application_criteria in ('Replace', 'Label less Replace', 'Replacement Visa') and new_grounds is null and visa_type = 'Visitor' then 'AAAAAA'
		when application_criteria in ('Replace', 'Label less Replace', 'Replacement Visa') and new_grounds is null and application_type = 'Returning Resident''s' then 'ABDBDA'
		else new_grounds
	end as new_grounds
	,new_spell_app_no
	,Visa_Type
	,application_category
	,application_type
	,application_criteria
	,nationality
	,gender
	,months_allowed
	,ExpiryDateTravel
	,ExpiryDateStay
	,DatePermitExpiry
	,DatePermitIssue
	,datevisacreated
	,visa_priority
	,V_Startdate
	,V_enddate
into [IDI_Sandpit].$(targetschema).$(projprefix)_spell_visas
from (
	select 
		y.*
		/* If new grounds is a "Replacement" type, then use new_grounds_b. else use new_grounds_a as the new grounds type.*/
		,case when new_grounds_a is null or new_grounds_a in (select Grounds_code from groundscodes where codetype = 'RPL') then coalesce(new_grounds_b, new_grounds_a) else new_grounds_a end as new_grounds
		,case when new_grounds_a is null or new_grounds_a in (select Grounds_code from groundscodes where codetype = 'RPL') then coalesce(new_spell_app_no_b, new_spell_app_no_a)  else new_spell_app_no_a end as new_spell_app_no
	from (
		
		select 
			a.*			
			/* If application grounds for the current visa record is an "Interim" or a "Replacement" type record, then use the 'application ground' with the grounds of closest previous visa record,
				that has the same visa type but the application ground not being another replacement or interim type. We fix this look-back to the previous ■■■■■■■■■■, and not any further.*/
			,case 
				when a.application_grounds_code in (select Grounds_code from groundscodes) 
				then
					(select top 1 b.application_grounds_code 
					from IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa b 
					where a.snz_uid = b.snz_uid
						and a.Visa_Type_temp = b.Visa_Type_temp
						and a.rn > b.rn and b.rn >= a.rn - 10
						and b.application_grounds_code not in (select Grounds_code from groundscodes)
					order by rn desc
					)
			end as new_grounds_b
			/* As before, if the application grounds for the current record was changed, then obtian the application number of the record which was used to edit the application ground of the current 
				record.*/
			,case 
				when a.application_grounds_code in (select Grounds_code from groundscodes) 
				then
					(select top 1 b.Application_number 
					from IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa b 
					where a.snz_uid = b.snz_uid
						and a.Visa_Type_temp = b.Visa_Type_temp
						and a.rn > b.rn and b.rn >= a.rn - 10
						and b.application_grounds_code not in (select Grounds_code from groundscodes)
					order by rn desc
					)
			end as new_spell_app_no_b
			/* If application grounds for the current visa record is an "Interim" record, then use the 'application ground' with the grounds of closest previous visa record,
				regardless of visa type such that the previous record does not have a replacement or interim application ground. We fix this look-back to the previous ■■■■■■■■■ only.
				If none were found, use 'AAAAAA' as the default application ground*/
			,case 
				when a.application_grounds_code in (select Grounds_code from groundscodes where codetype = 'INT')
				then 
					coalesce(
						(select top 1 c.application_grounds_code 
							from IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa c
							where a.snz_uid = c.snz_uid
								and a.rn > c.rn and c.rn >= a.rn - 4
								and c.application_grounds_code not in (select Grounds_code from groundscodes)
							order by rn desc
							)
						,'AAAAAA')
				else NULL 
			end	as new_grounds_a
			/* As before, if the application grounds for the current record was changed, then obtain the application number of the record which was used to edit the application ground of the current 
				record.*/
			,case 
				when a.application_grounds_code in (select Grounds_code from groundscodes where codetype = 'INT')
				then 					
					(select top 1 c.Application_number 
						from IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa c
						where a.snz_uid = c.snz_uid
							and a.rn > c.rn and c.rn >= a.rn - 4
							and c.application_grounds_code not in (select Grounds_code from groundscodes)
						order by rn desc
						)
				else NULL 
			end	as new_spell_app_no_a
		from IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa a
	)y
)z
;

create unique nonclustered index idx_spell_visas on [IDI_Sandpit].$(targetschema).$(projprefix)_spell_visas([snz_uid],[Application_number]);

/* Delete the nonoverlap_visa table as this object is no longer useful */
drop table if exists IDI_Sandpit.$(targetschema).$(projprefix)_nonoverlap_visa;

/*********** Combine movements and visa spells *************/

/* Delete the database object if it already exists */
drop table if exists IDI_Sandpit.$(targetschema).$(projprefix)_migration_spells;

/* Only those who have a movement and a visa record will figure in the output here. This means all movements with individuals not associated with any visas are filtered out.*/
with cutoff_date as (
	select top 1 cast(create_date as date) as cutoff_date from $(idicleanversion).sys.tables where name = 'movements'
)
/* 1. Combine and match movements and approved visas.*/
,move_visa_combine as (
	select 
		visas.*
		,moves.permit_date_issued, moves.permit_date_expiry, moves.arrival_date, moves.depart_date, moves.spell_duration, moves.days_out
		/* The following code snippet may be useful some day for validations - hence retaining this for now.*/
		/*case when expirydatetravel < cast(arrival_date as date) or expirydatestay < cast(arrival_date as date) or (V_enddate is null and expirydatestay is null) then 1 else 0 end as deleteflag
		,case when cast(arrival_date as date) between v_startdate and v_enddate then 0 else 1 end as flag_c
		,case when cast(moves.depart_date as date) between v_startdate and v_enddate then 0 else 1 end as flag_d
		,case when cast(moves.depart_date as date) between v_startdate and ExpiryDateStay then 0 else 1 end as flag_e
		,rank(Arrival_time) over (partition by snz_uid order by Arrival_time, V_startdate, V_enddate) as rnk*/
		,lag(arrival_date) over (partition by moves.snz_uid order by arrival_date, V_startdate, V_enddate, application_number) as earlier_arrival_date
	from [IDI_Sandpit].$(targetschema).$(projprefix)_spell_moves moves
	inner join [IDI_Sandpit].$(targetschema).$(projprefix)_spell_visas visas 
		on (moves.snz_uid = visas.snz_uid
			and visas.decision_date <= cast(moves.depart_date as date)
			and (v_startdate <= cast(moves.depart_date as date) or v_startdate is null) /* If V_startdate is null, then we hope the decision_date condition will work in lieu of this (which is already accounted for in an earlier join).*/
			and (v_enddate >= cast(arrival_date as date) or v_enddate is null)
			and not(
				case when (expirydatetravel < cast(arrival_date as date) or expirydatestay < cast(arrival_date as date) or (V_enddate is null and expirydatestay is null)) then 1 else 0 end = 1
				and case when cast(arrival_date as date) between v_startdate and v_enddate then 1 else 0 end = 0
				and case when cast(moves.depart_date as date) between v_startdate and v_enddate then 1 else 0 end = 0
				and case when cast(moves.depart_date as date) between v_startdate and ExpiryDateStay then 1 else 0 end = 0
			)
		)
)
,migration_spells as (
	select
		snz_uid
		,identity_code
		,Application_number
		,spell_start
		/* Truncate overlapping spells by looking at the start date of next spell and limiting current spell to 1 day before start of next spell*/
		,case
			when lead(spell_start) over (partition by snz_uid order by arrival_date, v_startdate, v_enddate, spell_start, spell_end, application_number) < spell_end 
			then dateadd(dd, -1, lead(spell_start) over (partition by snz_uid order by arrival_date, v_startdate, v_enddate, spell_start, spell_end, application_number))
			else spell_end
		end as spell_end
		,arrival_date
		,depart_date
		,permit_date_issued
		,permit_date_expiry
		,spell_duration
		,days_out
		,V_Startdate
		,V_enddate
		,DatePermitIssue
		,DatePermitExpiry		
		,ExpiryDateTravel
		,ExpiryDateStay
		,decision_date
		,decision_date_entered
		,completed_date
		,application_grounds_code
		,new_grounds
		,new_spell_app_no
		,Visa_Type
		,application_category
		,application_type
		,application_criteria
		,nationality
		,gender
		,months_allowed		
		,datevisacreated
		,visa_priority		
	from (
		select * 
			,case 
				/* Construct spell start and end dates based on visa start/end and arrival/departure dates.*/
				when earlier_arrival_date = arrival_date and v_startdate between arrival_date and (select cutoff_date from cutoff_date) then v_startdate
				when earlier_arrival_date = arrival_date and v_startdate > (select cutoff_date from cutoff_date) then decision_date_entered
				when earlier_arrival_date <  arrival_date then arrival_date
				else arrival_date
			end as spell_start
			,case 
				when v_enddate >= (select cutoff_date from cutoff_date) then depart_date
				when depart_date >= V_enddate then V_enddate
				else depart_date
			end as spell_end
		from move_visa_combine
	) spell
)
/* 2. Truncate spells where people may have passed away during a spell. Remove any spells that may happen after a person's death (assuming this is a data quality issue).*/
,live_migration_spells as (
	select 
		spell.snz_uid
		,identity_code
		,Application_number
		,spell_start
		/* For individuals who passed away during their spell, set the proxy date of death as the spell end.*/
		,case
			when dod_proxy between spell_start and spell_end then dod_proxy
			else spell_end
		end as spell_end
		,arrival_date
		,depart_date
		,permit_date_issued
		,permit_date_expiry
		,spell_duration
		,days_out
		,V_Startdate
		,V_enddate
		,DatePermitIssue
		,DatePermitExpiry		
		,ExpiryDateTravel
		,ExpiryDateStay
		,decision_date
		,decision_date_entered
		,completed_date
		,application_grounds_code
		,new_grounds
		,new_spell_app_no
		,Visa_Type
		,application_category
		,application_type
		,application_criteria
		,nationality
		,gender
		,months_allowed		
		,datevisacreated
		,visa_priority	
		,case
			when dod_proxy between spell_start and spell_end then 1
			else 0 
		end as death_flag
	from migration_spells spell	
	left join (
				select snz_uid, max(eomonth(datefromparts(dia_dth_death_year_nbr, dia_dth_death_month_nbr,15))) as dod_proxy 
				from $(idicleanversion).dia_clean.deaths 
				where dia_dth_death_year_nbr >= 1990
				group by snz_uid) dth 
		on (spell.snz_uid = dth.snz_uid 
			and dod_proxy <= spell_start
			)
)
/* 3. Construct unlawful spells wherever this is available */
,unlawful_spells as (
	select distinct 
		snz_uid as snz_uid
		,snz_dol_uid as identity_code
		,current_client_status
		,became_unli_on
		,coalesce(stopped_being_unli_on, '9999-12-30') as stopped_being_unli_on
	from $(idicleanversion).[dol_clean].[unli_history] unli
	where became_UNLI_on >= '1990-01-01' 
		and current_Client_status = 'UNLI'
)
/* 4. Combine lawful and unlawful spells and adjust spell start and end dates accordingly*/
,combined_spells as (
	select 
		snz_uid
		,identity_code
		,Application_number
		,case
			when current_Client_status = 'UNLI' then 
				case when lag(spell_end) over (partition by snz_uid order by spell_start, spell_end, APPLICATION_NUMBER) = spell_start then dateadd(dd, 1, spell_start) else spell_start end 
			else spell_start 
		end as spell_start
		/* If current record is Unlawful stay, then if the next record's spell start = current spell end, then subtract 1 day*/
		,case
			when current_Client_status = 'UNLI' and lead(spell_start) over (partition by snz_uid order by spell_start, spell_end, APPLICATION_NUMBER) = spell_end 
			then dateadd(dd, -1, spell_end)
			when --current_Client_status <>  'UNLI' and 
				lead(current_Client_status) over (partition by snz_uid order by spell_start, spell_end, APPLICATION_NUMBER) = 'UNLI' and spell_end = '9999-12-30'
			then dateadd(dd, -1, lead(spell_start) over (partition by snz_uid order by spell_start, spell_end, APPLICATION_NUMBER))
			else spell_end
		end as spell_end
		,arrival_date
		,depart_date
		,permit_date_issued
		,permit_date_expiry
		,spell_duration
		,days_out
		,V_Startdate
		,V_enddate
		,DatePermitIssue
		,DatePermitExpiry		
		,ExpiryDateTravel
		,ExpiryDateStay
		,decision_date
		,decision_date_entered
		,completed_date
		,application_grounds_code
		,case 
			when current_client_status = 'UNLI' then 'UNLAWF'
			when visa_type = 'Visitor' and application_grounds_code is null then 'AAAAAA'
			when visa_type = 'Work' and application_grounds_code is null then 'WWWWWW'
			when visa_type = 'Resident' and application_grounds_code is null then 'RRRRRR'
			when new_grounds is NULL then application_grounds_code
			else new_grounds
		end as spell_grounds_code
		,new_spell_app_no
		,Visa_Type
		,application_category
		,application_type
		,application_criteria
		,nationality
		,gender
		,months_allowed		
		,datevisacreated
		,visa_priority
		,current_client_status
	from 
		/* Create a simple union of lawful and unlawful spells*/
		(
		select 
			snz_uid
			,identity_code
			,Application_number
			,cast(spell_start as date) as spell_start
			,cast(spell_end as date) as spell_end
			,arrival_date
			,depart_date
			,permit_date_issued
			,permit_date_expiry
			,spell_duration
			,days_out
			,V_Startdate
			,V_enddate
			,DatePermitIssue
			,DatePermitExpiry		
			,ExpiryDateTravel
			,ExpiryDateStay
			,decision_date
			,decision_date_entered
			,completed_date
			,application_grounds_code
			,new_grounds
			,new_spell_app_no
			,Visa_Type
			,application_category
			,application_type
			,application_criteria
			,nationality
			,gender
			,months_allowed		
			,datevisacreated
			,visa_priority
			,NULL as current_client_status
		from migration_spells
		union all 
		select 
			snz_uid
			,identity_code
			,NULL as Application_number
			,cast(became_unli_on as date) as spell_start
			,cast(stopped_being_unli_on as date) as spell_end
			,NULL as arrival_date
			,NULL as depart_date
			,NULL as permit_date_issued
			,NULL as permit_date_expiry
			,NULL as spell_duration
			,NULL as days_out
			,NULL as V_Startdate
			,NULL as V_enddate
			,NULL as DatePermitIssue
			,NULL as DatePermitExpiry		
			,NULL as ExpiryDateTravel
			,NULL as ExpiryDateStay
			,NULL as decision_date
			,NULL as decision_date_entered
			,NULL as completed_date
			,NULL as application_grounds_code
			,NULL as new_grounds
			,NULL as new_spell_app_no
			,NULL as Visa_Type
			,NULL as application_category
			,NULL as application_type
			,NULL as application_criteria
			,NULL as nationality
			,NULL as gender
			,NULL as months_allowed		
			,NULL as datevisacreated
			,NULL as visa_priority
			,current_client_status
		from unlawful_spells
		)x
)
/* 5. Identify first residence date for a person. This will become useful to identify recent residents and match with MBIE's published immigration counts.*/
,first_residence as (
	select a.snz_uid
		, min(coalesce(datevisacreated, case when v_startdate > spell_start then v_startdate else spell_start end)) as first_res_date
	from combined_spells a
	inner join $(idimetadataversion).[mbie_dol].[grounds_code] b 
		on (a.spell_grounds_code = b.Grounds_code)
	where b.visa_type IN ('Resident', 'Returning Resident')
	group by a.snz_uid
)
/* 6. Bring everything together.*/
select 
	z.snz_uid
	,identity_code
	,Application_number
	,spell_start
	,spell_end
	,arrival_date
	,depart_date
	,datediff(dd, spell_start, 
		case when spell_end is null or spell_end in ('9999-12-30', '9999-12-31') then  (select cutoff_date from cutoff_date)
			else spell_end end
	) as spell_duration
	,days_out as days_out_after_dept
	,case when spell_end in ('9999-12-30', '9999-12-31') then 0
		when lead(spell_start) over (partition by z.snz_uid order by spell_start, spell_end) is null then datediff(dd, spell_end, (select cutoff_date from cutoff_date))
		when spell_start = lead(spell_start) over (partition by z.snz_uid order by spell_start, spell_end) then 0
		else datediff(dd, spell_end, lead(spell_start) over (partition by z.snz_uid order by spell_start, spell_end)) - 1
		end as days_out_after_spell
	,V_Startdate
	,V_enddate
	,application_grounds_code
	,spell_grounds_code
	,coalesce(new_spell_app_no, Application_number) as spell_app_number
	,gc.Visa_type
	,gc.Application_substream
	,gc.Application_criteria
	,application_category
	,application_type
	,fr.first_res_date
	,occ.dol_occ_occ_code as occupation_code
	,occ.dol_occ_occ_standard_code as occupation_standard
	,occ.dol_occ_occ_text as occupation_name
	,occ.dol_occ_priority_ind as occupation_priority
	,occ.dol_occ_skill_level_code as occupation_skill_level_code
into IDI_Sandpit.$(targetschema).$(projprefix)_migration_spells
from (
	select *
		,case 
			when current_Client_status = 'UNLI' 
				and lead(spell_start) over (partition by snz_uid order by spell_start, spell_end) <= spell_start 
				and lead(spell_end) over (partition by snz_uid order by spell_start, spell_end) >= spell_end
			then 1 
			else 0 end as delete_flag
	from combined_spells		
	)z
left join $(idimetadataversion).[mbie_dol].[grounds_code] gc on (z.spell_grounds_code = gc.Grounds_code)
left join first_residence fr on (z.snz_uid = fr.snz_uid)
left join $(idicleanversion).[dol_clean].[occupations] occ on (z.snz_uid = occ.snz_uid and z.Application_number = occ.snz_application_uid)
where delete_flag = 0