Module Output

SQL:[IDI_Community].[hlth_mha_contacts].[mha_contacts_YYYYMM]
SAS: libname cmmhac dsn=IDI_community_srvprd schema=hlth_mha_contacts; run ;
How to access a code module in the Data Lab:Read here

Purpose

This module aims to identify secondary specialist mental health and addiction services (activities) by individuals in New Zealand. These activities can be used to produce estimated counts of secondary specialist mental health and addiction activities accessed by service users, estimated counts of service users by provider type (district or non-Government organisation), the type of service that was provided (contact, face-to-face contact, addiction service vs mental health service and bednights).

Mental health and addiction concepts:

Below we list concepts that researchers commonly wish to operationalise from IDI data when analysing mental health and addiction service use, need for services, and the prevalence of mental health conditions. The list includes notation on whether it is possible to measure the concept well with the data currently available in the IDI, if we could measure it with existing data that is not yet in the IDI, and other notes regarding data limitations and missing information. When scoping this code module, we considered whether these concepts can be validly measured using current data in the IDI. Most concepts are poorly measured using current data and so this module focuses on secondary specialist mental health and addiction service contacts only. We do not want to encourage the use of other data sources while there remain significant issues with bias and missing data, as described below. This position may be revised in future, as the data improves. We acknowledge that researchers may wish to add data from other sources to the dataset created by this code module. We encourage anyone who does so to pay careful attention to the issues below.

This code creates spells of secondary specialist mental health and addiction service contacts (3.1a).

Key Concepts

• Person: the person that utilises a secondary specialist mental health or addiction service.
• Activities: the mental health and addiction activity a person interacts with.
• Contact: mental health and addiction activities provided in a community or outpatient setting.
• Face-to-face contact: the person is physically present at a mental health and addiction service provided in a community or outpatient setting. Face-to-face activities exclude care coordination, contact with family/whanau, written correspondence, telephone calls and text messages.
• Bednight: identifies where the person is occupying a bed at midnight. A bednight is assumed to include all care provided to the person occupying the bed. Bednights are generally provided in inpatient and residential settings. A leave night (where a person spends the night away from a residential service) supersedes a bednight. Actual bednights are calculated by subtracting the number of leave nights from the number of bednights in a spell.

References & Contacts

1. Mental health and addiction service web tool (Health New Zealand) https://www.tewhatuora.govt.nz/for-health-professionals/data-and-statistics/mental-health-and-addiction/service-use-web-tool

Development team

Module Business Rules

1. unique_ref_id: the unique record identifier for secondary specialist mental health and addiction services. This is a concatenation of moh_mhd_organisation_id_code (moh_mhd_organisation_id_code) and moh_mhd_referral_id_code (moh_mhd_referral_id_code).
2. mha_activity_id: activity IDs are only unique when used in combination with the organisation ID (moh_mhd_organisation_id_code) and referral ID (moh_mhd_referral_id_code). Count distinct of this variable will provide the number of mental health and addiction activities. For where the activity is a bednight, several activities may have been merged together so the combination of moh_mhd_referral_id_code and organisation ID (moh_mhd_organisation_id_code) is used (which is the unique_ref_id variable) so for bednights this column will be allocated a NULL value.
3. service_provider_type: the service provider type is an indicator to identify the service provider (HealthNZ and Non-Government Organisation). All service providers that are non-DHB organization types are classified as NGOs.
4. addiction_services_flag: a Y/N flag indicating if the service is an addiction service or not. This can be used to include or exclude addiction services. Value is Y where the team type code is ‘03’ or ‘11’, or activity type code are T16, T17, T18, T19, T20 or T48. For bednights that may include both mental health and addiction related service, the referral is flagged as Y if any of the bednight spells were addiction service related.
5. contact_flag: a Y/N flag to identify if the activity was a contact. This is Y where the activity unit type is CON and the activity type code is not T35.
6. contact_face_to_face_flag: a Y/N flag to identify if the activity was a face-to-face contact. This is Y where the activity unit type is CON, and the activity type code is not T08, T32, T33, T35, T37 or T52, and the activity setting code is not WR, PH, SM and OM.
7. bednight_flag: a Y/N flag indicating if the activity was a bednight. This is Y where the activity unit type is BED.
8. bednight_type: the bednight type classifies the type of bednight, including inpatient bednight (activity type code T02, T03 or T04), forensic bednight (activity type code T11, T12, T13 or T14) and other bednight (all other bednight/leavenight activity type codes other than inpatient or forensic).
9. number_of_bednights: to be used in conjunction with the bednight flag. This is the count of number of midnights for records where the bednight_flag = ‘Y’.
10. number_of_leavenights: to be used in conjunction with the number of bednights variable. Calculated for the number of midnights that are crossed per unique record of organisation ID (moh_mhd_organisation_id_code) and referral ID (moh_mhd_referral_id_code) for each bednight type where activity unit type is LEA. To calculate the total number of bednights in a spell, subtract number_of_leavenights from number_of_bednights.
11. activity_spell_start_date and activity_spell_end_date: start and end dates of the activity. Where the activity is a bednight, for each unique record of organisation ID (moh_mhd_organisation_id_code) and referral ID (moh_mhd_referral_id_code) the minimum moh_mhd_activity_start_date and the maximum moh_mhd_activity_end_date are returned to determine the length of the spell that the bednights and leave nights are attributed to.
12. activity_type_code: the mental health and addiction activity the person interacted with. Where the activity is a bednight, the activity type code is assigned to the relevant classification group for activity type codes - Inpatient and Forensic bednights, all other bednight types that do not correspond to either of these categories gets a value of ‘Other’.

