Module output

Main benefits
SQL: [IDI_Community].MSD_ISE_MAIN_BENEFIT.msd_ise_main_benefit_YYYYMM
SAS: libname cmmsdmb ODBC dsn=idi_community_srvprd schema=cm_read_MSD_ISE_MAIN_BENEFIT;

Supplementary benefits
SQL: [IDI_Community].MSD_ISE_SUPPLEMENTARY.msd_ise_supplementary_YYYYMM
SAS: libname cmmsdis ODBC dsn=idi_community_srvprd schema=cm_read_MSD_ISE_SUPPLEMENTARY;

Ad-hoc benefits
SQL: [IDI_Community].MSD_ISE_AD_HOC.msd_ise_ad_hoc_YYYYMM
SAS: libname cmmsdah ODBC dsn=idi_community_srvprd schema=cm_read_MSD_ISE_AD_HOC;

Tax credit benefits
SQL: [IDI_Community].MSD_ISE_TAX_CREDIT.msd_ise_tax_credit_YYYYMM
SAS: libname cmmsdtc ODBC dsn=idi_community_srvprd schema=cm_read_MSD_ISE_TAX_CREDIT;

Retirement benefits
SQL: [IDI_Community].MSD_ISE_RETIREMENT_BENEFIT.msd_ise_retirement_benefit_YYYYMM
SAS: libname cmmsdrb ODBC dsn=idi_community_srvprd schema=cm_read_MSD_ISE_RETIREMENT_BENEFIT;

How to access a code module in the Data Lab : Read here

MSD Income Support Payments

Purpose of the Income Support Payments Module

Income support payment spells are administered by the Ministry of Social Development and includes main benefits, supplementary benefits and ad hoc payments.

Key Concepts

New Zealand income support system

Income Support refers to financial transfer programmes designed to reduce poverty among eligible groups within society. In general, New Zealand has an entitlement-based income support system funded through general taxation (ie not a social insurance-based model). This means that, if a person meets the entitlement criteria, they continue to receive income support indefinitely. Income Support programmes can be divided into five types:

retirement: pensions paid to people over qualifying age
main benefits: designed to provide the main income for people whose circumstances make it difficult for them to earn income from employment
supplementary benefits: additional regular top up payments for low-income families (with the exception of the child disability payment that is not means tested)
ad hoc payments: one off payments for incidental costs for low-income families, which are either a grant or a loan
tax credits: payments to low-income families with children designed to provide additional financial support and encourage movement into employment (ie make work pay).

In addition, New Zealand also operates two additional income transfer programmes:

accident compensation: ACC is a public accident insurance scheme that covers everyone for medical costs and loss of income for reason of accident and is funded through employer/employee levies as well as levies on motor vehicles.
study assistance: allowances and grants provided to low-income families to help cover living costs while studying at the tertiary level.

Accident compensation, tax credits and study assistance are not covered in this module. Instead, this code module provides payment information on the first four of these programmes, namely: retirement, main benefits, supplementary benefits and ad hoc payments.

Retirement pensions

Retirement pensions are designed to assist older people when they stop working. Currently, retirement pensions are not means tested, but are taxed. This means everybody eligible of these pensions receive them regardless of their other sources of income. The main types of retirement pensions are:

New Zealand Superannuation (NZ Super): For people who are resident in New Zealand for at least 10 years between the age of 50 and 65 are entitled to Superannuation on reaching the qualifying age (currently set at 65). NZ Super is assessed as a family unit, where couples are paid at a lower per person rate than single people. The rate of NZ Super is adjusted each year so the couple rate is at 66% of the average wage.
Veteran’s Pension is paid to disabled veterans aged 65 years or over who have served in a war or other emergency. The Veteran’s Pension is paid at the same rate and has most of the same rules as NZ Super, with some extra benefits for veterans and their partner if they have one. People receiving Veteran’s Pension and are assessed as having a disablement rating of 52% or more, they may be entitled to extra support including:
- automatic entitlement to a SuperGold Card/Community Services Card
- a lump sum payment on the death of the Veteran or their partner
- Veteran’s Pension payments are not reduced if the person needs long-term hospital care.

Main benefits

Main benefits, also referred to as first tier assistance, are taxable payments for working age people who are not in employment and are in circumstances that means they cannot work full time.

Entitlement to main benefit is based on the circumstances of the family. For example, if one member of a couple has income over a certain threshold, then the family will not be entitled to income support. Like pensions, couple families receive a lower per person rate than single families in the same circumstances. But unlike pensions, until April 2020 income support payment rates were adjusted each year in line with inflation (based on the CPI index) rather than being pegged to the average wage. From April 2020 onward, these rates are indexed to average wage growth. Without one-off adjustments, this has meant an increase in the gap between main benefit payments and employment income over the last 25 years.

The specific types of main benefits have changed over the last two decades. In particular, the welfare reforms in July 2013 resulted in ending of three benefits (Sickness, Widow’s and Domestic Purposes Benefit for Women Alone) as well as substantial changes in the names, obligations and eligibility criteria of all main benefits.

Main benefits can be grouped into the following types (* indicates the specific benefit is no longer current):

Unemployment [Unemployment Benefit*, Jobseeker Support - Work Ready]: are paid to people currently out of work but are expected to seek full time employment.
Carer [Domestic Purposes - Sole Parent*, Sole Parent Support, Domestic Purposes - Caring for Sick and Infirm*, Supported Living Payment - Caring for Sick and Infirm, Emergency Maintenance Allowance]: paid to people with caring responsibilities that prevent them from working full time. The largest group in this category are sole-parents, followed by those caring for an ill family member. Note, in July 2013, sole parents whose youngest child was over 14 are not entitled to a carer benefit and are instead on the unemployment related benefit (Job Seeker Support). Emergency Maintenance Allowance is paid to sole parents in need, but who do not meet the qualifying criteria, for the main carer benefits.
Health and Disability [Invalid’s*, Sickness*, Supported Living Payment, Job Seeker Support Health Condition or Disability]: these benefits are paid where a person is unable to work because of illness. Sickness benefits were paid in cases of temporary ill-health, whilst Invalid’s/Support Living Payment are for people with long term illness or disability. Before July 2013, Sickness benefit was paid for temporary periods of ill health, after July 2013 the Sickness benefit ceased as a benefit type. People with temporary ill health are paid an unemployment benefit (Job Seeker Support) with suspended work obligations (referred to as Job Seeker Support Health Condition or Disability).
Youth [Independent Youth Benefit*, Youth Payment, Young Parent Payment]: these benefits are paid to people between the ages of 15 and 17 who are not being supported by their caregivers (including children aging out of State care). The Youth Payment replaced the Independent Youth Benefit in September 2012. The Young Parent Payment was introduced at the same time. The Young Parent Payment differs from the Youth Payment in that eligibility extends up to the age of 19. It is also important to note that the Young Parent Payment is not a sole parent benefit, with people receiving the Young Parent Payment can have a partner on benefit.
Women alone [Widow’s benefit*, Domestic Purposes - Woman Alone*]: Before July 2013 the New Zealand income support system provided assistance to widows (but not widowers) as well as older single women (over 55) who had no employment experience.
Emergency Benefit: assistance that may be paid to people who cannot support themselves and who do not qualify for any other payments. Similar to Emergency Maintenance Allowance, Emergency Benefit is a discretionary benefit to cover circumstances need that are not covered by existing entitlements.

