Total income module collection: Tax credits

ivan.welsh
24 June 2024

Module output

SQL::[IDI_Community].[cm_read_income_t1_tax_credits].income_t1_tax_credits_YYYYMM
SAS: libname cmti ODBC dsn=idi_community_srvprd schema=cm_read_income_t1_tax_credits; proc print data = cmti.income_t1_tax_credits_YYYYMM
How to access a code module in the Data Lab : Read here

Purpose of Tax credits module

The purpose of this module is to construct the total tax credits for people in New Zealand. Entitlements in this module are broken down by type of tax credit.

Key concepts

Tax credits income

Tax credits are all family assistance payments such as those under the working for families package and low income independent earners or Independent Earner Tax Credit (IETC).

Types of tax credit and descriptions

Working for Families (WFF) tax credits:

To get Working for Families, a person must:

A person is entitled to payments up until a child turns 18. If the child is still at school or in tertiary study when they turn 18, a person can get payments until 31 December of that year.

The different WFF tax credits are available for people with children, as outlined below:

Family tax credit (FTC) - Provides basic assistance for families with dependent children. Paid to the nominated “principal caregiver”.

Entitlement is abated according to joint family income. Was previously called family support tax credit, and some variable names reflect this.

A family won’t be eligible for FTC if the family receives a parent’s allowance (paid under Section 32(2) of the War Pensions Act 1954 (repealed)), or for children that are included in a foster care allowance (board payments from OrangaTamariki), orphan’s benefit, or unsupported child’s benefit.

Although a family may qualify to receive FTC, their entitlement to the credit can be nil, based on their circumstances. The amount they are entitled to receive will differ depending on:

For families who receive a main benefit, FTC is usually paid by MSD, and may be protected. Protected FTC prevents the family’s entitlement from being abated, meaning they will receive the maximum FTC amount, if they meet both conditions:

In Work Tax Credit (FTC) - Introduced in 2006 to replace the Child Tax Credit. Payable to families independent of state support, who also meet a work test of 20 hours per week (single parent families) or 30 hours per week (two-parent families). This work test was removed on 1 July 2020.

From 1 April 2021, customers receiving the in-work tax credit can keep receiving the payments for up to two weeks when taking an unpaid break from work (a grace period). This could be either as they transition between jobs, are unpaid for a period, or leave employment.

From 1 July 2020, the ‘hours of work’ requirement was removed as part of the COVID-19 Response (Taxation and Social Assistance Urgent Measures) Act 2020. Up to 30 June 2020, customers were required to work:

On 1 April 2006, In-work tax credit (IWTC) replaced the child tax credit (CTC) for families who meet these eligibility requirements:

Child Tax Credit (CTC) - Introduced in July 1996 and payable to families independent of signficant state support (not receiving benefit, super or ACC weekly compensation).

Was previously known as the Independent Family Tax Credit. Was replaced by IWTC in April 2006, but with grandparenting provisions for existing recipients.

Very few people are now receiving it and no new Working for Families customers can receive CTC.

Parental Tax Credit (PTC) - Introduced in October 2009, with the same qualifying criteria as CTC, and paid for the first 10 weeks after the birth of a child including a child adopted at birth. PTC was removed in July 2018 with the introduction of BSTC.

Minimum Family Tax Credit (MFTC) - Guarantees a minimum after-tax family income, pro-rated by the number of weeks in full-time work. Other WFF entitlements are not included in the income calculation. Was formerly (and confusingly) known as Family Tax Credit, and some variable names reflect this. Was also formerly known as Guaranteed Minimum Family Income (GMFI).

This payment is for families who earn under the threshold amount, and work a minimum number of hours each week as follows:

Unless the principal caregiver (PCG) or their partner also work the required hours for salary or wages, they won’t qualify for MFTC for the weeks their family income is from:

Best Start Tax Credit (BSTC) - Introduced as part of the Families Package in 1 July 2018, Best Start is a weekly payment for families supporting a newborn baby. Families who qualify for Best Start can receive the payment until the child turns 1, regardless of their income. People can only receive Best Start once they are no longer receiving Paid Parental Leave. After the first year, families can receive Best Start payments until their child turns 3, but the payments are income tested alongside other WFF payments.

From 1 April 2024 - Families can receive up to $73.00 a week (up to $3,838.00 per year) for each qualifying child.

From 1 April 2023 - 31 March 2024 - Families can receive up to $69.00 a week (up to $3,632.00 per year) for each qualifying child.

Working for Families tax credits are paid by Inland Revenue, either on a weekly or fortnightly basis, or at the end of the tax year. If people are paid weekly/fortnightly they may end up with a debt to IR (or a credit) at the end of the tax year if they underestimated the income they would earn in the tax year. Any end-of-year adjustment is likely to be paid in the following tax year, although people sometimes claim Working for Families payments for historical tax years Information about WFF debts that may be later repaid or written off is not included

If a primary caregiver is on benefit they are generally paid Family Tax Credit (and, in recent years, Best Start Tax Credit) by MSD. Beneficiaries are not eligible for other WFF tax credits. If they are paid WFF by MSD, these periods are ‘ring-fenced’ such that any income earned in off-benefit periods within the tax year cannot affect the person’s on-benefit entitlement. This removes the risk that a person is penalised for moving off benefit and into work by having a debt created with IR.

Abatement - FTC, IWTC, PTC, and BSTC (after child turns 1) are abated together, based on joint family income.

Independent Earner Tax Credit (IETC):

The IETC was introduced in 2009 and gives a payment of 10 dollars per week to individuals earning between $24,000 and $48,000 per annum, and who are not receiving an income tested benefit, WFF tax credits, NZ superannuation, a veteran’s pension or an overseas equivalent to any of these. IETC is progressively abated for those earning over $44,000. IETC was intended to increase workforce participation incentives by targeting those just below the full-time minimum wage (at the time it was introduced). IETC entitlement is based on whole calendar months. If the customer is ineligible for part of the month, then there is no entitlement for the full month. IETC can be claimed for deceased customers only for whole months while they were alive.

Unresolved Data quality issue:

