Module Output

SQL:[IDI_Community].[hsg_emergency_housing_spells].[emergency_housing_spells_YYYYMM]
SAS: libname cmeh dsn=IDI_community_srvprd schema=hsg_emergency_housing_spells; run ;
How to access a code module in the Data Lab:Read here
All workshop recordings : Find here

Purpose

The purpose of this module is to construct spells of Emergency Housing Special Needs Grant (EH SNG) received by a primary applicant. These spells can be used to produce estimated counts of people who are receiving EH SNG as a primary applicant at a given point in time. The module can be used to produce data for research into the extent of provision of EH SNG and those who have accessed it.

Key Concepts

Emergency Housing eligibility/criteria (current as at April 2025)

Emergency housing (EH) is short-term accommodation and individuals must meet certain criteria:

• Have serious housing need/nowhere to stay
• Have nowhere to stay that night (on the day of application) or in the next 7 nights, and
• Agree to meet responsibilities from the 8th night (if the individual is in EH for more than 7 nights).

Emergency housing may also be available for those escaping family violence or leaving prison when they meet the above criteria of having nowhere to stay.

Residency

If a person is getting a main benefit (e.g. Jobseeker Support), NZ Super or Veteran’s Pension from MSD , they meet the residency criteria.
If they don’t get one of the above benefits, they need to be considered ordinarily resident in New Zealand and meet one of these residency criteria:

• is a New Zealand citizen
• has a resident visa or permanent resident visa and has lived in NZ continuously for at least 2 years since getting it
• is recognised as a refugee by Immigration New Zealand
• is recognised as a protected person by Immigration New Zealand, or
• has a Special Needs Grant because they came to New Zealand on a temporary visa to be with a NZ partner and the relationship ended due to domestic violence.

Housing need circumstances

• If a person is considered to have caused or contributed toward their need for emergency housing, this may be taken into consideration.
  For example: moving out of accommodation without a good reason and didn’t have another place to move into.
• Emergency housing may also be available for those escaping family violence or leaving prison when they meet the above criteria of having nowhere to stay

Income and assets

Generally, applicant households need to have income and/or assets under specified amounts the current values as at April 2025 are as followed:

• income under
  • $625.16 a week (before tax) if you’re Single with no children, and aged between 16 and 17 years old
  • $718.48 a week (before tax) if you’re Single with no children, and aged 18+
  • $1,043.60 a week (before tax) if you’re a Couple with or without children
  • $871.86 a week (before tax) if you’re a Sole Parent with 1 child
  • $918.54 a week (before tax) if you’re a Sole Parent with 2+ children
• cash assets under
  • $1,368.68 if you’re Single with no children
  • $2,280.58 if you’re a Couple (with or without children), or a Sole Parent
    If they have income or assets worth more than this, they may still qualify for emergency housing. It depends on the situation.

References

1. Work and income - Emergency Housing: www.workandincome.govt.nz/housing/nowhere-to-stay/emergency-housing/who-can-get-it.html
2. Monthly MSD EH report on numbers/grants: www.msd.govt.nz/about-msd-and-our-work/publications-resources/statistics/housing/monthly-housing-reporting.html

Development Team

Module Business Rules

1. If the primary applicant has multiple EH grants, the grants are ordered chronologically.
   If the days between two grants (based on the MSD decision date for a grant) for a client is longer than 28 days, it is treated as a new spell.
   Where the MSD decision date between grants is 28 days or less, they are considered to form a single spell.
2. For each unique snz_uid (person) the spells are ordered chronologically and numbered with a spell_nbr of 1 indicating the earliest emergency housing spell.
3. The spell start date is determined by the earliest grant decision date in a spell.
4. The spell end date is determined by looking at the latest/final grant date [MSD decision date] and adding +21 days to this date.
5. The spell duration is the number of days between the spell start date and the spell end date (end date business rule detailed above)

Spell nbr

• The spell number identifier for a person that identifies that spell’s chronological place for all EH spells that an individual has had.
  For example the EH spell with the earliest date for a person will have a spell_nbr of 1, the next spell for that person (if there are multiple) chronologically will have a spell_nbr of 2 etc.

Spell start

• The spell start for an emergency housing spell is derived from the grant decision date msd_tte_decision_date, in the case of consecutive payments that make up one spell for an individual it is the earliest spell_start_date for that spell.

Spell end

• The spell end for an emergency housing spell is derived from the last grant date (grant decision) in a spell sourced from msd_tte_decision_date. It is defined as the last grant date (in a spell) + 21 days. This follows MSD business rules.

Payment amount

• The total amount paid over a spell, calculated by summing all EH-SNG payments [msd_tte_pmt_amt] during in the spell’s period.

Open Issues/Comments

1. External data matching issue: This analysis uses the EH code module which builds from existing MSD data, using MSD business rules to calculate spells in EH.
   Limitations in the current data provision mean housing spells cannot be exactly replicated within the IDI and exits from EH may be undercounted.
   This issue will be resolved once the new provision goes into the IDI during the October refresh.