Supplementary benefits

Supplementary benefits, also called second tier assistance, are regular non-taxable payments designed to provide additional support to low-income families. Like main benefits, the payment rates and entitlement thresholds are all adjusted in line with inflation. The main types of supplementary payments include:

Accommodation assistance [Accommodation Supplement]: a weekly payment which helps people with their rent, board or the cost of owning a home. The Accommodation Supplement is the lower of either 70% of accommodation costs or a set maximum. The maximum amount a family can receive varies according to where they live. Areas with higher accommodation costs (eg main centres such as Auckland) have higher maximums.
Disability Support [Disability Allowance] Disability Allowance is a means tested benefit to pay for the additional costs from a disability. These additional costs need to be verified by the person’s doctor. In addition, there is a Special Disability Allowance to cover the costs of travel for people on pension or main benefit to visit their partner in hospital (expected stay of at least 13 weeks) or getting the Residential Care Subsidy.
Support for children: there exist a range of supplementary assistance for supporting children.
- Childcare Subsidy: is an income tested payment to help families with the cost of pre-school childcare and covers situations where the child is not eligible to the 20 hours ECE subsidy. The Childcare Subsidy covers 9 hours of child care a week, but can go up to 50 hours where parents are working, in training or receiving health treatment.
- Out of School Care and Recreation (OSCAR) Subsidy: is a payment which helps families with the costs of before- and after-school programmes, and school holiday programmes. The OSCAR subsidy covers costs up to 20 hours a week, and up to 50 hours a week for school holiday programmes.
- Child Disability Allowance: is payment made to the main carer of a child or young person with a serious disability. It is paid in recognition of the extra care and attention needed for that child. The Child Disability Allowance is not means tested.
- Away From Home Allowance: provides assistance with accommodation costs for the caregivers of dependent 16- and 17-year-olds who move away from home to undertake tertiary study or employment related training. The allowance is means tested based on receiving a main benefit or entitled to Family Tax Credit.
Temporary Additional Support: Temporary Additional Support is a weekly payment which helps someone who can’t meet their essential living costs from what they earn or from other sources. The maximum period of Temporary Additional Support is 13 weeks. However, there are no restrictions on renewing Temporary Additional Support at the end of this period.
Education and training assistance is financial assistance to help people with training or study costs. These are strictly not income support payments, instead they are designed to provide assistance to people to move into employment.
- Course Participation Assistance: is non-taxable, non-recoverable financial assistance towards the actual and reasonable costs for people on income support participating in a short-term employment related training course or programme. These courses or programmes are generally 12 weeks or less in duration.
- Training Incentive Allowance: is a non-taxable assistance to people receiving primarily carer related benefits (eg Sole Parent Support) to undertake employment related training, and who do not have the necessary work skills, qualifications or recent work experience to obtain employment in the labour market.
- The Guaranteed Childcare Assistance Payment: paid to parents on Young Parent Payment who are studying, and need assistance with childcare.
- Sole Parent Support Study Assistance loan can be paid to sole parents are studying at level 4 or above have costs which are directly related to their study (but not studying for a bachelors with honours, masters or doctorate degree) and have not exhausted the Course Related Costs component of their Student Loan, if they are entitled to this assistance.
- Domestic Purposes Benefit Sole Parent Study Assistance Loan*
- Early Learning Payment pays the costs of Early Childhood Education for children aged 18 months to three years who are from families enrolled in selected Family Start or Early Start Programmes.
Treatment Assistance: financial assistance to help people with health care costs.
- Community Costs: help cover the costs of attending short term residential treatment programmes for alcohol, drug or other medical or psychological disorders (including eating disorders).

Ad-hoc/Hardship assistance

Ad hoc/hardship assistance is the third tier of the income support system. These are irregular payments to cover unexpected or infrequent costs. Ad hoc payments are split between recoverable and non-recoverable.

Recoverable Assistance is a payment which helps people pay for something they need urgently when they have no other way of paying for it. This grant is generally paid for items such as appliances, school uniforms or rent arrears. Recoverable Assistance has to be paid back in instalments.

Recoverable Assistance Payment: provides non-taxable, interest free, recoverable financial assistance to non-beneficiaries to meet essential immediate needs for specific items or services. People who meet the income and cash asset tests may have access to Recoverable Assistance Payments up to a maximum amount of six weeks of the rate of Supported Living Payment.
Advance Payment of Benefit: everyone receiving a main benefit and who require assistance to meet a particular immediate need for an essential item have access to an advance of up to 6 weeks of their net benefit entitlement.
Residential Care Loan is for people going into residential care may want to keep their home for a while to allow them to adjust to their changed circumstances. Rather than selling the home immediately, the Residential Care Loan is an interest-free loan paid directly to the hospital or rest home and is usually repaid when the person dies or the home is sold, whichever happens first.

Non-recoverable assistance payments are tax free and are not expected to be repaid.

