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

Purpose of the Income Support Payments Module

This module consolidates the existing income support code module tables into a single table for calculating income from income support.

Key concepts

Income support payments

For detail on income support payments and the construction please refer to the MSD income support payments code module.

Total income spell

A total income spell is the minimum period per income type. Income support payments spells can last from one day to multiple years.

Source frequency

Source frequency is the minimum period that each income type is stored in the data. The source frequency for income support payments is daily.

Gross income amount

Gross income amount is income before tax (if applicable) that a person receives. For income support payments, only main benefit payments, which includes retirement benefits are taxed.

Observed tax payments

Tax recorded as paid to IRD in the IDI. The amount of tax that has been has been deducted from source (eg PAYE and tax filing). Note that observed tax deduction is not the same as actual tax liability since this can only be calculated once all income sources have been identified over a tax period. This information has not been included in the initial version of the module.

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

Community of Interest

The Community of Interest is contained in the income support payments code module MSD Income Support Payments .

Period

The income support payments information is from 1990 onwards.

Module Business Rules

1. Merge the existing income support payment datasets
   a. msd_ise_main_benefit_YYYYMM
   b. msd_ise_supplementary_YYYYMM
   c. msd_ise_ad_hoc_YYYYMM
   d. msd_ise_retirement_benefit_YYYYMM
2. Calculate the following columns
   a. start_date from payment_start
   b. end_date from payment_end
   c. income_source_type based on benefit_levl2 value
   d. income_source based on comparable_benefit_lvl1
   e. taxable_income_indicator (1,0) set to 1 for msd_ise_main_benefit_YYYYMM, all other income support payments are not taxed
   f. daily_rate from payment_rate_gross
   g. number_of_days as the number of days between payment_start and payment end
   h. source_frequency as ‘daily’
   i. gross_income as the daily_rate times the number_of_days.

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

This module required the build of the income support payment module.

IDI_Community.cm_read_MSD_ISE_MAIN_BENEFIT.msd_ise_main_benefit_YYYYMM
IDI_Community.cm_read_MSD_ISE_SUPPLEMENTARY.msd_ise_supplementary_YYYYMM
IDI_Community.cm_read_MSD_ISE_AD_HOC.msd_ise_ad_hoc_YYYYMM
IDI_Community.cm_read_MSD_ISE_RETIREMENT_BENEFIT.msd_ise_retirement_benefit_YYYYMM

Outputs

The code creates a view.

 {targetdb}.{targetschema}.{projprefix}_inc_support_paymt_{refresh}

Variable Descriptions

Module Version & Change History

Code

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

:setvar targetdb "IDI_Usercode"
:setvar sourcedb "IDI_Community"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "income_t1"
:setvar idicleanversion "IDI_Clean_202403"
:setvar idimetadataversion "IDI_Metadata_202403"
:setvar yyyymm "202403"

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

/* This code is structured into 4 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. Retirement benefit data from ISE First Tier Expenditure, reallocated using benefit Level 1 Codes.
MSD tax credits not included here since they tax credits are part of another module
*/

 
/* Drop the final output if it exists */
IF OBJECT_ID('$(targetschema).$(projprefix)_inc_support_paymt','V') IS NOT NULL
DROP VIEW $(targetschema).$(projprefix)_inc_support_paymt;
GO

CREATE VIEW $(targetschema).$(projprefix)_inc_support_paymt AS 
 

SELECT
	snz_uid
	,[payment_start] as period_start_date
	,[payment_end] as period_end_date
	,'daily' as source_frequency
	,[benefit_lvl2] as income_source_type
	,[comparable_benefit_lvl1]  as income_source
	,1 as taxable_income_indicator /* main benefits are taxable */
	,CAST(CAST([payment_rate_gross] AS decimal(19,2)) * (DATEDIFF(day,[payment_start],[payment_end])+1) AS decimal(19,2)) as gross_income
FROM $(sourcedb).$(targetschema).$(projprefix)_ise_main_benefit_$(yyyymm)
UNION ALL
SELECT
	snz_uid
	,[payment_start] as period_start_date
	,[payment_end] as period_end_date
	,'daily' as source_frequency
	,[benefit_lvl2] as income_source_type
	,[comparable_benefit_lvl1]  as income_source
	,0 as taxable_income_indicator /* supp benefits not taxable */
	,CAST(CAST([payment_rate_gross] AS decimal(19,2)) * (DATEDIFF(day,[payment_start],[payment_end])+1) AS decimal(19,2)) as gross_income
FROM $(sourcedb).$(targetschema).$(projprefix)_ise_supplementary_$(yyyymm)
UNION ALL
SELECT
	snz_uid
	,[payment_start] as period_start_date
	,[payment_end] as period_end_date
	,'daily' as source_frequency
	,[benefit_lvl2] as income_source_type
	,[comparable_benefit_lvl1]  as income_source
	,0 as taxable_income_indicator /* adhoc benefits not taxable */
	,CAST(CAST([payment_rate_gross] AS decimal(19,2)) * (DATEDIFF(day,[payment_start],[payment_end])+1) AS decimal(19,2)) as gross_income
FROM $(sourcedb).$(targetschema).$(projprefix)_ise_ad_hoc_$(yyyymm)
UNION ALL
SELECT
	snz_uid
	,[payment_start] as period_start_date
	,[payment_end] as period_end_date
	,'daily' as source_frequency
	,[benefit_lvl2] as income_source_type
	,[comparable_benefit_lvl1]  as income_source
	,1 as taxable_income_indicator /* retirement benefits, which are also main benefits are taxable */
	,CAST(CAST([payment_rate_gross] AS decimal(19,2)) * (DATEDIFF(day,[payment_start],[payment_end])+1) AS decimal(19,2)) as gross_income
FROM $(sourcedb).$(targetschema).$(projprefix)_ise_retirement_benefit_$(yyyymm);

 
G