Output path

SQL:[IDI_Community].[dsbl_intellectual_disability].[intellectual_disability_YYYYMM]
SAS: libname cmid dsn=idi_community_srvprd schema=dsbl_intellectual_disability; run ;
How to access a code module in the Data Lab:Read here

Description

The purpose of this code module is to identify individuals in New Zealand in the IDI who have a recorded diagnosis of intellectual disability or records indicating an intellectual disability diagnosis is likely (for example a recorded residential history for persons with intellectual disability).

The dataset will list individuals, the earliest event when an individual has a recorded/likely diagnosis of intellectual disability in the IDI, date of the earliest event, the individual’s age and whether they are alive at the time of the IDI refresh. For those individuals who have died, the dataset lists a date of death and age at death.

This module is not intended to be used to give a true prevalence rate of people in New Zealand with intellectual disability due to limitations of the datasets included in the IDI. The code module allows researchers to have a consistent way of identifying intellectually disabled New Zealanders and will allow for comparisons of outcomes for people with intellectual disability and people without intellectual disability.

Key Concepts

• Individuals are identified from a diagnosis recorded using a recognised ICD-9-CM-A diagnosis code for intellectual disability or a diagnosis/likely diagnosis recorded through other means by a professional.
• An individual may have multiple entries in a source dataset or across a number of source datasets.
• This module collates the earliest reference to the individual with an appropriate diagnosis/likely diagnosis in each source dataset and then identifies the earliest reference amongst all datasets.
• The module indicates whether the individual was alive at the time of the most recent IDI refresh.
• The module will record events within the time period that data is included in the IDI.
• The earliest date recorded in the IDI is not a date of diagnosis or onset of a condition.

Business history

Code history and ICD code notes

ICD codes used are based on the ICD clinical code system for the earliest date of data as per Health New Zealand (HNZ) | Te Whatu Ora best practice. This means ICD-9-CM-A codes have been used in line with the start of the health-related datasets in the IDI.

On advice from HNZ clinical coding/classification staff, codes have first been looked up directly in ICD-10-AM 12th Edition (current Edition used), then back mapped to ICD-10-AM 1st Edition and then back mapped to ICD-9-CM-A codes.

Notes for specific datasets

• For NMDS privately funded hospital discharge dataset, individuals are identified by ICD codes backmapped to ICD-9-CM-A and the more recent ICD-10-AM editions where backmapping appears to not have occured.

• For the MORT dataset, ICD codes have only been provided in their submitted system codes (i.e. not backmapped). This means the source dataset includes both ICD-9-CM-A and editions of ICD-10-AM and the code underlying this module has been written to reflect this.

• For the PRIMHD dataset, codes have been provided in DSM-IV and ICD-10-AM edition codes. This means the source dataset includes both DSM-IV and ICD-10-AM editions
  and the code underlying this module has been written to reflect this.

• For the MHINC dataset, codes have been provided in various ICD code editions as well as backmapping to DSM-IV where possible. This means the source dataset includes both DSM-IV and ICD-9-CM-A and the code underlying this module has been written to reflect this.

References

1. Beltran-Castillon L and McLeod K (2023) From Data to Dignity: Health and Wellbeing Indicators for New Zealanders with Intellectual Disability. Published: IHC, Kōtātā Insight
2. Stats NZ (2025) Household Disability Survey 2023 - findings, definitions, and design summary.

Community of Interest

Key business rules

Data quality and other known issues

1. Not all individuals in New Zealand diagnosed with intellectual disability will be identified in this module. The module requires that the individual has had an ACC claim form, hospital discharge events, receivedmental health or Disability Support Services, income support/benefit eligibility, an Oranga Tamariki gateway assessment, been recorded in interRAI data as having a residential history for persons with intellectual disability, or had their death recorded, where the diagnosis of intellectual disability has been specified.

2. Datasets included have different lag times for having updated data available in the IDI. This means the completeness of the data at the time of analysis will depend on the lag time of the different datasets.

3. Individuals identified in the SOCRATES disability dataset do not always appear in the referral or needs assessment datasets as well. Where an individual is only identified through the SOCRATES disability dataset there is no associated event date. In this case a proxy date of ‘3999-12-31’ will be seen in the final table.

4. Registerable stillbirths are included in the mortality dataset.

Comparison against other sources