Note, activities where team types are 24 (Integrated Primary Access and Choice team) and 26 (Intellectual disability team) are excluded from the Secondary specialist mental health and addiction service contacts and bednights output table. This is in line with the decision made by MHWC to exclude services provided by these teams that aren’t considered a mental health or addiction service based on advice from subject matter experts.

Open Issues/Comments

1. Programme for the Integration of Mental Health Data (PRIMHD) contains data about Health New Zealand funded mental health and addiction service activity and outcomes. The data is collected from the former district health boards and non-governmental organisations.

• PRIMHD is generally extracted as financial years (as opposed to calendar years). National Collection Annual Maintenance Programme (NCAMP) changes take effect from 1 July each year. If any changes are made to codes used in PRIMHD, the data will be consistent within a financial year.
• PRIMHD data does not include information on:
  • the provision of primary mental health and addiction services including Access and Choice services
  • Secondary mental health services funded by other government departments, e.g. funded by the Ministry of Social Development
  • Problem gambling
  • People with a mental health condition who do not access services
  • Privately funded mental health and addiction services.

2. Mental health and addiction services for older people are funded as mental health and addiction services in the Northern and Midland regions. In the Southern and Central regions, they are funded as disability support services. PRIMHD mainly captures mental health and addiction services and occasionally captures data on disability support services. Data on people utilizing services aged over 65 (including psychogeriatric services) is incomplete.

3. There are known under-reporting of data where some organisations have breaks in reporting and/or incomplete data. In 2008, districts began reporting to PRIMHD and in addition an increasing number of non-government organisations began reporting to the PRIMHD database. Shifts or patterns in the data may reflect the gradual adaptation of non-government organisations into the PRIMHD database instead of any trend in mental health and addiction service use. Although non-government organisation data is still incomplete, Health New Zealand (Te Whatu Ora) considers it complete enough for comparison across time from 1 July 2012 onwards.

• Known under-reporting include:
  • diagnosis data in PRIMHD is incomplete
  • activity volumes for Waikato district are slightly low in 2022/23
  • Bay of Plenty district has some issues with duplicated activity data which may mean activity volumes are over-inflated in some time periods
  • Canterbury district has not reported any supplementary consumer record data to PRIMHD in 2022/23
  • a number of non-government organisations have incomplete data in PRIMHD. For the 2022/23 period, 15 out of 198 non-government organisations have lower than expected activity volumes.

4. Diagnosis data is incomplete and has not been included in this code module. Caution should be used if diagnosis data is matched to this code module data.