According to Treasury reporting, around half a million people received IETC each year between 2012 and 2015, with payments either being made to salary and wage earners through the PAYE system and to other people though the end-of-year tax return process (IR3 returns or personal tax summaries). Around two-thirds of people are reported as receiving the payments at the end of the tax year, however only a small proportion of the stated half-million recipients each year seem to be identifiable from the IR3 or PTS data. Amount of IETC entitlement is a data point supplied in the autocalc file, IR3 file only shows whether the tax credit has been claimed or not.

Total income spell

A total income spell is the minimum period per income type. Tax credits in practice may be paid in a daily, weekly, fortnightly, monthly spell etc.

Source frequency

Source frequency is the frequency with which an income type is stored in the data. Source frequency for tax credits is varying. WFF, PTS and Autocalc tables are annual and MSD second tier table is spell based (Note that Inland Revenue shares all FAM tax credits files with Stats NZ monthly). Within this module, we record tax credits using annual spells.

Gross income amount

Gross income amount is the taxable income before tax that a person receives.

Observed tax

Tax recorded as paid to IRD in the IDI. This is the amount of tax that has been has been deducted from source (eg PAYE and tax filing). Tax credits are not taxable.

Granularity

The modules are setup to be event format: One row per event, events have a person, start and end dates, and descriptive information. The variables below will uniquely identify each event in the above table:

Entity id: snz uid
Period: start end, end date
Event information: income source, taxable income indicator

Data sources

wff.clean_fam_returns.dtls

Working For Families (WFF) datasets contain information ranging from 1999 to present. WFF is assistance for families that is delivered jointly by MSD and Inland Revenue. It contains information about WFF tax credits (family tax credit, in-work tax credit, minimum family tax credit, parental tax credit), Accommodation supplement and Childcare assistance (childcare subsidy, OSCAR subsidy).

WFF Family Return Details table provides entitlements and amounts paid to the primary caregiver with data available from March 2000 to present. This includes tax credit entitlements from the family support tax credit (FSTC), parental tax credit (PTC), child tax credit (CTC), family tax credit (FTC), in-work tax credit (IWTC), and best start tax credit. This table also has information on Student loans and benefit payments. Though WFF entitlements are dependent on family income, it is paid to the primary caregiver so for income derivation purposes it is regarded as income only for the primary caregiver. This table is refreshed monthly as at the last day of each month.

Employer monthly schedule: ir_clean.ird_ems

Employer monthly schedule (EMS) table contains employee level data from the tax returns (also called Employment Information on IR website) filed by employers. The EMS form is also used by government agencies to report government transfer payments to individuals that are taxed at source. They include income-tested benefits, New Zealand superannuation, student allowances, paid parental leave, and accident compensation payments. EMS table is built using EIE data provided by IR.

EMS is available in the LBD/IDI. This is a monthly tax return filed by all businesses with paid employees, which summarises the monthly wage and salary payments made to each of their employees, and the ‘pay-as-you-earn’ (PAYE) income tax deductions made. EMS is a mandatory reporting requirement for all employing firms.

Since April 2019, Inland Revenue has shifted from monthly filings under the EMS to compulsory payday filing under the Employment Information - Employee (EI-E) system. This follows a transition period since April 2018 in which firms could select whether to file monthly or through the payday filing system. For consistency, Stats NZ has made a processed version of the EI-E data available, allocating payday information to calendar months and including these records as part of the EMS tables.

Note:

MSD: msd_clean.msd_second_tier_expenditure

The Ministry for Social Development (MSD) datasets within the IDI contain information about government transfer benefits paid to individuals. The MSD data is supplied to Stats NZ on a quarterly basis. Majority of MSD data variables come from the SWIFTT (Social Welfare Information for Tomorrow Today) data.
Second-tier benefits are non-taxable government transfers with data available from April 1990 to present. They contain information about supplementary benefit daily rate. As these are non-taxable transfers, the data is not available in IR data. Often, these are supplementary payments to a main benefit, and supplementary rates are taken from supplementary code rates under main benefit rates. For example, an individual receiving jobseeker support could also be receiving the accommodation supplement and winter energy payment.

PTS: ir_clean.ird_pts

This table contains information on personal tax summary (PTS) with data available from March 2000 to March 2020. PTS is a tax return for individual taxpayers to show their individual income and tax deduction details for a given tax year. This table can be used to capture interest income and dividend income.

Autocalc: ir_clean.ird_autocalc_information

This table shows information used by IR to calculate automatic assessments with data available from April 2019 to present. This auto-calc process is applied to people whose income is only salary, wages, interest or dividends. This information is received by IR from employers and payers of investment income. Assessments are calculated and refunds are issued automatically.

idi_adhoc.clean_read_ir.ir_ir3_2013_to_2020

IR3 is the income tax return for individuals used to confirm the amount of personal income tax to be paid at the end of each tax year with data available from March 2000 to present. The main purpose of IR3 is to include any monetary payments that have not been taxed at source. IR3 is used for self-employment (filed annually by sole traders) and includes non-zero partnership, or shareholder salary income, as well as rental income. IR3 is mandatory for individuals who earned more than $200 (before tax) income other than salary, wages, interest, dividends, and/or taxable Māori authority distributions. IR3 is filed annually.

Starting from July 2017, IR has extended the supply of IR3 information to the IDI to include further information about investment income, including earnings from interest, dividend, estate trust, overseas, and other sources. Also included are non-taxable income from a range of tax credit entitlements, tax rebate, and student loan-liable income.

There may be a substantial lag in reporting IR3 data as individual businesses and those using tax agents have up to a year after the end of the financial year to file an annual tax return with IR.

Note: IR3s belonging to people who reported no self-employment, partnership or shareholder income were excluded for tax years prior to 2019. They are in the ad-hoc tables.

Extra note: data summary in intermediate temp tables

The data is at an SNZ_UID by tax year level, and includes the following fields:

Practical notes

Period

The tax credits information is from 1999 onwards.

Negative amounts

No exclusions made. Some tax credit amounts, such as a few Family Tax credits are recorded as negative amounts in the tax year ended 31 March 2020. This has been corrected in the code by taking the absolute values of these negative amounts.