• Counts of individuals published in the IHC publication ‘From Data to Dignity’, (2023) were for those individuals with intellectual disability in the study population defined as the 2018 Administrative Population Census (APC). The counts from this code module for people with intellectual disability are similar to those in the 2023 publication.

• Counts from the latest Stats NZ Household Disability Survey (HDS) estimated 1% of the New Zealand population aged 5 years and over living in New Zealand households had a intellectual disability diagnosis (Stats NZ, 2025).This equates to around ■■■■■■■■■■■■■. In comparison with the HDS, this module has a slightly lower count.

Parameters

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

1. {targetdb}: The SQL database on which the datasets are to be created.
2. {idicleanversion}: The IDI Clean version that the datasets need to be based on.
3. {targetschema}: The project schema under the target database into which the datasets are to be created.

Dependencies

{idicleanrefresh}.[acc_clean].[claims]
{idicleanrefresh}.[acc_clean].[medical_codes]
{idicleanrefresh}.[moh_clean].[pub_fund_hosp_discharges_diag]
{idicleanrefresh}.[moh_clean].[pub_fund_hosp_discharges_event]
{idicleanrefresh}.[moh_clean].[priv_fund_hosp_discharges_diag]
{idicleanrefresh}.[moh_clean].[priv_fund_hosp_discharges_event]
{idicleanrefresh}.[moh_clean].[mortality_diagnosis]
{idicleanrefresh}.[moh_clean].[mortality_registrations]
{idicleanrefresh}.[moh_clean].[pop_cohort_demographics]
{idicleanrefresh}.[security].[concordance]
{idicleanrefresh}.[moh_clean].[interrai]
{idicleanrefresh}.[msd_clean].[msd_incapacity]
{idicleanrefresh}.[data].[personal_detail]
{idicleanrefresh}.[dia_clean].[deaths]

The following IDI_Adhoc are the most recent versions for this current refresh but changes to the code will need to be made as new datasets are uploaded into the IDI.

[IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_disability_2022]
[IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_needs_assessment_2022]
[IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_referral_2022]
[IDI_Adhoc].[clean_read_MOH_PRIMHD].[primhd_diagnoses_202502]
[IDI_Adhoc].[clean_read_MOH_PRIMHD].[moh_primhd_mhinc]
[IDI_Adhoc].[clean_read_CYF].[cyf_gateway_cli_needs]

Outputs

{targetdb}.{targetschema}.intellectual_disability

Variable descriptions

Granularity:
One row represents an individual, the earliest identification of their diagnosis in the IDI, birthdate, gender and death (if applicable).

Module Version & Change History

Code

/*Set Parameters*/
/*Ref date should be the 15th of the month of the refresh used*/
:setvar targetdb "targetdb"
:setvar targetschema "targetschema" 
:setvar idicleanversion "idicleanversion"
:setvar refdate "refdate"
:setvar idimetadataversion "idimetadataversion"

USE $(targetdb);
GO

/*ACC claims with a code that includes intellectual disability disorder(mild, moderate, severe, profound)*/

DROP TABLE IF EXISTS #acc_id;
SELECT
c.snz_uid
,'ACC' AS source_table
,c.code
,c.date AS event_date
,CAST(NULL AS VARCHAR(10)) AS entity
INTO #acc_id
FROM 
(
SELECT 
[snz_uid]
,[acc_cla_accident_date] AS date
,[acc_cla_primary_inj_code] AS code
FROM [$(idicleanversion)].[acc_clean].[claims]
     WHERE ([acc_cla_primary_inj_code] IN ('E312.','E311.','E310.','E30..', 'E3...') AND [acc_cla_primary_inj_type_code] = 'Read Code')
UNION ALL  
SELECT 
b.snz_uid
,b.acc_cla_accident_date AS date
,a.[acc_med_injury_code] AS code
FROM  [$(idicleanversion)].[acc_clean].[medical_codes] AS a
      LEFT JOIN  [$(idicleanversion)].[acc_clean].[claims] AS b ON a.[snz_acc_claim_uid] = b.[snz_acc_claim_uid]
	  WHERE a.[acc_med_injury_code] IN ('E312.','E311.','E310.','E30..', 'E3...')
      ) AS c;

/*Public hospital discharges where clinical code matches a diagnosis of intellectual disability*/