• Health New Zealand does not require a diagnosis within the first 3 months of treatment. This means that there may be no diagnosis recorded for a large number of people who only received short-term care.
• Many districts also submit a large number of non-specific diagnoses such as ‘diagnosis deferred’ and ‘no specific diagnosis’. These codes are used too often to be considered credible. Approximately 26% (found on Te Whatu Ora website) of PRIMHD diagnoses recorded in 2022/23 were non-specific.
• Diagnoses in PRIMHD are not tied to a particular service contact - they are separate. Diagnoses have a start date (of when the diagnosis was first given) and an end date. These dates roughly correspond with periods of service use but they are not tied to specific visits. A diagnosis time period may contain one or many service contacts and conversely a given service contact may have one, several, or no ‘active’ diagnoses at the date of service/activity.

5. Referral start and end dates have not been included in this code module output. PRIMHD data in the IDI does not contain entries for referrals that are not associated with activities. If referral start and end dates are required for your analysis, the code module dataset can be linked to PRIMHD to add them to your output.

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.

Dependencies

{idicleanversion}.moh_clean.primhd
{idimetaversion}.moh_primhd.organisation_code

Output

{targetdb}.{targetschema}.mha_contacts

Variable Descriptions

Module Version & Change History

Code

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

use $(targetdb);
GO

/* 0. All contacts (excluding bednights). */
DROP TABLE IF EXISTS #tmp_all_contacts;

SELECT snz_uid
    , snz_moh_uid
    , CONCAT(moh_mhd_referral_id_code,moh_mhd_organisation_id_code) AS unique_ref_id
    , CONCAT(moh_mhd_organisation_id_code,moh_mhd_referral_id_code,moh_mhd_activity_id_code) AS mha_activity_id
    , moh_mhd_organisation_id_code
    , moh_mhd_activity_start_date AS activity_spell_start_date
    , moh_mhd_activity_end_date AS activity_spell_end_date
    , moh_mhd_activity_type_code AS activity_type_code
    , CASE 
			WHEN org.organisation_type = 'District Health Board (DHB)' THEN 'HealthNZ'
			ELSE 'NGOs'
		END AS service_provider_type
    , CASE
			WHEN moh_mhd_team_type_code IN ('03','11') OR moh_mhd_activity_type_code IN ('T16','T17','T18','T19','T20','T48') THEN 'Y'
			ELSE 'N'
		END AS addiction_services_flag
    , CASE
			WHEN moh_mhd_activity_unit_type_text = 'CON' AND moh_mhd_activity_type_code <> 'T35' THEN 'Y'
			ELSE 'N'
		END AS contact_flag
    , CASE
			WHEN moh_mhd_activity_type_code NOT IN ('T08','T32','T33','T35','T37','T52') AND moh_mhd_activity_setting_code NOT IN ('WR','PH','SM','OM') THEN 'Y'
			ELSE 'N'
		END AS contact_face_to_face_flag
    , 'N' AS bednights_flag
    , 'NOT bednight' AS bednights_type
    , NULL AS number_of_bednights
    , NULL AS number_of_leavenights
INTO #tmp_all_contacts
FROM [$(idicleanversion)].[moh_clean].[primhd]
LEFT JOIN [$(idimetaversion)].[moh_primhd].[organisation_code] org
ON moh_mhd_organisation_id_code = org.organisation_id
WHERE moh_mhd_activity_unit_type_text = 'CON'
AND moh_mhd_activity_type_code <> 'T35'
AND moh_mhd_team_type_code NOT IN ('24','26') ;

/* 1. Extract all inpatient bednight related activities (all bednights and leave away from the bed). */

DROP TABLE IF EXISTS #tmp_bednights;

SELECT DISTINCT snz_uid
    , snz_moh_uid
    , moh_mhd_organisation_id_code
    , CONCAT(moh_mhd_referral_id_code,moh_mhd_organisation_id_code) AS unique_ref_id
    , moh_mhd_activity_start_datetime
    , moh_mhd_activity_end_datetime
    , moh_mhd_activity_unit_type_text
    , moh_mhd_activity_type_code
    , CASE WHEN moh_mhd_team_type_code IN ('03','11') OR moh_mhd_activity_type_code IN ('T16','T17','T18','T19','T20','T48') THEN 1
			ELSE 0 END AS addiction_flag