Unexpected one-off costs: payments to help cover unexpected events.
- Funeral Grant: can help with some of the funeral costs of someone who has died.
- Special Needs Grant: a payment to help people in certain circumstances pay for something when they have no other way of paying for it. Special Needs Grant includes getting and removing long-term reversible contraception as well as a reestablishment grant for people returning to the community.
- Transition or sustaining employment: assistance to help people move into and remain in employment.
- Modification Grant: provided to people with disabilities lasting for more than six months that helps with the costs of special equipment for work, or changes to a workplace.
- Assistance to transition into employment: Assistance to transition into employment (before 1 July 2014 called Transition to Work Grant) helps people with the costs of moving into a job.
- New Employment Transition Grant is a payment which helps people who are no longer on a benefit and who can’t work because of sickness or a breakdown in childcare arrangements.
- Seasonal Work Assistance: provides financial payments to seasonal workers who are no longer getting a benefit and have lost wages because of work missed due to bad weather.
Childcare related support: a number of payments can be made for parents.
- The School and Year Start-up Payment is available to people who are caring for someone else’s child and helps with the costs that mostly happen at the beginning of the year, in particular pre-school and school-related costs such as clothing, school fees and stationery.
- Extraordinary Care Fund provides non-taxable, financial assistance to carers who receive the Orphans Benefit or Unsupported Childs Benefit. The grant is not included in the hardship count and does not affect eligibility for other payments such as Working for Families or Extra help supplementary assistance. The Extraordinary Care Fund has up to four funding rounds.
Health Care support
- Civilian Amputee Assistance to help people with some of the costs not covered by the public health care system when they are required to attend a limb centre to have an artificial limb fitted, adjusted or repaired. Assistance is not subject to an income or asset test.
Domestic support
- Home Help is a non-taxable payment that provides financial assistance to people who require temporary part-time home help to complete tasks normally performed in the home such as laundry, housework and food preparation. This could be because of a multiple birth, a domestic emergency or because the client requires domestic support. The family needs to have a current Community Services Card and meet an asset test, but do not need to be receiving a main benefit.

Collection Methodology

Income support entitlement information is from MSD’s Social Welfare Information Tomorrow Today (SWIFTT) system. This system was introduced in the early 1990s and provides information on entitlement to income support. Payment transaction information is handled by separate payments system that is not currently available for supply to the IDI.

Eligibility vs payments

As noted previously, income support unlike tax the tax system is based on the family unit. Therefore, reporting on beneficiaries is based on the notional ‘head of household’ and shows the number of families receiving a main benefit (note reporting for New Zealand Superannuation doesn’t do this and counts each adult separately).

However, when it comes to benefit payments these are paid to adults within the family. For main benefits, the total main benefit amount is split evenly between the two adults in the family. Therefore, while BDD tables in the IDI store spells as a partner separately from primary and singles, the payment spells tables include all adults receiving income support.

Benefit entitlement vs actual payments

Income support payment information supplied to the IDI is currently the most up to date version of people’s entitlement to income support payments. The payment data is not what was actually paid to people on a given day (ie a transaction history).

Current entitlement payments show what a person should have been paid over a given period. Entitlement information is retrospectively updated based on new information on a person’s circumstances. For example, a person may apply for benefit, but while they are entitled to payments from the date of application, they will not receive a payment until after the benefit is granted. Conversely, if a person is found to be ineligible for a benefit, then the entitlement payment history is updated to show they should not have been paid and any overpayments are transferred to debt.

Off-sets, debt repayments and re-directions

In the payment rate table, the rate paid is the amount the person is entitled to receive. Entitlement is a difference between the maximum a person can receive minus any abatements from other income (ie the amount of benefit payment decreases as income from employment increases). However, the entitlement payment rate (after tax) shown in these tables may not be what is deposited into a person’s bank account. Subsequent deductions include:

Debt repayments: these are either to repay debt to MSD or other agencies such as Justice.
Redirections: where some benefits are paid to third parties such as power companies or care giver.
Sanctions to benefits: if a person fails to meet their work obligations, they may have payments deducted for a period.

At present we do not have information in the IDI on these deductions or when redirections are made and to whom they are directed to.

IDI tables will not match official benefit numbers

There are two main reasons why IDI data will not reconcile to official benefit counts.

As at and current view of benefit entitlement:
Official reporting of benefit numbers is based on the ‘as at’ view of benefit entitlement, while the IDI tables are the most current view of entitlement. This difference in data presentation means it is not possible to reconcile month end counts between results from the IDI tables to those published by MSD. MSD is working on developing a new data supply that will provide ‘as at’ entitlement payments that will more closely match what is used for official reporting.

Official benefit counts are not counts of people:
Official counts are for benefits ‘in force’ and are based on the nominal head of household (the primary recipient), partners and children are not included in month end counts. The exception is New Zealand Superannuation, where each adult is included in the total count.

Main benefit payments to couples

For main benefits, while entitlement is calculated for the family, the payment is split between each adult in the family. On the other hand, supplementary payments are generally paid to one member of the household.

Number of income support programmes a person can receive

For main benefits, a person within a family can only receive one at a time. Individuals can receive multiple supplementary benefits at the same time, and they can also receive these alongside a main benefit. A supplementary benefit does not get apportioned, and supplementary benefits are paid to one individual regardless of partnership status. For example, a child benefit will go to one parent or other parent on the benefit.

‘Non-beneficiary’ means receiving supplementary benefits only

Within income support, the term ‘non-beneficiary’ refers to people who are receiving supplementary payments but no main benefit. This should not be confused with a person receiving no income support payments at all (ie not on a benefit).

Practical Notes

Duplicate benefits by SNZ_UID

The snz_swn_uid is based on the MSD generated swn number. This value stays constant between refreshes. The snz_uid is the identity the snz_swn_uid is linked to. The linking to the IDI spine is based on IR numbers recorded on the MSD SWIFTT system for PAYE tax deductions. For this reason, the match rate to the spine is generally good for individuals who have received income support. However, there are records where a snz_uid is matched to more than one snz_swn_uid. In these cases, it is up to the researcher to decide how to handle these duplicates. The snz_msd_uid is a SNZ derived linking variable and is not constant across refreshes.

Benefit tables are split by high level benefit type

The income support payments data is split between four tables:

[ise_retirement_benefit]: retirement related benefits such as New Zealand Superannuation and Veteran’s Pension.
[ise_main_benefit]: youth and working age related benefits that currently include Sole Parent Support, Jobseeker Support, Supported Living Payment, Young Parent Payment and Youth Payment.
[ise_supplimentary]: regular top up payments such as Accommodation Supplement
[ise_ad_hoc]: one of hard ship and assistance grants or loans
[ise_tax_credits]: tax credits paid by MSD on IR’s behalf, note this table is only a subset of total tax credit payments.