DROP TABLE IF EXISTS #moh_pub_id; 
SELECT
b.snz_uid
,'MOH_PUB' AS source_table
,a.[moh_dia_submitted_system_code] AS code_sys
,a.[moh_dia_diagnosis_type_code]
,a.[moh_dia_event_id_nbr]
,a.[moh_dia_clinical_code]
,b.moh_evt_birth_year_nbr
,CAST (b.[moh_evt_evst_date] AS DATE) AS event_date
,CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_pub_id
FROM [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_diag] AS a
     LEFT JOIN [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_event] AS b ON a.moh_dia_event_id_nbr = b.moh_evt_event_id_nbr
	 WHERE ((SUBSTRING(a.[moh_dia_clinical_code],1,3) IN ('317','319') OR SUBSTRING (a.[moh_dia_clinical_code],1,4) IN ('3180','3181', '3182')) AND a.[moh_dia_clinical_sys_code] = '06' AND a.[moh_dia_diagnosis_type_code] IN ('A','B'));
	

/*Private hospital discharges where clinical code matches a diagnosis of intellectual disability*/

DROP TABLE IF EXISTS #moh_pri_id;
SELECT 
b.snz_uid
,'MOH_PRI' AS source_table
,a.[moh_pri_diag_event_id_nbr]
,a.[moh_pri_diag_clinic_sys_code]
,a.[moh_pri_diag_sub_sys_code]
,a.[moh_pri_diag_diag_type_code]
,a.[moh_pri_diag_clinic_code] 
,b.[moh_pri_evt_start_date] AS event_date
,CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_pri_id
FROM [$(idicleanversion)].[moh_clean].[priv_fund_hosp_discharges_diag] AS a
     LEFT JOIN [$(idicleanversion)].[moh_clean].[priv_fund_hosp_discharges_event] AS b ON a.moh_pri_diag_event_id_nbr = b.moh_pri_evt_event_id_nbr
     WHERE ((SUBSTRING(a.[moh_pri_diag_clinic_code],1,3) IN ('317, 319') OR SUBSTRING(a.[moh_pri_diag_clinic_code],1,4) IN ('3180','3181','3182')) AND a.[moh_pri_diag_clinic_sys_code] = '06' AND a.[moh_pri_diag_diag_type_code] IN ('A','B'))
     OR (SUBSTRING(a.[moh_pri_diag_clinic_code],1,4) IN ('F700','F701','F708','F709','F710','F711','F718','F719','F720','F721','F728','F729','F730','F731','F738','F739','F780','F781','F788','F789','F790','F791','F798','F799') AND a.[moh_pri_diag_clinic_sys_code] >= '10' AND a.[moh_pri_diag_diag_type_code] IN ('A','B'));

/*Registrations in the mortality dataset where clinical code matches a diagnosis of intellectual disability*/

DROP TABLE IF EXISTS #mos_id;
SELECT
 b.[snz_uid]
,b.[snz_moh_uid]
,'MOH_MOS' AS source_table
,DATEFROMPARTS(b.moh_mor_birth_year_nbr,b.moh_mor_birth_month_nbr,15) AS event_date
,a.[moh_mort_diag_clinical_code] AS code
,a.[moh_mort_diag_clinic_type_code]
,a.[moh_mort_diag_clinic_sys_code]
,a.[moh_mort_diag_diag_type_code]
,CAST (NULL AS VARCHAR(10)) AS entity
INTO #mos_id
FROM [$(idicleanversion)].[moh_clean].[mortality_diagnosis] AS a
      LEFT JOIN [$(idicleanversion)].[moh_clean].[mortality_registrations] AS b  ON a.snz_dia_death_reg_uid = b.snz_dia_death_reg_uid
      WHERE ((SUBSTRING (a.[moh_mort_diag_clinical_code],1,4) IN ('3180, 3181, 3182') OR SUBSTRING (a.[moh_mort_diag_clinical_code],1,3) IN ('317','319')) AND a.[moh_mort_diag_clinic_sys_code] = '06')
	  OR (SUBSTRING(a.[moh_mort_diag_clinical_code],1,4) IN ('F700','F701','F708','F709','F710','F711','F718','F719','F720','F721','F728','F729','F730','F731','F738','F739','F780','F781','F788','F789','F790','F791','F798','F799')  AND a.[moh_mort_diag_clinic_sys_code]>='10');

 /* Individuals in the Ministry of Health SOCRATES database with code 1208 or 1299. Date can either be first contact date or referral date.
Where an individual appears only in the disability table but not the needs assessment or referral table, an event date of 3999-12-31 is added.
This date will be removed in the collation process if the individual appears in other datasets with an earlier event_date or kept if the 
diagnosis table is the only record for the individual*/