INTO #tmp_bednights
FROM [$(idicleanversion)].[moh_clean].[primhd]
WHERE moh_mhd_activity_unit_type_text IN ('BED','LEA')
AND moh_mhd_activity_unit_count_nbr >= 0
AND moh_mhd_team_type_code NOT IN ('24','26') ;

/* 2. Create a date calendar template for bednight calculations - get midnights at start of each day over years. */
DROP TABLE IF EXISTS #dates;

CREATE TABLE #dates(
    dates DATETIME
        , 
    PRIMARY KEY(
        dates
    )
);
 DECLARE @dIncr DATETIME = '2008-07-01' DECLARE @dEnd DATETIME 

SELECT DISTINCT @dEnd = MAX(moh_mhd_activity_end_date)
FROM [$(idicleanversion)].[moh_clean].[primhd]

 WHILE(
    @dIncr < @dEnd
)BEGIN
INSERT INTO #dates(
    dates
)
VALUES(
    @dIncr
)
SELECT @dIncr = DATEADD(DAY,1, @dIncr) END

/* 3. Any calendar night where there exists an inpatient bednight that starts before or on and ends after this 00.00 and where there does not also exist a leave activity that also starts before and ends after this 00.00. */
DROP TABLE IF EXISTS #tmp_inpatient_night;

SELECT a.unique_ref_id
    , dates
    , a.addiction_flag
    , CASE WHEN b.unique_ref_id IS NULL THEN dates END AS bed_actual
    , CASE WHEN b.unique_ref_id IS NOT NULL THEN dates END AS leave
INTO #tmp_inpatient_night
FROM(
    SELECT unique_ref_id
        , dates
        , MAX(addiction_flag) AS addiction_flag
    FROM(
        SELECT a.unique_ref_id
            , a.moh_mhd_activity_start_datetime
            , a.moh_mhd_activity_end_datetime
            , addiction_flag
            , c.dates
        FROM #tmp_bednights a
        JOIN #dates c
        ON a.moh_mhd_activity_start_datetime <= c.dates
        AND a.moh_mhd_activity_end_datetime > c.dates
        WHERE moh_mhd_activity_type_code IN ('T02','T03','T04')
    )a
    GROUP BY a.unique_ref_id
        , dates
)a
LEFT JOIN #tmp_bednights b
ON a.unique_ref_id = b.unique_ref_id
AND b.moh_mhd_activity_start_datetime <= a.dates
AND b.moh_mhd_activity_end_datetime > a.dates
AND b.moh_mhd_activity_unit_type_text IN ('LEA')

ORDER BY a.unique_ref_id ;

/* 4. Count calendar nights. */
DROP TABLE IF EXISTS #tmp_inpatient_nightcounts_a;

SELECT a.unique_ref_id
    , 'Inpatient' AS bednights_type
    , MIN(a.dates) AS activity_spell_start_date
    , MAX(a.dates) AS activity_spell_end_date
    , COUNT(DISTINCT a.dates) AS bed_total
    , MAX(addiction_flag) AS addiction_flag
INTO #tmp_inpatient_nightcounts_a
FROM #tmp_inpatient_night a
GROUP BY a.unique_ref_id ;

/* adding actual bed nights count which takes into account leave nights */
DROP TABLE IF EXISTS #tmp_inpatient_nightcounts_b;

SELECT a.unique_ref_id
    , COUNT(DISTINCT b.dates) AS bed_actual
INTO #tmp_inpatient_nightcounts_b
FROM #tmp_inpatient_night a
INNER JOIN(
    SELECT DISTINCT preb.unique_ref_id
        , preb.dates
    FROM(
        SELECT *
        FROM #tmp_inpatient_night
        WHERE bed_actual IS NOT NULL
    )preb
)b
ON a.unique_ref_id = b.unique_ref_id
AND a.dates = b.dates
GROUP BY a.unique_ref_id ;