Tax credits paid by MSD

The [ise_tax_credits] table has the tax credits paid by MSD on IR’s behalf. However, this is not comprehensive of all tax credits received, since people can choose whether they receive tax credits from either MSD or IR.

Benefits are not always classified to the detail required

The current data supply does not allow identification of some specific benefits. Affected benefits include:

Supplementary, miscellaneous benefit: the type of miscellaneous benefit variable is not currently available. This affects income support payment such as the COVID Income Relief Payment (CIRP).

MSD is working to include this additional variable to enable identification of these payments.

Taxation status of income support payments

Tax treatment of income support varies by benefit type. Main benefits have income tax deducted at source (PAYE) and the amount is recorded in the [payment_rate_tax] variable. Supplementary and ad hoc payments are not taxed.

Entitlement is not the same as actual payments

Because of retrospective updates, the entitlement payment history provides an approximate indication of the amount of payment received and when. The actual amount and date of payment may differ from what is presented in these tables. These retrospective actions have the largest impact for the most recent payment history, but in a small number of cases, such as fraud; retrospective actions can extend back many years. Conversely, instances where entitlement was incorrectly applied, then people can receive payments for historical periods of underpayment of income support.

Grouping benefits

Income support programmes have changed over the last 30 years. In some cases, these are minor changes in name (eg Unemployment Benefit to Job Seeker Work Ready) while others have ceased (Widow’s Benefit) or have changed in eligibility (eg Sole Parent Support). To help with aggregating similar programmes and identify the most similar current income support programme, MSD maintains a concordance table in the IDI metadata schema ([IDI_Metadata_YYMM].[msd_bdd].[income_supt_bengrp21_code]).

The table maps the detailed benefit name [benefit_name] and [benefit_name_ detailed] to higher level benefit groups [benefit_name_lvl1, benefit_name_lvl2] as well as comparable contemporary benefit [comparable_benefit_lvl1, comparable_benefit_lvl2].
In the code module tables, each record has a comparable_benefit_lvl1 variable.

Child Disability Allowance (CDA)

Information on CDA is recorded in another table within SWIFTT and is not currently supplied to the IDI.

References & Contacts

For information on current income support programmes refer to Manual and Procedures. The web page also has a history of changes to programmes from 1999 onward (Income support - Map).

For a longer history of income support programmes refer to McKenzie, Social Assistance Chronology - a chronology of social assistance policy and programmes in New Zealand - 1844 to 2022

Community of Interest

Domain	Agency	Person
Lead SME	MSD	Marc de Boer
IDI analysts	MSD	Marc de Boer,
		Bryan Ku,
		Jeffrey Azzato
	The Treasury	Sarah Crichton
Data Supply	MSD	Marc de Boer,
		Bryan Ku,
		Sam Mortlock
Policy/Operational Experts	MSD	Anne Riley,
		Aaron Orr
Module Coder	Nicholson Consulting	Vinay Benny

Module Business Rules

Take payment spells data from [msd_first_tier_expenditure], [msd_second_tier_expenditure] and [msd_third_tier_expenditure] tables.
The [msd_first_tier_expenditure] has [additional_service_data] variable missing, so it cannot identify the specific benefit the payment is for. To resolve this, we use the [additional_service_data] in [msd_spell] table to identify the [additional_service_data] value at the start of each main benefit payment period. However, the more detailed benefit is not always identified.
Benefit descriptions are taken from the IDI metadata table [income_supt_serv21_code] and [income_supt_asd21_code] based on the value current at the benefit payment period start date.
For main benefits, tax credits and ad hoc payments stored in [msd_second_tier_expenditure] are moved to correct table for consistency. Similarly, some supplementary payments are moved from [msd_third_tier_expenditure]. Finally [msd_first_tier_expenditure] expenditure is split between benefits and pensions (eg NZ Superannuation).

Open Issues/Comments

There are a negligible number of records under main benefit dataset that do not have a benefit level associated with them - and hence get removed from the main benefits code module output table.

Parameters

The following parameters should be supplied to this module to run it in the database:

{targetdb}: The SQL database on which the spell datasets are to be created.
“{idicleanversion}”: The IDI Clean version that the spell datasets need to be based on.
{targetschema}: The project schema under the target database into which the spell datasets are to be created.
{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.

Dependencies

[IDI_Metadata_YYYYMM].[msd_bdd].[income_supt_bengrp21_code]
[IDI_Metadata_YYYYMM].[msd_bdd].[income_supt_serv21_code]
[IDI_Metadata_YYYYMM].[msd_bdd].[income_supt_asd21_code]
[IDI_Metadata_YYYYMM].[msd_bdd].[income_supt_payrsn21_code]
[IDI_Clean_YYYYMM].[msd_clean].[msd_spell]
[IDI_Clean_YYYYMM].[msd_clean].[msd_partner]
[IDI_Clean_YYYYMM].[msd_clean].[msd_first_tier_expenditure]
[IDI_Clean_YYYYMM].[msd_clean].[msd_second_tier_expenditure]
[IDI_Clean_YYYYMM].[msd_clean].[msd_third_tier_expenditure]

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].[MSD_ISE_MAIN_BENEFIT]
[IDI_Community].[MSD_ISE_SUPPLEMENTARY]
[IDI_Community].[MSD_ISE_AD_HOC]
[IDI_Community].[MSD_ISE_TAX_CREDITS]
[IDI_Community].[MSD_ISE_TAX_RETIREMENT_BENEFIT]

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

{targetdb}.{targetschema}.{projprefix}_ise_main_benefit
{targetdb}.{targetschema}.{projprefix}_ise_supplementary
{targetdb}.{targetschema}.{projprefix}_ise_ad_hoc
{targetdb}.{targetschema}.{projprefix}_ise_tax_credits
{targetdb}.{targetschema}.{projprefix}_ise_retirement_benefit

Variable Descriptions

Because of the size of the datasets, income support payments have been split into five tables based on broad benefit type. However, for simplicity each table has the same overall structure.

Aspect	Variables	Description
Entity	snz_uid	Current refresh snz_uid
	snz_msd_uid	SNZ maintained master MSD person id. This id can change between refreshes when SNZ creates a new link between underlying MSD ids.
	snz_swn_uid	Social Welfare Number person id that remains constant across refreshes.