DROP TABLE IF EXISTS #moh_soc_id;
SELECT
DISTINCT
b.snz_uid
,'SOC' AS source_table
,CASE WHEN CAST(SUBSTRING(c.[FirstContactDate],1,7) AS date) IS NOT NULL THEN CAST(SUBSTRING(c.[FirstContactDate],1,7) AS date) 
	  WHEN CAST(SUBSTRING(e.[ReferralDate],1,7) AS date) IS NOT NULL THEN CAST(SUBSTRING(e.[ReferralDate],1,7) AS date)
	  WHEN CAST(SUBSTRING(e.[ReferralDate],1,7) AS date) IS NULL AND CAST(SUBSTRING(c.[FirstContactDate],1,7) AS date) IS NULL THEN '3999-12-31'
	  END AS event_date
,CAST (a.code as VARCHAR(7)) AS code
,a.[Description] 
,CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_soc_id
FROM [IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_disability_2022] AS a 
     LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b ON a.snz_moh_uid = b.snz_moh_uid 
     LEFT JOIN (SELECT DISTINCT snz_moh_uid,[FirstContactDate] FROM [IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_needs_assessment_2022] ) AS c ON a.snz_moh_uid=c.snz_moh_uid 
     LEFT JOIN (SELECT DISTINCT snz_moh_uid,[ReferralDate] FROM [IDI_Adhoc].[clean_read_MOH_SOCRATES].[moh_referral_2022]) as e ON a.snz_moh_uid=e.snz_moh_uid
     WHERE code IN ('1208','1299');

/*Intellectual disability diagnosis in PRIMHD diagnosis data*/ 

DROP TABLE IF EXISTS #moh_PRIMHD_id;
SELECT
b.snz_uid
,a.[snz_moh_uid]
,'MOH_PRIMHD' AS source_table
,CAST (a.[organisation_id] AS VARCHAR(10)) AS entity
,a.[classification_code_id]
,a.[diagnosis_type]
,CONVERT(date,a.[classification_start_date],103) AS event_date
,a.[clinical_coding_system_id]
,a.[clinical_code] AS code
INTO #moh_PRIMHD_id
FROM [IDI_Adhoc].[clean_read_MOH_PRIMHD].[primhd_diagnoses_202502] AS a
     LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b ON a.snz_moh_uid = b.snz_moh_uid
      WHERE ((SUBSTRING(a.[clinical_code],1,3) IN ('317','319')  AND a.[clinical_coding_system_id]='07') OR
	(SUBSTRING(a.[clinical_code],1,4) IN ('3180','3181','3182')  AND a.[clinical_coding_system_id]='07') OR
	(SUBSTRING(a.[clinical_code],1,4) IN ('F700','F701','F708','F709','F710','F711','F718','F719','F720','F721','F728','F729','F730','F731','F738','F739','F780','F781','F788','F789','F790','F791','F798','F799')  AND a.[clinical_coding_system_id]>='10'));

/* Intellectual disability diagnosis in PRIMHD MHINC data*/

DROP TABLE IF EXISTS #moh_mhinc_id;
SELECT 
b.snz_uid
,'MOH_MHINC' AS source_table
,CAST (a.[organisation_id] AS VARCHAR(10)) AS entity
,a.[classification_start] AS event_date
,a.[clinical_coding_system_id]
,a.[clinical_code] AS code
,a.[diagnosis_type]
INTO #moh_mhinc_id
FROM [IDI_Adhoc].[clean_read_MOH_PRIMHD].[moh_primhd_mhinc] AS a
     LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b ON a.snz_moh_uid = b.snz_moh_uid
     WHERE ((SUBSTRING(a.[clinical_code],1,3) IN ('317','319')  AND a.[clinical_coding_system_id]='06') OR 
    (SUBSTRING([clinical_code],1,4) IN ('3180','3181','3182')  AND a.[clinical_coding_system_id]='06') OR
	(SUBSTRING(a.[clinical_code],1,3) IN ('317','319')  AND a.[clinical_coding_system_id]='07') OR
	(SUBSTRING(a.[clinical_code],1,4) IN ('3180','3181','3182')  AND a.[clinical_coding_system_id]='07'));