Tax credits future upgrades

There is ongoing work for converting WFF date from annual to monthly. Depending on the state of the work, we can include it in future upgrades to the module. Separate identification of tax credits paid by IR and MSD can also be included in a future upgrade. Inclusion of MSD spells can also be a consideration for future upgrades.

Additional notes

Sources of validation

Working for families statistics.xls (Working for Families statistics datasets)
Except for IETC, the module outputs match well with the validation dataset

References

  1. Can you get Working for Families?
  2. https://www.ird.govt.nz/working-for-families/about

Key Contacts

Role Organisation Person
Dev lead MBIE Eric Krassoi Peach
Module Coder Nicholson Consulting Linda Martis
Code Supply Independent Keith McLeod
Peer review (code) Stats NZ Ivan Welsh
Peer review (documentation) IRD
IRD		 Joanne Butterfield
Scott Anderson
Module Steward TBD TBD

Module business rules

Defining spell start and end dates

Start and end dates are based on spell dates. The return period column is end date and the start date will be the start of that tax year.

Person identifier snz_uid

A global unique identifier created by Statistics NZ. There is a snz_uid for each distinct identity in the IDI. This identifier is changed and reassigned each refresh.

Minimum income threshold

Currently no minimum threshold applied.

Interoperability with other modules

This module is in the business landscape so could be that it is most closely associated with business profile modules and employment spells. It is also in the socioeconomic landscape where income is a determinant of positive social outcomes, so associated with social housing, education modules, health modules, driving modules, employment assistance modules etc.

Taxable income indicator

Taxable income indicator to be hard coded in modules. Within tax credits module, income is not taxable.

IETC data source: Only available in IR3 2013-2020, PTS and more recently from Autocalc

In a few cases it is captured in multiple data sources, mainly during the transition from PTS to AC.
Occasionally the values in the different sources differ.
In absence of an alternative, we take AC first, then PTS, then IR3 (AC > PTS > IR3)

FTC data source : WFF > EMS (supplementary use of MSD t2 table)

FTC comes from multiple sources. Primary source is WFF fam_return_details, which has most IR payments and some MSD payments and ACC

Almost all FTC records should be recorded in WFF, unless WFF is only paid by MSD, in which case they should also be in EMS and MSD second tier expenditure.

We take FTC data from WFF first, then EMS. We ignore PTS and IR3 as these should reflect WFF.

EMS doesn’t separately identify FTC and BSTC, so we need to apportion these based on their share of tier 2 payments.

We could instead use tier 2 directly, but EMS is expected to be a more accurate reflection of actual payments made by MSD

PTC/CTC/IWTC : data source : WFF

PTC/CTC/IWTC are consistently recorded in WFF. Sometimes also in PTS and IR3 records. We just use WFF as this is likely to be the most reliable.
NB: CTC was grandparented in 2006 and very few people now receive it.
NB: PTC was disestablished in July 2018 (2019 tax year).

MFTC: data source: WFF

MFTC is consistently recorded in WFF. Sometimes also in PTS and IR3 records. Since 2021 there have been some very strange large values in the IR3 data which look very suspicious and may relate to something completely different. For this reason it is not used here

BSTC: data source: WFF > EMS (supplementary use of MSD t2 table)

BSTC is recorded in WFF data when it is paid by IR, and is recorded in tier 2 when paid by MSD
As with FTC we need to apportion EMS amounts based on their share of tier 2 payments.

Code logic:

  1. Obtain FTC and BSTC data from ird_ems
  2. Obtain FTC and BSTC data from msd_second_tier_expenditure
    -We calculate the total payment based on daily gross amount
    -convert period to tax years
  3. Obtain bstc, ptc, ctc, ftc, mftc, iwtc amounts from wff_clean.fam_return_dtls
    -Steps to remove duplicates in data
  4. Obtain ietc data from ird_autocalc_information
    -Steps to remove duplicates in data
  5. Obtain ptc, ctc, mftc, ftc, iwtc data from ird_rtns_keypoints_ir3
    -Steps to remove duplicates in data
  6. Obtain ptc, ctc, mftc, ftc, iwtc, bstc, ietc data from ird_pts
    -Steps to remove duplicates in data
  7. Obtain ptc, ctc, mftc, ftc, iwtc data from Adhoc table ir_ir3_2000_to_2014
    -Steps to remove duplicates in data
  8. Obtain ietc data from Adhoc table ir_ir3_2013_to_2020
    -Steps to remove duplicates in data
  9. Steps 1 to 8 extract all data from multiple sources.
    We will not be using all the sources for every tax credit.
  10. Create a final table with
    -ietc from AC > PTS > IR3
    -ftc from WFF > EMS, using MSD t2 to apportion amount
    -ptc from WFF
    -ctc from WFF
    -iwtc from WFF
    -mftc from WFF
    -bstc from WFF > EMS, using MSD t2 to apportion amount

Parameters

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

  1. {targetdb}: The SQL database on which the spell datasets are to be created.
  2. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
  3. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
  4. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.

Dependencies

[IDI_Clean_YYYYMM].[wff_clean].[fam_return_dtls] 
[IDI_Clean_YYYYMM].[ir_clean].[ird_ems]
[IDI_Clean_YYYYMM].[msd_clean].[msd_second_tier_expenditure]  
[IDI_Clean_YYYYMM].[ir_clean].[ird_pts] 
[IDI_Clean_YYYYMM].[ir_clean].[ird_autocalc_information] 
[IDI_Clean_YYYYMM].[ir_clean].[ird_rtns_keypoints_ir3]
[IDI_Adhoc].[clean_read_IR].[ir_ir3_2013_to_2020]
[IDI_Adhoc].[clean_read_IR].[ir_ir3_2000_to_2014]

Outputs

{targetdb}.{targetschema}.{projprefix}_tax_credits_{refresh}

Variable Descriptions