Period	Payment_start	Income support payment start date.
	Payment_end	Income support payment end date. Open end dates have a high-end date of 31Dec9999
Event Information	Benefit_lvl2	Main, supplementary, and ad hoc payment type
	Benefit_lvl1	Official benefit name as at the payment start date
	Comparable_benefit_lvl1	The equivalent current benefit name (eg Unemployment benefit is now call Jobseeker Support Work Ready).
	Payment_reason_lvl1	Reason for benefit payment, applies to ad hoc payments only (eg CIRP payments)
	Payment_rate_gross	Daily rate of benefit payment
	Payment_rate_tax	Daily rate of benefit payment tax deduction
	loan	Payment is a loan (recoverable) or not
	Source	Which income support payments table the record comes from ISE1 = 1st tier, ISE2=2nd tier, ISE3=3rd tier).
	Serv_code	SWIFTT system benefit name code
	Additional_service_data	SWIFTT detailed benefit name code
	Payment_type_code	SWIFTT system payment reason code

Module Version & Change History

Date	Version Comments
July 2022	Initial version of code (in SAS) and documentation
31 August 2022	Inital SQL version

Code


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

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

/* This code is structured into 5 sections.
1. Main benefit data, complied from ISE First Tier Expenditure & reallocation of some Supplementary benefits to Main based on their Benefit Level 2 codes.
2. Supplementary benefit data, complied from ISE Second Tier Expenditure & reallocation of some Adhoc payments to Supplementary based on Benefit Level 2 codes.
3. Ad-hoc benefit data,  complied from ISE Third Tier Expenditure & reallocation of some Supplementary to Ad-hoc payments based on Benefit Level 2 codes.
4. Tax Credits data, obtained from ISE Second Tier Expenditure.
5. Retirement benefit data from ISE First Tier Expenditure, reallocated using benefit Level 1 Codes.
*/


/*--------------------- MAIN BENEFITS (EXCL. RETIREMENTS)--------------------------- */
/* Consolidate all main benefits. 2 sources- ise_main_benefit1, realloc_sup_mainben1 */

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