/* adding leave nights count */
DROP TABLE IF EXISTS #tmp_inpatient_nightcounts_c;

SELECT a.unique_ref_id
    , COUNT(DISTINCT c.dates) AS leave
INTO #tmp_inpatient_nightcounts_c
FROM #tmp_inpatient_night a
INNER JOIN(
    SELECT DISTINCT prec.unique_ref_id
        , prec.dates
    FROM(
        SELECT *
        FROM #tmp_inpatient_night
        WHERE leave IS NOT NULL
    )prec
)c
ON a.unique_ref_id = c.unique_ref_id
AND a.dates = c.dates
GROUP BY a.unique_ref_id ;

/* final inpatient table will all variables */
DROP TABLE IF EXISTS #tmp_inpatient_nightcounts;

SELECT a.unique_ref_id
    , a.bednights_type
    , a.activity_spell_start_date
    , a.activity_spell_end_date
    , a.bed_total
    , b.bed_actual
    , c.leave
    , a.addiction_flag AS addiction_flag
INTO #tmp_inpatient_nightcounts
FROM #tmp_inpatient_nightcounts_a a
LEFT JOIN #tmp_inpatient_nightcounts_b b
ON a.unique_ref_id = b.unique_ref_id
LEFT JOIN #tmp_inpatient_nightcounts_c c
ON a.unique_ref_id = c.unique_ref_id ;

/* 5. Any calendar night where there exists a forensic bednight that starts before and ends after this 00.00 and where there does not also exist a leave activity that also starts before and ends after this 00.00. */
DROP TABLE IF EXISTS #tmp_forensic_night;

SELECT a.unique_ref_id
    , dates
    , CASE WHEN b.unique_ref_id IS NULL THEN dates END AS bed_actual
    , CASE WHEN b.unique_ref_id IS NOT NULL THEN dates END AS leave
    , a.addiction_flag
INTO #tmp_forensic_night
FROM(
    SELECT unique_ref_id
        , dates
        , MAX(addiction_flag) AS addiction_flag
    FROM(
        SELECT a.unique_ref_id
            , a.moh_mhd_activity_start_datetime
            , a.moh_mhd_activity_end_datetime
            , c.dates
            , addiction_flag
        FROM #tmp_bednights a
        JOIN #dates c
        ON a.moh_mhd_activity_start_datetime <= c.dates
        AND a.moh_mhd_activity_end_datetime > c.dates
        WHERE moh_mhd_activity_type_code IN ('T11','T12','T13','T14')
    )a
    GROUP BY a.unique_ref_id
        , dates
)a
LEFT JOIN #tmp_bednights b
ON a.unique_ref_id = b.unique_ref_id
AND b.moh_mhd_activity_start_datetime <= a.dates
AND b.moh_mhd_activity_end_datetime > a.dates
AND b.moh_mhd_activity_unit_type_text IN ('LEA')
ORDER BY a.unique_ref_id ;

/* 6. Count calendar nights. */
DROP TABLE IF EXISTS #tmp_forensic_nightcounts_a;

SELECT a.unique_ref_id
    , 'Forensic' AS bednights_type
    , MIN(a.dates) AS activity_spell_start_date
    , MAX(a.dates) AS activity_spell_end_date
    , COUNT(DISTINCT a.dates) AS bed_total
    , MAX(addiction_flag) AS addiction_flag
INTO #tmp_forensic_nightcounts_a
FROM #tmp_forensic_night a
GROUP BY a.unique_ref_id ;

/* adding actual bed nights count which takes into account leave nights */
DROP TABLE IF EXISTS #tmp_forensic_nightcounts_b;

SELECT a.unique_ref_id
    , COUNT(DISTINCT b.dates) AS bed_actual
INTO #tmp_forensic_nightcounts_b
FROM #tmp_forensic_night a
INNER JOIN(
    SELECT DISTINCT preb.unique_ref_id
        , preb.dates
    FROM(
        SELECT *
        FROM #tmp_forensic_night
        WHERE bed_actual IS NOT NULL
    )preb
)b
ON a.unique_ref_id = b.unique_ref_id
AND a.dates = b.dates
GROUP BY a.unique_ref_id ;