Aspect Variables Description
Entity snz_uid Current refresh snz_uid
Period period_start_date
period_end_date		 Tax year start date
Tax year end date
Event source_frequency Frequency of income recorded in source data
Information income_source Type of tax credit being derived in this module
Note that:
- Tax credits are recorded annually in the module
- MSD table records tax credits as spells, but that has not been used in this module
Income sources are:
- Best Start Tax Credit
- Child Tax Credit
- Family Tax Credit
- In Work Tax Credit
- Independent Earner Tax Credit
- Minimum Family Tax Credit
- Parental Tax Credit
taxable_income_indicator Indicator for whether income is taxable
Note that:
1:income is taxable
0:income is not taxable
Tax credits are not taxable
gross_income Income before tax

Module Version & Change History

Date Version Comments
2023-06 Initial version of code created by Keith McLeod (Independent contractor)
2024-06 Initial Streamlining and preparation for code module by Linda Martis, Nicholson Consulting

Code

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

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

 

/*
Create EMS FTC/BSTC data
EMS FTC is FTC paid by MSD, but actually also includes BSTC paid by MSD since July 2018
*/

/*
There are a few thousand observations where the FTC entitlement is negative rather than positive
All of these records occurred in the tax year ended 31 March 2020, the first year following the roll-out of START
From looking at MSD second tier expenditure records, it looks like these values are simply recorded with the wrong sign
For this reason we take the absolute value of the recorded payment.
*/

drop table if exists #ems_ftc;
select distinct ems.snz_uid,
				ems.snz_ird_uid,
				ems.return_period,
				sum(abs(ems.ems_bs_ftc)) as ems_bs_ftc 
into #ems_ftc
from (select snz_uid,
	snz_ird_uid,
	ir_ems_fstc_amt*-1 as ems_bs_ftc,
	case when month(ir_ems_return_period_date)<=3 then datefromparts(year(ir_ems_return_period_date),3,31) 
	else datefromparts(year(ir_ems_return_period_date)+1,3,31) end as return_period
	from $(idicleanversion).ir_clean.ird_ems
	where ir_ems_fstc_amt != 0 and snz_uid !=-11) ems
group by ems.return_period,ems.snz_uid,ems.snz_ird_uid;