/* Registrations in the interrai dataset with records of residential history for persons with intellectual disability*/

DROP TABLE IF  EXISTS #moh_irai_id;
SELECT [snz_uid]
,[moh_irai_res_hist_intellect_ind] as code
,[moh_irai_assessment_date] as event_date
,CAST (NULL AS VARCHAR(10)) AS entity
,'IRAI' AS source_table
INTO #moh_irai_id
FROM [$(idicleanversion)].[moh_clean].[interrai]
     WHERE [moh_irai_res_hist_intellect_ind] > 0;

/* Intellectual disability recorded on medical certificates */
  
DROP TABLE IF EXISTS #msd_incp_id;
SELECT
[snz_uid]
,code_raw AS code
,from_date AS event_date
,'MSD_INCP' AS source_table
,CAST (NULL AS VARCHAR (10)) AS entity
INTO #msd_incp_id
FROM
((
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incrsn_code] AS code_raw
	  ,'I' AS type
  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
UNION
(
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incrsn95_1_code] AS code_raw
	  ,'1' AS type

  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
UNION
(
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incrsn95_2_code] AS code_raw
	  ,'2' AS type
 
  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
UNION
(
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incrsn95_3_code] AS code_raw
	  ,'3' AS type
    
  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
UNION
(
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incrsn95_4_code] AS code_raw
	  ,'4' AS type
 
  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
UNION
(
SELECT [snz_uid]
      ,[msd_incp_incp_from_date] AS from_date
      ,[msd_incp_incp_to_date] AS to_date
      ,[msd_incp_incapacity_code] AS code_raw
	  ,'T' AS type
    
  FROM [$(idicleanversion)].[msd_clean].[msd_incapacity]
)
) AS a
      WHERE code_raw IS NOT NULL AND code_raw !='000' AND code_raw IN ('008','164');

/* intellectual disability as a client need in gateway assesssments*/

DROP TABLE IF EXISTS #cyf_gateway_id;
SELECT       
b.snz_uid
,a.snz_msd_uid
,a.[need_type_code]
,a.[needs_desc]
,a.[need_category_code] AS code
,a.[needs_cat_desc]
,needs_created_date AS event_date
,'CYF' AS source_table
, CAST (NULL AS VARCHAR (10)) AS entity
INTO #cyf_gateway_id
FROM [IDI_Adhoc].[clean_read_CYF].[cyf_gateway_cli_needs] AS a 
     LEFT JOIN [$(idicleanversion)].security.concordance AS b  ON a.snz_msd_uid = b.snz_msd_uid
     WHERE need_type_code = 'INT';

/* collate data from all sources into one table*/

DROP TABLE IF EXISTS #id;
SELECT
a.snz_uid 
,MIN(event_date) AS min_date
,source_table
,entity
INTO #id
FROM
(
SELECT  snz_uid, event_date, source_table, entity FROM #mos_id
UNION ALL
SELECT  snz_uid, event_date, source_table, entity FROM #moh_pub_id
UNION ALL
SELECT  snz_uid, event_date, source_table, entity FROM #moh_pri_id
UNION ALL
SELECT  snz_uid, event_date, source_table, entity FROM #moh_soc_id
UNION ALL
SELECT  snz_uid, event_date, source_table, entity FROM #moh_mhinc_id
UNION ALL
SELECT  snz_uid, event_date, source_table, entity FROM #moh_PRIMHD_id
UNION ALL
SELECT snz_uid, event_date, source_table, entity FROM #cyf_gateway_id
UNION ALL
SELECT snz_uid, event_date, source_table, entity FROM #msd_incp_id
UNION ALL
SELECT snz_uid, event_date, source_table, entity FROM #moh_irai_id
UNION ALL
SELECT snz_uid, event_date, source_table, entity FROM #acc_id
) AS a
GROUP BY snz_uid
, source_table
, entity;

/* subset of individuals who are deceased and are in death data*/

DROP TABLE IF EXISTS #id_dd;
SELECT 
a.*
,b.snz_birth_month_nbr
,b.snz_birth_year_nbr
,b.snz_sex_gender_code
,b.snz_deceased_year_nbr
,b.snz_deceased_month_nbr
,b.snz_birth_date_proxy
,NULL AS age
,DATEFROMPARTS(b.snz_deceased_year_nbr, b.snz_deceased_month_nbr, 15) AS dod
,'ID' AS [type]
INTO #id_dd
FROM #id as a
     INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b ON a.snz_uid = b.snz_uid
     WHERE a.snz_uid IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
     AND b.snz_deceased_year_nbr IS NOT NULL;