2. Note on duplicates:
   A client may have multiple grants on the same day. However, MSD advises to consider them all legitimate
   as these occur due to the frontline practices and to match publicly published figures as close as possible.
   Count all records of grants in each spell.

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 refresh version of IDI Clean 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.

Dependencies

{idicleanversion}.[msd_clean].[msd_third_tier_expenditure]

Outputs

{outputs}{targetdb}.{targetschema}.[eh_emergency_spells]

Variable Descriptions

Module Version & Change History

Code

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

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

/* Delete the database object if it already exists */
DROP
VIEW IF EXISTS [$(targetschema)].[eh_emergency_spells];
GO

CREATE
VIEW [$(targetschema)].[eh_emergency_spells] AS 

/* 1. Note on duplicates; 
   A client may have multiple grants on the same day. However, MSD advises to consider them all legitimate 
   as these occur due to the frontline practices and to match publicly published figures as close as possible. 
   Count all records of grants in each spell. */
WITH base_data AS(
    SELECT [snz_uid]
        , [snz_msd_uid]
        , [snz_swn_nbr]
        , [msd_tte_tt_pmt_nbr]
        , [msd_tte_decision_date] AS [spell_start_date]
        , [msd_tte_parent_svc_code]
        , [msd_tte_lump_sum_svc_code]
        , [msd_tte_pmt_rsn_type_code]
        , [msd_tte_pmt_amt] AS [payment_amount]
        , [msd_tte_recoverable_ind]
        , [msd_tte_rcmd_dist_code]
    FROM [$(idicleanversion)].[msd_clean].[msd_third_tier_expenditure]
    WHERE [msd_tte_pmt_rsn_type_code] = '855'
)
    , 
/* 2. Identify Spells;
   If the days between two grants for a client is longer than 28 days, treat it as a new spell. */
identify_spells AS(
    SELECT *
        , CASE 
            WHEN DATEDIFF(DAY, LAG([spell_start_date]) OVER (PARTITION BY [snz_uid], [snz_msd_uid] 
															 ORDER BY [spell_start_date]), [spell_start_date]) > 28 
            OR LAG([spell_start_date]) OVER (PARTITION BY [snz_uid], [snz_msd_uid] 
											 ORDER BY [spell_start_date]) IS NULL 
            THEN 1 ELSE 0 
        END AS [new_spell]
    FROM base_data
)
    , 
/* 2a. Add a spell number for each client */
spell_nbr AS(
    SELECT *
        , SUM([new_spell]) OVER (PARTITION BY [snz_uid], [snz_msd_uid] 
							   ORDER BY [spell_start_date] ROWS UNBOUNDED PRECEDING) AS [spell_nbr]
    FROM identify_spells
)
    , 
/* 3. Clean up the attributes of each spell;
   The earliest spell start date is the spell start date
   The sum of all payments is the total payment amount for each spell
   Use the information from the latest payment_nbr within the spell for its attributes */
spell_maxpaymentnbr AS(
    SELECT [snz_uid]
        , [snz_msd_uid]
        , [spell_nbr]
        , MIN([spell_start_date]) AS [spell_start_date]
        , MAX([spell_start_date]) AS [max_spell_start_date]
        , COUNT(*) AS [spell_grants_count]
        , SUM([payment_amount]) AS [payment_amount]
        , MAX([msd_tte_tt_pmt_nbr]) AS [max_payment_nbr]
    FROM spell_nbr
    GROUP BY [snz_uid]
        , [snz_msd_uid]
        , [spell_nbr]
)
    , spell_info AS(
    SELECT a.[snz_uid]
        , a.[snz_msd_uid]
        , a.[spell_nbr]
        , a.[spell_start_date]
        , a.[max_spell_start_date]
        , a.[spell_grants_count]
        , a.[payment_amount]
        , b.[msd_tte_parent_svc_code] AS [benefit_code]
        , b.[msd_tte_recoverable_ind] AS [recoverable_ind]
        , b.[msd_tte_rcmd_dist_code] AS [msd_district_code]
    FROM spell_maxpaymentnbr a
    LEFT JOIN spell_nbr b
    ON a.[snz_uid] = b.[snz_uid]
    AND a.[snz_msd_uid] = b.[snz_msd_uid]
    AND a.[max_payment_nbr] = b.[msd_tte_tt_pmt_nbr]
)
/* 4. Finalise the table with the spell end date and spell duration
   spell end date is 21 days from the max spell date within the spell*/

SELECT [snz_uid]
    , [snz_msd_uid]
    , [spell_nbr]
    , [spell_start_date]
    , DATEADD(DAY, 21, [max_spell_start_date]) AS [spell_end_date]
    , DATEDIFF(DAY, [spell_start_date], DATEADD(DAY, 21, [max_spell_start_date])) AS [spell_duration]
    , [spell_grants_count]
    , [payment_amount]
    , [recoverable_ind]
    , [benefit_code]
    , [msd_district_code]
FROM spell_info

;
GO