RAISERROR('Inserted %d rows in #ems_ftc', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* And create MSD tier 2 FTC and BSTC data */

drop table if exists #t2_ftc_bs ;
select snz_uid,
	msd_ste_start_date as period_start_date, /* Benefit start date */
	msd_ste_end_date as period_end_date, /* Benefit end date */
	msd_ste_daily_gross_amt as daily_gross,
	msd_ste_daily_nett_amt as daily_nett,
	msd_ste_supp_serv_code as t2_serv /* Supplementary service codE */ 
into #t2_ftc_bs
from $(idicleanversion).msd_clean.msd_second_tier_expenditure
where msd_ste_supp_serv_code in ('064','066'); /* Supplementary service code 064 - Family Tax Credit. 066 - Best Start Tax Credit  */

RAISERROR('Inserted %d rows in #t2_ftc_bs', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*
Create a tax year lookup table to sum tier 2 records by tax year 
*/

drop table if exists #tylookup;
declare @startdate date='19990401';
declare @cutoffdate date=datefromparts(year(GETDATE()), 03, 31);

with seq(n) as
(select 0 union all select n+1 from seq
where n+1<DATEDIFF(YEAR,@startdate,@cutoffdate)),
d(d) as
(select dateadd(year,n,@startdate) from seq),
dd as
(select startdate=convert(date,d),
enddate=dateadd(day,-1,dateadd(year,1,d))
from d)
select * 
into #tylookup
from dd;

RAISERROR('Inserted %d rows in #tylookup', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*
Sum T2 values by tax year 
*/

drop table if exists #t2_ftc_bs_ty2;
select distinct a.snz_uid,
	a.t2_serv,
	sum((datediff(day,case when period_start_date>startdate then period_start_date else startdate end,
					case when period_end_date<enddate then period_end_date else enddate end)+1)*daily_gross) as payment,
	b.enddate as return_period
into #t2_ftc_bs_ty2
from #t2_ftc_bs a inner join #tylookup b
on a.period_start_date<=b.enddate and a.period_end_date>=b.startdate
group by a.snz_uid,a.t2_serv,b.enddate;

RAISERROR('Inserted %d rows in #t2_ftc_bs_ty2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

drop table if exists #t2_ftc_bs_ty;
select snz_uid,
	return_period,
	sum(case when t2_serv='064' then payment else 0 end) as t2_ftc,
	sum(case when t2_serv='066' then payment else 0 end) as t2_bstc
into #t2_ftc_bs_ty
from #t2_ftc_bs_ty2
group by snz_uid,return_period
order by snz_uid,return_period;
/* Very similar row count to the EMS records */

RAISERROR('Inserted %d rows in #t2_ftc_bs_ty', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*
Extract WFF data from family return details table
 */

drop table if exists #wff_frd;
select snz_uid,
		partner_snz_uid,
		snz_ird_uid,
		wff_frd_return_period_date as return_period,
		wff_frd_year_month_key,
		/*
		 Take absolute values of entitlements. Some are negative but very few and this seems to be an error? 
		 Entitlements are generally recorded as negative but some BSTC and PTC entitlements are recorded as positive;
		 It is not clear why this is the case :
				- For PTC, positive values increase the total assessed entitlement from 2012 onwards, and it therefore seems reasonable to assume they 
					are true entitlements which should be treated as negative. In 2009 and 2011 they are netted off the total entitlement, while 2010 appears 
					to be mixed. In any case, the number of records affected is very small;
		 		- For BSTC, positive values seem to related to cases where a person is only entitled to BSTC and not other tax credits. The total assessed entitlement
					is also recorded as zero in these cases, which is presumably incorrect.
		 */
		abs(coalesce(wff_frd_fam_bstc_entitlement_amt,0)) as bstc, 
		abs(coalesce(wff_frd_ptc_entitlement_amt,0)) as ptc, 
		abs(coalesce(wff_frd_ctc_entitlement_amt,0)) as ctc, 
		abs(coalesce(wff_frd_fstc_entitlement_amt,0)) as ftc, 
		abs(coalesce(wff_frd_ftc_entitlement_amt,0)) as mftc, 
		abs(coalesce(wff_frd_iwp_entitlement_amt,0)) as iwtc,
		abs(coalesce(wff_frd_fam_assessment_amt,0)) as assessed_ir_tot,
		abs(coalesce(wff_frd_fam_paid_amt,0)) as wff_ir_tot,
		abs(coalesce(wff_frd_winz_paid_amt,0)) as wff_msd_tot, 
		abs(coalesce(wff_frd_final_dr_cr_amt,0)) as tot_adjustment,
		wff_snz_unique_nbr as wff_snz_unique_nbr,
		wff_frd_fb_payment_code as payment_schedule
into #wff_frd
from $(idicleanversion).wff_clean.fam_return_dtls;

RAISERROR('Inserted %d rows in #wff_frd', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* 
We only want to keep the most recently updated record for each snz_uid for each tax year - take the most recent wff_frd_year_month_key
However, note that there may be a minor data quality issue in taking this approach:
	note below from metadata re SNZ_IRD_UID :
		Note: From March 2022 (and affecting data back to 2021-10-31), snz_ird_uid's that have gone bankrupt within a single period will contain 2 rows. You can sum these to find the real total.
	We may be incorrectly treating people who went bankrupt. for these people, we may need to keep both rows and sum their totals. We have not investigated this further, and whether there are
	other duplicates that would need different treatment. However, a low number of bankrupcies in NZ every year may not warrant any further investigation and may not affect WFF tax credit totals.
*/

drop table if exists #wff_frd2;
select a.*
into #wff_frd2
from #wff_frd a
join (select snz_uid,return_period,max(wff_frd_year_month_key) as wff_frd_year_month_key
	from #wff_frd group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.wff_frd_year_month_key=b.wff_frd_year_month_key
order by snz_uid,return_period;

RAISERROR('Inserted %d rows in #wff_frd2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*
 Generally the higher unique nbr seems to be the final assessment (lower numbers missing some entitlements) but in some cases seems to be missing the MSD payment
 we will pick that up from EMS or second tier expenditure though
 */

/* We only want to keep the most recently updated record for each snz_uid for each tax year  */
drop table if exists #wff_frd3;
select a.*
into #wff_frd3
from #wff_frd2 a
join (select snz_uid,return_period,max(wff_snz_unique_nbr) as wff_snz_unique_nbr /* picking the higher unique nbr to remove duplicates */
	from #wff_frd2 group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.wff_snz_unique_nbr=b.wff_snz_unique_nbr
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #wff_frd3', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* No duplicates now.  */

/* Now bring in tax credit data from IR tables, especially IETC which is not in WFF tables */

/*
First IETC from autocalc 
*/

drop table if exists #ac_ietc;

select snz_uid,
	snz_ird_uid,
	ir_ac_return_period_date as return_period,
	ir_ac_iect_enttlmnt_amt as ac_ietc,
	ir_ac_snz_unique_nbr
into #ac_ietc
from  $(idicleanversion).ir_clean.ird_autocalc_information
where ir_ac_iect_enttlmnt_amt is not null and ir_ac_iect_enttlmnt_amt != 0;

RAISERROR('Inserted %d rows into #ac_ietc', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Very few dupes - take the highest unique number 
Alternative could be to take the higher value or to sum the fields, but only very few records so unimportant
 */

drop table if exists #ac_ietc_fin;

select a.*
into #ac_ietc_fin
from #ac_ietc a
join (select snz_uid,return_period,max(ir_ac_snz_unique_nbr) as ir_ac_snz_unique_nbr
	from #ac_ietc group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ac_snz_unique_nbr=b.ir_ac_snz_unique_nbr
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ac_ietc_fin', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* No dupes  */

/*
And now WFF tax credits from IR3 
*/

drop table if exists #ir3_wff;
select snz_uid,
	snz_ird_uid,
	ir_ir3_return_period_date as return_period,
	ir_ir3_snz_unique_nbr,
	ir_ir3_return_version_nbr,
	ir_ir3_ird_timestamp_date,
	ir_ir3_location_nbr,
	ir_ir3_parent_tax_crdt_entl_amt*-1 as ir3_ptc,
	ir_ir3_child_tax_crdt_entl_amt*-1 as ir3_ctc,
	ir_ir3_family_tax_crdt_entl_amt*-1 as ir3_mftc,
	ir_ir3_fam_sup_tax_crdt_entl_amt*-1 as ir3_ftc,
	ir_ir3_in_work_payment_entl_amt*-1 as ir3_iwtc
into #ir3_wff
from  $(idicleanversion).ir_clean.ird_rtns_keypoints_ir3 /* only contains data from YR 2000 to latest year */
where ir_ir3_parent_tax_crdt_entl_amt!=0 OR ir_ir3_child_tax_crdt_entl_amt!=0 OR ir_ir3_family_tax_crdt_entl_amt!=0 OR ir_ir3_fam_sup_tax_crdt_entl_amt!=0 
	OR ir_ir3_in_work_payment_entl_amt!=0;

RAISERROR('Inserted %d rows into #ir3_wff', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

drop table if exists #ir3_wff2;
select a.*
into #ir3_wff2
from #ir3_wff a
join (select snz_uid,return_period,max(ir_ir3_ird_timestamp_date) as ir_ir3_ird_timestamp_date
	from #ir3_wff group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ir3_ird_timestamp_date=b.ir_ir3_ird_timestamp_date
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3_wff2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Still a few dupes. Take max ir_ir3_location_nbr */ 
drop table if exists #ir3_wff3 ;
select a.*
into #ir3_wff3
from #ir3_wff2 a
join (select snz_uid,return_period
,max(ir_ir3_location_nbr) as ir_ir3_location_nbr 
from #ir3_wff2 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ir3_location_nbr=b.ir_ir3_location_nbr
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3_wff_fin', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Still a few dupes. Take max ir_ir3_snz_unique_nbr */ 

drop table if exists #ir3_wff_fin ;
select a.*
into #ir3_wff_fin
from #ir3_wff3 a
join (select snz_uid,return_period
,max(ir_ir3_snz_unique_nbr) as ir_ir3_snz_unique_nbr 
from #ir3_wff3 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ir3_snz_unique_nbr=b.ir_ir3_snz_unique_nbr
order by snz_uid,return_period;

/* all duplicates removed */

/* And WFF tax credits and IETC from PTS */

drop table if exists #pts_wff;
select snz_uid,
	snz_ird_uid,
	ir_pts_snz_unique_nbr,
	ir_pts_return_period_date as return_period,
	ir_pts_parent_tax_crdt_entl_amt*-1 as pts_ptc,
	ir_pts_child_tax_crdt_entl_amt*-1 as pts_ctc,
	ir_pts_family_tax_crdt_entl_amt*-1 as pts_mftc,
	ir_pts_fam_sup_tax_crdt_entl_amt*-1 as pts_ftc,
	ir_pts_in_work_payment_entl_amt*-1 as pts_iwtc,
	ir_pts_bstc_entl_amt*-1 as pts_bstc,
	ir_pts_ind_ern_tax_crdt_entl_amt as pts_ietc,
	ir_pts_timestamp_date
into #pts_wff
from  $(idicleanversion).ir_clean.ird_pts
where ir_pts_parent_tax_crdt_entl_amt!=0 OR ir_pts_child_tax_crdt_entl_amt!=0 OR ir_pts_family_tax_crdt_entl_amt!=0 OR ir_pts_fam_sup_tax_crdt_entl_amt!=0 OR 
	ir_pts_in_work_payment_entl_amt !=0 OR ir_pts_bstc_entl_amt!=0 OR ir_pts_ind_ern_tax_crdt_entl_amt !=0;

RAISERROR('Inserted %d rows into #pts_wff', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO
/* From 2000 to 2020 year */

 
/* some dupes - take the most recent timestamp */

drop table if exists #pts_wff2;

select a.*
into #pts_wff2
from #pts_wff a
join (select snz_uid,return_period,max(ir_pts_timestamp_date) as ir_pts_timestamp_date
	from #pts_wff group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_pts_timestamp_date=b.ir_pts_timestamp_date
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #pts_wff2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Still a few dupes. Take max ir_pts_snz_unique_nbr */

drop table if exists #pts_wff_fin;

select a.*
into #pts_wff_fin
from #pts_wff2 a
join (select snz_uid,return_period
,max(ir_pts_snz_unique_nbr) as ir_pts_snz_unique_nbr 
from #pts_wff2 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_pts_snz_unique_nbr=b.ir_pts_snz_unique_nbr
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #pts_wff_fin', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* all duplicates removed */

/* 
And WFF tax credits from 2000 to 2014 IR3 data 
*/

drop table if exists #ir3b_wff;
select con.snz_uid,
	ir.snz_ird_uid,
	ir.ir_ir3_snz_unique_nbr,
	ir.ir_ir3_return_period_date as return_period,
	ir.ir_ir3_parent_tax_crdt_entl_amt*-1 as ir3b_ptc,
	ir.ir_ir3_child_tax_crdt_entl_amt*-1 as ir3b_ctc,
	ir.ir_ir3_family_tax_crdt_entl_amt*-1 as ir3b_mftc,
	ir.ir_ir3_fam_sup_tax_crdt_entl_amt*-1 as ir3b_ftc,
	ir.ir_ir3_in_work_payment_entl_amt*-1 as ir3b_iwtc,
	ir_ir3_ird_timestamp_date,
	ir_ir3_location_nbr
into #ir3b_wff
from $(idicleanversion).security.concordance con 
inner join idi_adhoc.clean_read_ir.ir_ir3_2000_to_2014 ir /* only contains data from YR 2000 to 2014 TY */
on con.snz_ird_uid=ir.snz_ird_uid
where coalesce(ir_ir3_fam_sup_tax_crdt_entl_amt,ir_ir3_parent_tax_crdt_entl_amt,ir_ir3_child_tax_crdt_entl_amt,ir_ir3_family_tax_crdt_entl_amt,
				ir_ir3_in_work_payment_entl_amt) is not null;

				
RAISERROR('Inserted %d rows into #ir3b_wff', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* take the most recent timestamp */ 

drop table if exists #ir3b_wff2;

select a.*
into #ir3b_wff2
from #ir3b_wff a
join (select snz_uid,return_period,max(ir_ir3_ird_timestamp_date) as ir_ir3_ird_timestamp_date
	from #ir3b_wff group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ir3_ird_timestamp_date=b.ir_ir3_ird_timestamp_date
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3b_wff2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Still a few dupes. Take max ir_ir3_snz_unique_nbr */

drop table if exists #ir3b_wff_fin;
select a.*
into #ir3b_wff_fin
from #ir3b_wff2 a
join (select snz_uid,return_period,max(ir_ir3_snz_unique_nbr) as ir_ir3_snz_unique_nbr from #ir3b_wff2 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.ir_ir3_snz_unique_nbr=b.ir_ir3_snz_unique_nbr
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3b_wff_fin', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* No duplicates */

/* 
And WFF tax credits and IETC from 2013 to 2020 IR3 data 
*/

drop table if exists #ir3c_wff;
select 
	con.snz_uid,
	ir.snz_ird_uid,
	ir.period as return_period,
	case when ir.ietc_tax_credit='Y' then ir.tax_credits else 0 end as ir3c_ietc, /*  only contains data from YR 2013 to  2020 TY */
	ir.total_ftc as ir3c_totftc,
	timestamp,
	location_number,
	effective_date,
	processing_date
into #ir3c_wff
from $(idicleanversion).security.concordance con 
inner join idi_adhoc.clean_read_ir.ir_ir3_2013_to_2020 ir
on con.snz_ird_uid=ir.snz_ird_uid
where ftc_entitlement_amt!=0 OR ptc_entitlement_amt!=0 OR ctc_entitlement_amt!=0 OR mftc_entitlement_amt!=0 OR iwtc_entitlement_amt!=0 OR 
				bs_entitlement_amt!=0 OR total_ftc!=0 OR tax_credits !=0;

RAISERROR('Inserted %d rows into #ir3c_wff', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* some dupes - take the most recent processing_date */

drop table if exists #ir3c_wff2;
select a.*
into #ir3c_wff2
from #ir3c_wff a
join (select snz_uid,return_period,max(timestamp) as timestamp
	from #ir3c_wff group by snz_uid,return_period) as b
	on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.timestamp=b.timestamp
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3c_wff2', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* some dupes - take max effective date */

drop table if exists #ir3c_wff3 ;
select a.*
into #ir3c_wff3
from #ir3c_wff2 a
join (select snz_uid,return_period,max(effective_date) as effective_date from #ir3c_wff2 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.effective_date=b.effective_date
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3c_wff3', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* some dupes - take max location_number */

drop table if exists #ir3c_wff4 ;
select a.*
into #ir3c_wff4
from #ir3c_wff3 a
join (select snz_uid,return_period,max(location_number) as location_number from #ir3c_wff3 group by snz_uid,return_period) b
on a.snz_uid=b.snz_uid and a.return_period=b.return_period and a.location_number=b.location_number
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3c_wff4', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* some dupes left - no more unique values to choose from - we'll remove dups by summing ir3c_ietc and ir3c_totftc */

drop table if exists #ir3c_wff_fin ;
select snz_uid,
snz_ird_uid,
return_period,
SUM(ir3c_ietc) AS ir3c_ietc,
SUM(ir3c_totftc) AS ir3c_totftc,
timestamp,
processing_date
into #ir3c_wff_fin
from #ir3c_wff4
group by snz_uid, snz_ird_uid, return_period,timestamp, processing_date
order by snz_uid,return_period;

RAISERROR('Inserted %d rows into #ir3c_wff_fin', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*  no duplicates */

/* 
Now bring them all together and get rid of null values
 */

drop table if exists #wff_all_source;
select	_all_.snz_uid,
		_all_.return_period,
		coalesce(a.snz_ird_uid,b.snz_ird_uid,d.snz_ird_uid,e.snz_ird_uid,f.snz_ird_uid,g.snz_ird_uid,h.snz_ird_uid) as snz_ird_uid,
		a.partner_snz_uid,
		coalesce(a.bstc,0) as bstc,
		coalesce(a.ptc,0) as ptc,
		coalesce(a.ctc,0) as ctc,
		coalesce(a.ftc,0) as ftc,
		coalesce(a.mftc,0) as mftc,
		coalesce(a.iwtc,0) as iwtc,
		a.assessed_ir_tot,
		a.wff_ir_tot,
		a.wff_msd_tot,
		a.tot_adjustment,
		a.payment_schedule,
		coalesce(b.ems_bs_ftc,0) as ems_bs_ftc,
		coalesce(c.t2_ftc,0) as t2_ftc,
		coalesce(c.t2_bstc,0) as t2_bstc,
		coalesce(d.ac_ietc,0) as ac_ietc,
		coalesce(e.ir3_ptc,0) as ir3_ptc,
		coalesce(e.ir3_ctc,0) as ir3_ctc,
		coalesce(e.ir3_mftc,0) as ir3_mftc,
		coalesce(e.ir3_ftc,0) as ir3_ftc,
		coalesce(e.ir3_iwtc,0) as ir3_iwtc,
		coalesce(f.pts_ptc,0) as pts_ptc,
		coalesce(f.pts_ctc,0) as pts_ctc,
		coalesce(f.pts_mftc,0) as pts_mftc,
		coalesce(f.pts_ftc,0) as pts_ftc,
		coalesce(f.pts_iwtc,0) as pts_iwtc,
		coalesce(f.pts_bstc,0) as pts_bstc,
		coalesce(f.pts_ietc,0) as pts_ietc,
		coalesce(g.ir3b_ptc,0) as ir3b_ptc,
		coalesce(g.ir3b_ctc,0) as ir3b_ctc,
		coalesce(g.ir3b_mftc,0) as ir3b_mftc,
		coalesce(g.ir3b_ftc,0) as ir3b_ftc,
		coalesce(g.ir3b_iwtc,0) as ir3b_iwtc,
		coalesce(h.ir3c_ietc,0) as ir3c_ietc,
		coalesce(h.ir3c_totftc,0) as ir3c_ftc
into #wff_all_source
from (select snz_uid,return_period from #wff_frd3 union
		select snz_uid,return_period from #ems_ftc #ems_ftc union 
		select snz_uid,return_period from #wff_frd3 #t2_ftc_bs_ty union 
		select snz_uid,return_period from #wff_frd3 #ac_ietc_fin union 
		select snz_uid,return_period from #wff_frd3 #ir3_wff_fin union 
		select snz_uid,return_period from #wff_frd3 #pts_wff_fin union 
		select snz_uid,return_period from #wff_frd3 #ir3b_wff_fin union 
		select snz_uid,return_period from #wff_frd3 #ir3c_wff_fin) _all_
left outer join #wff_frd3 a 
on _all_.snz_uid=a.snz_uid and _all_.return_period=a.return_period
left outer join #ems_ftc b
on _all_.snz_uid=b.snz_uid and _all_.return_period=b.return_period
left outer join #t2_ftc_bs_ty c
on _all_.snz_uid=c.snz_uid and _all_.return_period=c.return_period
left outer join #ac_ietc_fin d
on _all_.snz_uid=d.snz_uid and _all_.return_period=d.return_period
left outer join #ir3_wff_fin e
on _all_.snz_uid=e.snz_uid and _all_.return_period=e.return_period
left outer join #pts_wff_fin f
on _all_.snz_uid=f.snz_uid and _all_.return_period=f.return_period
left outer join #ir3b_wff_fin g
on _all_.snz_uid=g.snz_uid and _all_.return_period=g.return_period
left outer join #ir3c_wff_fin h
on _all_.snz_uid=h.snz_uid and _all_.return_period=h.return_period;

RAISERROR('Inserted %d rows into #wff_all_source', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* 
And construct final table
*/

drop table if exists #wff_calc;
select 
	snz_uid, snz_ird_uid,return_period,ems_bs_ftc,
	case when ac_ietc!=0 then ac_ietc when pts_ietc!=0 then pts_ietc else ir3c_ietc end as ietc,
	case when ftc!=0 then ftc when t2_bstc+t2_ftc>0 then ems_bs_ftc*t2_ftc/(t2_bstc+t2_ftc) else 0 end as ftc,
	ptc,
	ctc,
	iwtc,
	mftc,
	case when bstc!=0 then bstc when t2_bstc+t2_ftc>0 then ems_bs_ftc*t2_bstc/(t2_bstc+t2_ftc) else 0 end as bstc,
	/* Tax credits paid by IR */
	coalesce(wff_ir_tot,0) as ir_paid,
	/* Tax credits paid by MSD */
	case when ems_bs_ftc>0 then ems_bs_ftc else coalesce(wff_msd_tot,0) end as msd_paid
into #wff_calc
from #wff_all_source;

RAISERROR('Inserted %d rows into #wff_calc', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/* Drop the final table if it exists */
drop table if exists #wide_tax_credits_taxyr; 

select 
	snz_uid,
	snz_ird_uid,
	CAST(ftc AS decimal(19,2)) as ftc, /* 'Family Tax Credit' */
	CAST(iwtc AS decimal(19,2)) as iwtc, /* 'In Work Tax Credit' */
	CAST(ctc AS decimal(19,2)) as ctc, /* 'Child Tax Credit' */
	CAST(ptc AS decimal(19,2)) as ptc, /* 'Parental Tax Credit' */
	CAST(mftc AS decimal(19,2)) as mftc, /* 'Minimum Family Tax Credit' */
	CAST(ietc AS decimal(19,2)) as ietc, /* 'Independent Earner Tax Credit' */
	CAST(bstc AS decimal(19,2)) as bstc, /* 'Best Start Tax Credit' */
	/* Total WFF tax credits */
	round(ftc+ptc+ctc+iwtc+bstc+mftc,2) as tot_wff,
	/* Total WFF and IETC */
	round(coalesce(ietc+ftc+ptc+ctc+iwtc+bstc+mftc,0),2) as tot_taxcredit,
	round(coalesce(ir_paid+ietc,0),2) as ir_paid,
	round(coalesce(msd_paid,0),2) as msd_paid,
	/* Debit or credit at end of tax year */
	round(coalesce(ftc+ptc+ctc+iwtc+bstc+mftc-ir_paid-msd_paid,0),2) as eoy_drcr,
	dateadd(day,1,dateadd(year,-1,return_period)) as tax_credit_start_date,
	return_period as tax_credit_end_date
into #wide_tax_credits_taxyr
from #wff_calc
where round(coalesce(ietc+ftc+ptc+ctc+iwtc+bstc+mftc,0),2)>0;

RAISERROR('Created %d rows into #wide_tax_credits_taxyr', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

/*
And construct the final module
*/

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

  CREATE TABLE $(targetdb).$(targetschema).$(projprefix)_tax_credits (
	 snz_uid INT NOT NULL
	,period_start_date DATE NOT NULL
	,period_end_date DATE NOT NULL
	,source_frequency CHAR(7) NOT NULL
	,income_source CHAR(35) NOT NULL
	,taxable_income_indicator TINYINT NOT NULL 
	,gross_income DECIMAL(19,2) NULL
	);

INSERT INTO $(targetdb).$(targetschema).$(projprefix)_tax_credits
 

SELECT 
[snz_uid]
,[period_start_date]
,[period_end_date]
,'annual' as source_frequency
,income_source
,0 as taxable_income_indicator
,gross_income
FROM (
select 
	snz_uid,
	ftc as [Family Tax Credit],
	iwtc as [In Work Tax Credit],
	ctc as [Child Tax Credit],
	ptc as [Parental Tax Credit],
	mftc as [Minimum Family Tax Credit],
	ietc as [Independent Earner Tax Credit],
	bstc as [Best Start Tax Credit],
	tax_credit_start_date as 'period_start_date',
	tax_credit_end_date  as 'period_end_date'
FROM #wide_tax_credits_taxyr
  ) source_table
  UNPIVOT
	(gross_income FOR income_source IN
		([Family Tax Credit]
      ,[In Work Tax Credit]
      ,[Child Tax Credit]
      ,[Parental Tax Credit]
      ,[Minimum Family Tax Credit]
      ,[Best Start Tax Credit]
	  ,[Independent Earner Tax Credit]
	  )
	) AS unpvt
	WHERE gross_income <> 0;

RAISERROR('Created table in Sandpit with %d rows', 0, 1, @@ROWCOUNT) WITH NOWAIT;
GO

 

ALTER TABLE $(targetdb).$(targetschema).$(projprefix)_tax_credits
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=PAGE);

DROP TABLE IF EXISTS #ems_ftc      ;
DROP TABLE IF EXISTS #t2_ftc_bs	  ;
DROP TABLE IF EXISTS #tylookup	  ;
DROP TABLE IF EXISTS #t2_ftc_bs_ty2;
DROP TABLE IF EXISTS #t2_ftc_bs_ty ;
DROP TABLE IF EXISTS #wff_frd	  ;
DROP TABLE IF EXISTS #wff_frd2	  ;
DROP TABLE IF EXISTS #wff_frd3	  ;
DROP TABLE IF EXISTS #ac_ietc	  ;
DROP TABLE IF EXISTS #ac_ietc_fin  ;
DROP TABLE IF EXISTS #ir3_wff	  ;
DROP TABLE IF EXISTS #ir3_wff2	  ;
DROP TABLE IF EXISTS #ir3_wff_fin  ;
DROP TABLE IF EXISTS #pts_wff	  ;
DROP TABLE IF EXISTS #pts_wff2	  ;
DROP TABLE IF EXISTS #pts_wff_fin  ;
DROP TABLE IF EXISTS #ir3b_wff	  ;
DROP TABLE IF EXISTS #ir3b_wff2	  ;
DROP TABLE IF EXISTS #ir3b_wff_fin ;
DROP TABLE IF EXISTS #wff_all_source;
DROP TABLE IF EXISTS #wff_calc	  ;
DROP TABLE IF EXISTS #wide_tax_credits_taxyr