/* 1. Apply some good formatting to the SERV lookup tables */
with msd_income_support_serv_codes as (
	select 
		right('0' + cast(serv.serv_code as varchar(3)), 3) as serv_code
		,trim(serv.benefit_name) as benefit_name
		,convert(date, serv.effective_from, 103) as effective_from
		,convert(date, serv.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.[income_supt_serv21_code] serv
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
		on (trim(serv.benefit_name) = trim(bengrp.benefit_name) 
			and (trim(bengrp.benefit_name_detailed) = '' or bengrp.benefit_name_detailed is null)
			)
)
/* 2. Apply some good formatting to the Additonal Service data codes lookup tables */
,msd_income_support_asd_codes as (
	select 
		asd.additional_service_data
		,asd.benefit_name_detailed
		,convert(date, asd.effective_from, 103) as effective_from
		,convert(date, asd.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.[income_supt_asd21_code] asd
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
	on (trim(asd.benefit_name_detailed) = trim(bengrp.benefit_name_detailed) 
		and trim(bengrp.benefit_name_detailed) <> '' and bengrp.benefit_name_detailed is not null)
)
/* 3. Gather partner information for those who are on main benefits. This is becaue main benefits are paid to both primary and partner recipients when partners are included in benefit.*/
,benefit_partner as (
	select 
		mainben.snz_swn_nbr
		,mainben.msd_spel_spell_nbr
		,ptr.msd_ptnr_ptnr_from_date as benstat_sd
		,coalesce(ptr.msd_ptnr_ptnr_to_date,'9999-12-31') as benstat_ed
		,mainben.msd_spel_servf_code
		,mainben.msd_spel_add_servf_code
		,row_number() over (partition by mainben.snz_swn_nbr, ptr.partner_snz_swn_nbr, ptr.msd_ptnr_ptnr_from_date order by mainben.msd_spel_add_servf_code ) as rn
		,ptr.partner_snz_swn_nbr
	from $(idicleanversion).msd_clean.msd_spell mainben
	inner join $(idicleanversion).msd_clean.msd_partner ptr 
		on (mainben.snz_swn_nbr = ptr.snz_swn_nbr
		   and ptr.msd_ptnr_ptnr_from_date between mainben.msd_spel_spell_start_date and coalesce(mainben.msd_spel_spell_end_date,'9999-12-31')
		   )
	where mainben.msd_spel_spell_start_date >= cast('1990-01-01' as date)
)
/* 4. Apply some de-duplication of partner data. */
,main_benefit_status2 as (
	select 
		b.partner_snz_swn_nbr as snz_swn_nbr
		,b.msd_spel_spell_nbr
		,b.benstat_sd
		,b.benstat_ed
		,b.msd_spel_add_servf_code
	from benefit_partner b 
	inner join (
		select snz_swn_nbr, partner_snz_swn_nbr, benstat_sd, max(rn) as rn
		from benefit_partner group by snz_swn_nbr, partner_snz_swn_nbr, benstat_sd
		) maxrn
		on (b.snz_swn_nbr = maxrn.snz_swn_nbr and b.partner_snz_swn_nbr = maxrn.partner_snz_swn_nbr and b.benstat_sd = maxrn.benstat_sd and b.rn = maxrn.rn)
	union all
	select 
		 mainben.snz_swn_nbr
		,mainben.msd_spel_spell_nbr
		,mainben.msd_spel_spell_start_date as benstat_sd
		,coalesce(mainben.msd_spel_spell_end_date,'9999-12-31') as benstat_ed
		,mainben.msd_spel_add_servf_code
	from $(idicleanversion).msd_clean.msd_spell mainben
	where mainben.msd_spel_spell_start_date >= cast('1990-01-01' as date)
)
/* 5. Remove any main benefit spell duration overlap*/
,main_benefit_status3 as (
	select 
		snz_swn_nbr
		,benstat_sd
		,case when benstat_ed >= coalesce(lead(benstat_sd) over (partition by snz_swn_nbr order by benstat_sd), '9999-12-31') 
			then dateadd(day, -1, benstat_ed) else benstat_ed end as benstat_ed
		,msd_spel_add_servf_code
	from main_benefit_status2 
)
/* 6. Add in any benefits in supplementary tables that are actually Main benefits. */
,realloc_sup_mainben1 as(
	select 
		a.snz_uid
		,a.snz_msd_uid
		,a.snz_swn_nbr
		,b.benefit_name_lvl2 as benefit_lvl2 
		,b.benefit_name as benefit_lvl1 
		,b.comparable_benefit_lvl1 
		,a.msd_ste_start_date as payment_start
		,a.msd_ste_end_date as payment_end
		,a.msd_ste_daily_gross_amt as payment_rate_gross
		,a.msd_ste_daily_gross_amt - msd_ste_daily_nett_amt as payment_rate_tax
		,'N' as loan
		,'ise2' as source
		,a.msd_ste_supp_serv_code as serv_code
from $(idicleanversion).msd_clean.msd_second_tier_expenditure a 
inner join msd_income_support_serv_codes as b
	on (a.msd_ste_supp_serv_code = b.serv_code
		and a.msd_ste_start_date between b.effective_from and b.effective_to
		and b.benefit_name_lvl2 = 'main benefit'
		and a.msd_ste_start_date >= cast('1990-01-01' as date)
		)
)/*7. Bring everything together!!*/
select *
into $(targetschema).$(projprefix)_ise_main_benefit
from (
	select
		a.snz_uid
		,a.snz_msd_uid
		,a.snz_swn_nbr
		,coalesce(c.benefit_name_lvl2, b.benefit_name_lvl2)  as benefit_lvl2
		,coalesce(c.benefit_name_detailed, b.benefit_name) as benefit_lvl1 
		,coalesce(c.comparable_benefit_lvl1, b.comparable_benefit_lvl1) as comparable_benefit_lvl1 
		,a.msd_fte_start_date as payment_start
		,a.msd_fte_end_date as payment_end
		,a.msd_fte_daily_gross_amt as payment_rate_gross
		,a.msd_fte_daily_gross_amt - msd_fte_daily_nett_amt as payment_rate_tax
		,'N' as loan
		,'ise1' as source
		,a.msd_fte_serv_code as serv_code
		,mainben.msd_spel_add_servf_code as additional_service_data
	from $(idicleanversion).msd_clean.msd_first_tier_expenditure a
	left join main_benefit_status3 as mainben
		on a.snz_swn_nbr = mainben.snz_swn_nbr
			and a.msd_fte_start_date between mainben.benstat_sd and mainben.benstat_ed 
	left join msd_income_support_serv_codes as b
		on (a.msd_fte_serv_code = b.serv_code
			and a.msd_fte_start_date between b.effective_from and b.effective_to)
	left join msd_income_support_asd_codes as c
		on (mainben.msd_spel_add_servf_code = c.additional_service_data
			and a.msd_fte_start_date between c.effective_from and c.effective_to)
	where coalesce(c.benefit_name_detailed, b.benefit_name) not in ('Non Beneficiary','Transitional Retirement Benefit','Transitional Retirement Benefit weekly', 'Veterans Pension', 'New Zealand Superannuation') 
	union all
	select *, cast(NULL as varchar(6)) as additional_service_data
	from realloc_sup_mainben1
)x;

/*--------------------- SUPPLEMENTARY BENEFITS --------------------------- */

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

/* Consolidate all supplementary benefits. 2 sources- msd_second_tier_expenditure, realloc_adhoc_sup1*/
with msd_income_support_serv_codes as (
	select 
		right('0' + cast(serv.serv_code as varchar(3)), 3) as serv_code
		,trim(serv.benefit_name) as benefit_name
		,convert(date, serv.effective_from, 103) as effective_from
		,convert(date, serv.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.income_supt_serv21_code serv
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
		on (trim(serv.benefit_name) = trim(bengrp.benefit_name) 
			and (trim(bengrp.benefit_name_detailed) = '' or bengrp.benefit_name_detailed is null)
			)
)
,realloc_adhoc_sup1 as(
	select 
		a.snz_uid
		,a.snz_msd_uid
		,a.snz_swn_nbr
		,b.benefit_name_lvl2 as benefit_lvl2
		,b.benefit_name as benefit_lvl1 
		,b.comparable_benefit_lvl1 
		,c.payment_reason_lvl1 
		,a.msd_tte_decision_date as payment_start
		,a.msd_tte_decision_date as payment_end
		,a.msd_tte_pmt_amt as payment_rate_gross
		,0 as payment_rate_tax
		,a.msd_tte_recoverable_ind as loan
		,'ise3' as source
		,a.msd_tte_lump_sum_svc_code as serv_code
		,a.msd_tte_pmt_rsn_type_code as payment_type_code
	from $(idicleanversion).msd_clean.msd_third_tier_expenditure  as a 
	left join msd_income_support_serv_codes as b
		on (a.msd_tte_lump_sum_svc_code = b.serv_code
		and a.msd_tte_decision_date between b.effective_from and b.effective_to
		)
	left join $(idimetadataversion).msd_bdd.income_supt_payrsn21_code as c
		on (a.msd_tte_pmt_rsn_type_code = c.payrsn_code)
	where a.msd_tte_decision_date >= cast('1990-01-01' as date)
		and b.benefit_name_lvl2 = 'supplementary benefit'
)
select *
into $(targetschema).$(projprefix)_ise_supplementary
from (
	select 
		sup.snz_uid
		,sup.snz_msd_uid
		,sup.snz_swn_nbr
		,serv.benefit_name_lvl2 as benefit_lvl2 
		,serv.benefit_name as benefit_lvl1 
		,serv.comparable_benefit_lvl1 
		,NULL as payment_reason_lvl1
		,sup.msd_ste_start_date as payment_start
		,sup.msd_ste_end_date as payment_end
		,sup.msd_ste_daily_gross_amt as payment_rate_gross
		,sup.msd_ste_daily_gross_amt - msd_ste_daily_nett_amt as payment_rate_tax
		,'N' as loan
		,'ise2' as source
		,sup.msd_ste_supp_serv_code as serv_code
		,NULL as payment_type_code
	from $(idicleanversion).msd_clean.msd_second_tier_expenditure sup 
	left join msd_income_support_serv_codes as serv
		on (sup.msd_ste_supp_serv_code = serv.serv_code
			and sup.msd_ste_start_date between serv.effective_from and serv.effective_to)
	where serv.benefit_name_lvl2 = 'supplementary benefit'
		and sup.msd_ste_start_date >= cast('1990-01-01' as date)
	union all
	select 
		adhoc_sup.snz_uid
		,adhoc_sup.snz_msd_uid
		,adhoc_sup.snz_swn_nbr
		,adhoc_sup.benefit_lvl2
		,adhoc_sup.benefit_lvl1 
		,adhoc_sup.comparable_benefit_lvl1 
		,adhoc_sup.payment_reason_lvl1 
		,adhoc_sup.payment_start
		,adhoc_sup.payment_end
		,adhoc_sup.payment_rate_gross
		,payment_rate_tax
		,adhoc_sup.loan
		,source
		,adhoc_sup.serv_code
		,adhoc_sup.payment_type_code
	from realloc_adhoc_sup1 adhoc_sup
)x;

/*--------------------- AD-HOC BENEFITS --------------------------- */

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

/* Consolidate all adhoc payment benefits. 2 sources- msd_third_tier_expenditure, realloc_sup_adhoc1*/
with msd_income_support_serv_codes as (
	select 
		right('0' + cast(serv.serv_code as varchar(3)), 3) as serv_code
		,trim(serv.benefit_name) as benefit_name
		,convert(date, serv.effective_from, 103) as effective_from
		,convert(date, serv.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.income_supt_serv21_code serv
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
		on (trim(serv.benefit_name) = trim(bengrp.benefit_name) 
			and (trim(bengrp.benefit_name_detailed) = '' or bengrp.benefit_name_detailed is null)
			)
)
,realloc_sup_adhoc1 as (
	select 
		sup.snz_uid
		,sup.snz_msd_uid
		,sup.snz_swn_nbr
		,serv.benefit_name_lvl2 as benefit_lvl2 
		,serv.benefit_name as benefit_lvl1 
		,serv.comparable_benefit_lvl1 
		,NULL as payment_reason_lvl1
		,sup.msd_ste_start_date as payment_start
		,sup.msd_ste_end_date as payment_end
		,sup.msd_ste_daily_gross_amt as payment_rate_gross
		,sup.msd_ste_daily_gross_amt - msd_ste_daily_nett_amt as payment_rate_tax
		,'N' as loan
		,'ise2' as source
		,sup.msd_ste_supp_serv_code as serv_code
		,NULL as payment_type_code
	from $(idicleanversion).msd_clean.msd_second_tier_expenditure sup 
	left join msd_income_support_serv_codes as serv
		on (sup.msd_ste_supp_serv_code = serv.serv_code
			and sup.msd_ste_start_date between serv.effective_from and serv.effective_to)
	where serv.benefit_name_lvl2 = 'one off payments'
		and sup.msd_ste_start_date >= cast('1990-01-01' as date)
)
select *
into $(targetschema).$(projprefix)_ise_ad_hoc
from (
	select 
		a.snz_uid
		,a.snz_msd_uid
		,a.snz_swn_nbr
		,b.benefit_name_lvl2 as benefit_lvl2
		,b.benefit_name as benefit_lvl1 
		,b.comparable_benefit_lvl1 
		,c.payment_reason_lvl1 
		,a.msd_tte_decision_date as payment_start
		,a.msd_tte_decision_date as payment_end
		,a.msd_tte_pmt_amt as payment_rate_gross
		,0 as payment_rate_tax
		,a.msd_tte_recoverable_ind as loan
		,'ise3' as source
		,a.msd_tte_lump_sum_svc_code as serv_code
		,a.msd_tte_pmt_rsn_type_code as payment_type_code
	from $(idicleanversion).msd_clean.msd_third_tier_expenditure  as a 
	left join msd_income_support_serv_codes as b
		on (a.msd_tte_lump_sum_svc_code = b.serv_code
		and a.msd_tte_decision_date between b.effective_from and b.effective_to
		)
	left join $(idimetadataversion).msd_bdd.income_supt_payrsn21_code as c
		on (a.msd_tte_pmt_rsn_type_code = c.payrsn_code)
	where a.msd_tte_decision_date >= cast('1990-01-01' as date)
		and b.benefit_name_lvl2 = 'one off payments'
	union all
	select 
		sup.snz_uid
		,sup.snz_msd_uid
		,sup.snz_swn_nbr
		,sup.benefit_lvl2 
		,sup.benefit_lvl1 
		,sup.comparable_benefit_lvl1 
		,sup.payment_reason_lvl1
		,sup.payment_start
		,sup.payment_end
		,sup.payment_rate_gross
		,sup.payment_rate_tax
		,sup.loan
		,sup.source
		,sup.serv_code
		,sup.payment_type_code
	from realloc_sup_adhoc1 sup
)x;


/*--------------------- TAX CREDIT BENEFITS --------------------------- */

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

/* Tax credits data- (those paid by MSD) */
with msd_income_support_serv_codes as (
	select 
		right('0' + cast(serv.serv_code as varchar(3)), 3) as serv_code
		,trim(serv.benefit_name) as benefit_name
		,convert(date, serv.effective_from, 103) as effective_from
		,convert(date, serv.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.income_supt_serv21_code serv
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
		on (trim(serv.benefit_name) = trim(bengrp.benefit_name) 
			and (trim(bengrp.benefit_name_detailed) = '' or bengrp.benefit_name_detailed is null)
			)
)
select 
	sup.snz_uid
	,sup.snz_msd_uid
	,sup.snz_swn_nbr
	,serv.benefit_name_lvl2 as benefit_lvl2 
	,serv.benefit_name as benefit_lvl1 
	,serv.comparable_benefit_lvl1 
	,sup.msd_ste_start_date as payment_start
	,sup.msd_ste_end_date as payment_end
	,sup.msd_ste_daily_gross_amt as payment_rate_gross
	,sup.msd_ste_daily_gross_amt - msd_ste_daily_nett_amt as payment_rate_tax
	,'N' as loan
	,'ise2' as source
	,sup.msd_ste_supp_serv_code as serv_code
into $(targetschema).$(projprefix)_ise_tax_credits
from $(idicleanversion).msd_clean.msd_second_tier_expenditure sup 
left join msd_income_support_serv_codes as serv
	on (sup.msd_ste_supp_serv_code = serv.serv_code
		and sup.msd_ste_start_date between serv.effective_from and serv.effective_to)
where serv.benefit_name_lvl2 = 'tax credit'
	and sup.msd_ste_start_date >= cast('1990-01-01' as date);


/*--------------------- RETIREMENT BENEFITS --------------------------- */

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

/* Retirements benefits*/
with msd_income_support_serv_codes as (
	select 
		right('0' + cast(serv.serv_code as varchar(3)), 3) as serv_code
		,trim(serv.benefit_name) as benefit_name
		,convert(date, serv.effective_from, 103) as effective_from
		,convert(date, serv.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.income_supt_serv21_code serv
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
		on (trim(serv.benefit_name) = trim(bengrp.benefit_name) 
			and (trim(bengrp.benefit_name_detailed) = '' or bengrp.benefit_name_detailed is null)
			)
)
/* 2. Apply some good formatting to the Additonal Service data codes lookup tables */
,msd_income_support_asd_codes as (
	select 
		asd.additional_service_data
		,asd.benefit_name_detailed
		,convert(date, asd.effective_from, 103) as effective_from
		,convert(date, asd.effective_to, 103) as effective_to
		,trim(bengrp.benefit_name_lvl2) as benefit_name_lvl2
		,trim(bengrp.comparable_benefit_lvl1) as comparable_benefit_lvl1
	from $(idimetadataversion).msd_bdd.income_supt_asd21_code asd
	left join $(idimetadataversion).msd_bdd.[income_supt_bengrp21_code] bengrp
	on (trim(asd.benefit_name_detailed) = trim(bengrp.benefit_name_detailed) 
		and trim(bengrp.benefit_name_detailed) <> '' and bengrp.benefit_name_detailed is not null)
)
/* 3. Gather partner information for those who are on main benefits. This is becaue main benefits are paid to both primary and partner recipients when partners are included in benefit.*/
,benefit_partner as (
	select 
		mainben.snz_swn_nbr
		,mainben.msd_spel_spell_nbr
		,ptr.msd_ptnr_ptnr_from_date as benstat_sd
		,coalesce(ptr.msd_ptnr_ptnr_to_date,'9999-12-31') as benstat_ed
		,mainben.msd_spel_servf_code
		,mainben.msd_spel_add_servf_code
		,row_number() over (partition by mainben.snz_swn_nbr, ptr.partner_snz_swn_nbr, ptr.msd_ptnr_ptnr_from_date order by mainben.msd_spel_add_servf_code ) as rn
		,ptr.partner_snz_swn_nbr
	from $(idicleanversion).msd_clean.msd_spell mainben
	inner join $(idicleanversion).msd_clean.msd_partner ptr 
		on (mainben.snz_swn_nbr = ptr.snz_swn_nbr
		   and ptr.msd_ptnr_ptnr_from_date between mainben.msd_spel_spell_start_date and coalesce(mainben.msd_spel_spell_end_date,'9999-12-31')
		   )
	where mainben.msd_spel_spell_start_date >= cast('1990-01-01' as date)
)
/* 4. Apply some de-duplication of partner data. */
,main_benefit_status2 as (
	select 
		b.partner_snz_swn_nbr as snz_swn_nbr
		,b.msd_spel_spell_nbr
		,b.benstat_sd
		,b.benstat_ed
		,b.msd_spel_add_servf_code
	from benefit_partner b 
	inner join (
		select snz_swn_nbr, partner_snz_swn_nbr, benstat_sd, max(rn) as rn
		from benefit_partner group by snz_swn_nbr, partner_snz_swn_nbr, benstat_sd
		) maxrn
		on (b.snz_swn_nbr = maxrn.snz_swn_nbr and b.partner_snz_swn_nbr = maxrn.partner_snz_swn_nbr and b.benstat_sd = maxrn.benstat_sd and b.rn = maxrn.rn)
	union all
	select 
		 mainben.snz_swn_nbr
		,mainben.msd_spel_spell_nbr
		,mainben.msd_spel_spell_start_date as benstat_sd
		,coalesce(mainben.msd_spel_spell_end_date,'9999-12-31') as benstat_ed
		,mainben.msd_spel_add_servf_code
	from $(idicleanversion).msd_clean.msd_spell mainben
	where mainben.msd_spel_spell_start_date >= cast('1990-01-01' as date)
)
/* 5. Remove any main benefit spell duration overlap*/
,main_benefit_status3 as (
	select 
		snz_swn_nbr
		,benstat_sd
		,case when benstat_ed >= coalesce(lead(benstat_sd) over (partition by snz_swn_nbr order by benstat_sd), '9999-12-31') 
			then dateadd(day, -1, benstat_ed) else benstat_ed end as benstat_ed
		,msd_spel_add_servf_code
	from main_benefit_status2 
)
/*6. Bring everything together and retain only retirement type benefits*/
select
    a.snz_uid
	,a.snz_msd_uid
	,a.snz_swn_nbr
	,coalesce(c.benefit_name_lvl2, b.benefit_name_lvl2)  as benefit_lvl2
	,coalesce(c.benefit_name_detailed, b.benefit_name) as benefit_lvl1 
	,coalesce(c.comparable_benefit_lvl1, b.comparable_benefit_lvl1) as comparable_benefit_lvl1 
	,a.msd_fte_start_date as payment_start
	,a.msd_fte_end_date as payment_end
	,a.msd_fte_daily_gross_amt as payment_rate_gross
	,a.msd_fte_daily_gross_amt - msd_fte_daily_nett_amt as payment_rate_tax
	,'N' as loan
	,'ise1' as source
	,a.msd_fte_serv_code as serv_code
	,mainben.msd_spel_add_servf_code as additional_service_data
into $(targetschema).$(projprefix)_ise_retirement_benefit
from $(idicleanversion).msd_clean.msd_first_tier_expenditure a
left join main_benefit_status3 as mainben
	on a.snz_swn_nbr = mainben.snz_swn_nbr
		and a.msd_fte_start_date between mainben.benstat_sd and mainben.benstat_ed 
left join msd_income_support_serv_codes as b
	on (a.msd_fte_serv_code = b.serv_code
		and a.msd_fte_start_date between b.effective_from and b.effective_to)
left join msd_income_support_asd_codes as c
	on (mainben.msd_spel_add_servf_code = c.additional_service_data
		and a.msd_fte_start_date between c.effective_from and c.effective_to)
/* Retain only retirement benefit types.*/
where coalesce(c.benefit_name_detailed, b.benefit_name) in ('Transitional Retirement Benefit','Transitional Retirement Benefit weekly', 'Veterans Pension', 'New Zealand Superannuation');