/* adding leave nights count */
DROP TABLE IF EXISTS #tmp_forensic_nightcounts_c;

SELECT a.unique_ref_id
    , COUNT(DISTINCT c.dates) AS leave
INTO #tmp_forensic_nightcounts_c
FROM #tmp_forensic_night a
INNER JOIN(
    SELECT DISTINCT prec.unique_ref_id
        , prec.dates
    FROM(
        SELECT *
        FROM #tmp_forensic_night
        WHERE leave IS NOT NULL
    )prec
)c
ON a.unique_ref_id = c.unique_ref_id
AND a.dates = c.dates
GROUP BY a.unique_ref_id ;

/* final forensic table will all variables */
DROP TABLE IF EXISTS #tmp_forensic_nightcounts;

SELECT a.unique_ref_id
    , a.bednights_type
    , a.activity_spell_start_date
    , a.activity_spell_end_date
    , a.bed_total
    , b.bed_actual
    , c.leave
    , a.addiction_flag
INTO #tmp_forensic_nightcounts
FROM #tmp_forensic_nightcounts_a a
LEFT JOIN #tmp_forensic_nightcounts_b b
ON a.unique_ref_id = b.unique_ref_id
LEFT JOIN #tmp_forensic_nightcounts_c c
ON a.unique_ref_id = c.unique_ref_id ;

/* 7. Any calendar night where there exists an other bednight that starts before or on and ends after this 00.00 and where there does not also exist a leave activity that also starts before and ends after this 00.00. */
DROP TABLE IF EXISTS #tmp_other_night;

SELECT a.unique_ref_id
    , dates
    , CASE WHEN b.unique_ref_id IS NULL THEN dates END AS bed_actual
    , CASE WHEN b.unique_ref_id IS NOT NULL THEN dates END AS leave
    , a.addiction_flag
INTO #tmp_other_night
FROM(
    SELECT unique_ref_id
        , dates
        , MAX(addiction_flag) AS addiction_flag
    FROM(
        SELECT a.unique_ref_id
            , a.moh_mhd_activity_start_datetime
            , a.moh_mhd_activity_end_datetime
            , c.dates
            , addiction_flag
        FROM #tmp_bednights a
        JOIN #dates c
        ON a.moh_mhd_activity_start_datetime <= c.dates
        AND a.moh_mhd_activity_end_datetime > c.dates
        WHERE moh_mhd_activity_type_code NOT IN ('T02','T03','T04','T11','T12','T13','T14')
    )a
    GROUP BY a.unique_ref_id
        , dates
)a
LEFT JOIN #tmp_bednights b
ON a.unique_ref_id = b.unique_ref_id
AND b.moh_mhd_activity_start_datetime <= a.dates
AND b.moh_mhd_activity_end_datetime > a.dates
AND b.moh_mhd_activity_unit_type_text IN ('LEA')
ORDER BY a.unique_ref_id ;

/* 8. Count calendar nights. */
DROP TABLE IF EXISTS #tmp_other_nightcounts_a;

SELECT a.unique_ref_id
    , 'Other' AS bednights_type
    , MIN(a.dates) AS activity_spell_start_date
    , MAX(a.dates) AS activity_spell_end_date
    , COUNT(DISTINCT a.dates) AS bed_total
    , MAX(addiction_flag) AS addiction_flag
INTO #tmp_other_nightcounts_a
FROM #tmp_other_night a
GROUP BY a.unique_ref_id ;

/* adding other bed nights count which takes into account leave nights */
DROP TABLE IF EXISTS #tmp_other_nightcounts_b;

SELECT a.unique_ref_id
    , COUNT(DISTINCT b.dates) AS bed_actual
INTO #tmp_other_nightcounts_b
FROM #tmp_other_night a
INNER JOIN(
    SELECT DISTINCT preb.unique_ref_id
        , preb.dates
    FROM(
        SELECT *
        FROM #tmp_other_night
        WHERE bed_actual IS NOT NULL
    )preb
)b
ON a.unique_ref_id = b.unique_ref_id
AND a.dates = b.dates
GROUP BY a.unique_ref_id ;