/* subset of individuals who are deceased but not in death data*/

DROP TABLE IF EXISTS #id_od;
SELECT
a.*
,b.snz_birth_month_nbr
,b.snz_birth_year_nbr
,b.snz_sex_gender_code
,b.snz_deceased_year_nbr
,b.snz_deceased_month_nbr
,b.snz_birth_date_proxy
,NULL AS age
,DATEFROMPARTS(b.snz_deceased_year_nbr, b.snz_deceased_month_nbr, 15) AS dod
,'ID' AS [type]
INTO #id_od
FROM #id AS a
     INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b ON a.snz_uid = b.snz_uid
     WHERE a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
     AND b.snz_deceased_year_nbr IS NOT NULL;

/* subset of individuals who are alive*/

DROP TABLE IF EXISTS #id_ad;
SELECT 
a.*
,b.snz_birth_month_nbr
,b.snz_birth_year_nbr
,b.snz_sex_gender_code
,b.snz_deceased_year_nbr
,b.snz_deceased_month_nbr
,b.snz_birth_date_proxy
,DATEDIFF(YEAR, b.snz_birth_date_proxy, '$(refdate)') AS age 
,'3999-12-31' AS dod 
,'ID' AS [type]
 INTO #id_ad
 FROM #id AS a
      INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b ON a.snz_uid = b.snz_uid
     WHERE a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
     AND a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM #id_dd)
     AND a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM #id_od);

 /* join all tables together*/

DROP TABLE IF EXISTS #id_final;
WITH id_union AS (
SELECT
* 
FROM #id_dd
UNION ALL
SELECT * FROM #id_od
UNION ALL
SELECT * FROM #id_ad
)

SELECT
* 
,DATEDIFF(YEAR, snz_birth_date_proxy, dod) AS age_at_death
,CASE WHEN age IS NOT NULL THEN 0 ELSE 1 END AS death_status
INTO #id_final
FROM id_union;

/* select all required variables */

DROP TABLE IF EXISTS #id_min;
SELECT 
snz_uid
,[type]
,entity
,source_table
,min_date
,snz_sex_gender_code
,snz_birth_month_nbr
,snz_birth_year_nbr
,snz_birth_date_proxy
,age
,death_status
,snz_deceased_year_nbr
,snz_deceased_month_nbr
,CASE WHEN dod = '3999-12-31' THEN NULL ELSE dod END AS dod
,CASE WHEN age_at_death > 1000 THEN NULL ELSE age_at_death END AS age_at_death
INTO #id_min
FROM #id_final;

/*take earliest recorded date for each snz_uid and create table */
DROP TABLE IF EXISTS #id_dbl;
SELECT 
snz_uid
,[type]
,entity
,source_table
,min_date
,snz_sex_gender_code
,snz_birth_month_nbr
,snz_birth_year_nbr
,snz_birth_date_proxy
,age
,death_status
,snz_deceased_year_nbr
,snz_deceased_month_nbr
,dod
,age_at_death
INTO #id_dbl
FROM #id_min a
WHERE min_date = (
SELECT MIN(min_date)
FROM #id_min b
WHERE b.snz_uid = a.snz_uid)
ORDER BY snz_uid;

/* remove duplicate snz_uid with the same minimum date but different entities or sources. Create table in IDI_Sandpit */

DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(projprefix)_intellectual_disability];

WITH cte AS 
(SELECT ROW_NUMBER() OVER(PARTITION BY snz_uid
ORDER BY snz_uid DESC) AS rn
FROM #id_dbl)
DELETE FROM cte WHERE rn > 1;

SELECT 
snz_uid
,[type]
,entity
,source_table
,min_date
,snz_sex_gender_code
,snz_birth_month_nbr
,snz_birth_year_nbr
,snz_birth_date_proxy
,age
,death_status
,snz_deceased_year_nbr
,snz_deceased_month_nbr
,dod
,age_at_death

INTO [$(targetdb)].[$(targetschema)].[$(projprefix)_intellectual_disability]

FROM #id_dbl