/* adding leave nights count */
DROP TABLE IF EXISTS #tmp_other_nightcounts_c;

SELECT a.unique_ref_id
    , COUNT(DISTINCT c.dates) AS leave
INTO #tmp_other_nightcounts_c
FROM #tmp_other_night a
INNER JOIN(
    SELECT DISTINCT prec.unique_ref_id
        , prec.dates
    FROM(
        SELECT *
        FROM #tmp_other_night
        WHERE leave IS NOT NULL
    )prec
)c
ON a.unique_ref_id = c.unique_ref_id
AND a.dates = c.dates
GROUP BY a.unique_ref_id ;

/* final other bednights table will all variables */
DROP TABLE IF EXISTS #tmp_other_nightcounts;

SELECT a.unique_ref_id
    , a.bednights_type
    , a.activity_spell_start_date
    , a.activity_spell_end_date
    , a.bed_total
    , b.bed_actual
    , c.leave
    , addiction_flag
INTO #tmp_other_nightcounts
FROM #tmp_other_nightcounts_a a
LEFT JOIN #tmp_other_nightcounts_b b
ON a.unique_ref_id = b.unique_ref_id
LEFT JOIN #tmp_other_nightcounts_c c
ON a.unique_ref_id = c.unique_ref_id ;

/* 9. append bednight tables for inpatient, forensic and other bednight types. */
DROP TABLE IF EXISTS #tmp_bednights_append;

SELECT *
INTO #tmp_bednights_append
FROM #tmp_inpatient_nightcounts
UNION ALL
SELECT *
FROM #tmp_forensic_nightcounts
UNION ALL
SELECT *
FROM #tmp_other_nightcounts ;

/* 11. final bednights table - adding the bed details to the bednights table. */
DROP TABLE IF EXISTS #tmp_bednights_final;

SELECT det.snz_uid
    , det.snz_moh_uid
    , bed.unique_ref_id
    , CAST(NULL AS VARCHAR) AS mha_activity_id
    , det.moh_mhd_organisation_id_code
    , CAST(bed.activity_spell_start_date AS DATE) AS activity_spell_start_date
    , CAST(bed.activity_spell_end_date AS DATE) AS activity_spell_end_date
    , CASE WHEN bednights_type='Inpatient' THEN 'T02_T03_T04'
	  WHEN bednights_type='Forensic' THEN 'T11_T12_T13_T14' ELSE 'Other' END AS activity_type_code
    , CASE WHEN org.organisation_type = 'District Health Board (DHB)' THEN 'HealthNZ' ELSE 'NGOs' END AS service_provider_type
    , CASE WHEN bed.addiction_flag =1 THEN 'Y' ELSE 'N' END AS addiction_services_flag
    , 'N' AS contact_flag
    , 'N' AS contact_face_to_face_flag
    , 'Y' AS bednights_flag
    , bed.bednights_type
    , bed.bed_total AS number_of_bednights
    , bed.leave AS number_of_leavenights
INTO #tmp_bednights_final
FROM #tmp_bednights_append bed
LEFT JOIN(
    SELECT DISTINCT snz_uid
        , snz_moh_uid
        , moh_mhd_organisation_id_code
        , unique_ref_id
    FROM #tmp_bednights
)det
ON bed.unique_ref_id = det.unique_ref_id
LEFT JOIN [$(idimetaversion)].[moh_primhd].[organisation_code] org
ON det.moh_mhd_organisation_id_code = org.organisation_id ;

/* 12. Final table - Secondary specialist mental health and addiction contacts dataset - combine the contacts dataset and bednights dataset together. */
DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[mha_contacts];

SELECT *

INTO [$(targetdb)].[$(targetschema)].[mha_contacts]

FROM(
    SELECT *
    FROM #tmp_all_contacts

    UNION ALL 

    SELECT *
    FROM #tmp_bednights_final
)x

;