Outputs:

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

Inputs:

Purpose

The purpose of this module is to construct Hospital stay spells for publicly funded hospital events in New Zealand.
These spells can be used to produce estimated measures of acute bed days, counts of hospitalisations a person had in a reference time period, hospitalisations attributed to diagnoses, and stays used as a proxy for severity - length of stay being an indicator of how severe the condition of the patient was, and ascertaining events part of the same spell including transfers.
Other uses could also be for benchmarking purposes (average spell length based on diagnosis and district of service), this can be tied to cost, and the output could also be used for system capacity planning.

Key Concepts

Hospital stay

• A hospital stay comprises both inpatient’s defined as a person who is admitted to hospital and stays more than three hours for medical, surgical, psychiatric or obstetric treatment, observation or care. A subset of inpatients are day patients (day case) - a person who is admitted and discharged routinely having spent no midnights in hospital - they have the same event start date and event end date.
• Day cases do not include those patients who died in hospital or transferred to another hospital; these are considered inpatients.
• A hospital stay can be comprised of one or multiple events, and one or multiple health facilities if a transfer is involved.

References & Contacts

1. The methods used in this code module are based on the Acute Bed Days methodology, but adapted to include all types of hospital events.

Development team

Stewardship team

Module Business Rules

1. Hospital stay A hospital stay is created from one or multiple individual events within the publicly funded hospital events dataset. A stay comprised of multiple events must meet one of the following criteria to be considered a “overlapping events” and thus part of the same stay:

• If an individual’s hospital event has a start datetime that is within 3 days of the previous event’s end datetime and that previous event resulted in a transfer.
• If an individual’s hospital event has a start datetime that is within 6 hours (plus or minus) of the previous event’s end datetime.
• To order the spells correctly, embedded events - that is event’s with datetimes that are entirely embedded within the datetimes of another event for that individual - have their start datetimes altered to be the end datetime of the previous event and the end datetime to be the start datetime of the next event.
• This step is necessary when determining if events constitute a stay, as otherwise embedded events will result in a hospital stay being artificially shortened when calculating the time since the previous event.
• Once the datetimes of embedded events are fixed, non-embedded events are ordered into first event of stay, last event of stay and intermediate events. Intermediate events are identified through overlapping events as defined above and an additional flag that indicates if the following event is also an overlapping event.

Exclusions

• If the last event in a hospital event/stay ends with a transfer to a publicly funded facility then the stay is classified as incomplete and is not included as part of the hospital stay.

Potential exclusion criteria

• In the Acute Bed Days measure for hospital stays that are fully publicly-funded, non-casemix stays are excluded. These stays are often for mental health or health of older people hospital stays that may be of interest to researchers, therefore instead of excluding these stays from the module we included an indicator titled casemix_stay_ind that categorises casemix and non-casemix stays. Researchers can decide whether or not to exclude non-casemix stays based on their own use cases.

2. Number of Events in Stay: The number of events in a stay is a count of the unique event id’s.

• Events with no overlap based on the criteria listed above are allocated a value of 1.
• Hospital stays that have both a first event and a last event but no intermediate event are allocated a value of 2.
• For hospital stays that have a first, last and at least one intermediate event, we count the distinct number of unique event id’s of the intermediate events and add 2 (one for the first event and one for the last event).

3. Transfer Flag: The transfer flag is derived from assessing all events in a stay and if any of those events had an end type code in one of the following codes: DA, DF, DO, DP, DT, DW, ET, EA.

4. Day Case Flag: When the date difference between the stay’s spell_start_date and spell_end_date is zero then the day case flag is assigned a value of ‘Y’. If the result is a non-zero then the value is ‘N’.

5. Geriatric Services Flag: A geriatric services flag is derived from a patients age at admission being 75+ years combined with a health specialty code from the first event in the stay being one of the following codes that indicate a geriatric service: D01, D02, D03, D04, D10, D11, D12, D13, D14, D20, D21, D22, D23, D24, D25, D26, D27, D28, D29, D30, D31, D32, D33, D34.

6. Casemix Stay Indicator: If both the first and last event in a stay has a purchase unit that is not ‘EXCLU’ or if the first event in a stay has a purchase unit that is not ‘EXCLU’ then this indicator gets a value of ‘casemix’ to indicate the stay as a casemix stay, otherwise this is a non-casemix stay which is excluded from publicly available acute stay reports.

7. Acute Stay Indicator: Stays are categorised as an ‘Acute Stay’ if the admission type code of the first event in the stay is classified as ‘AC’ and if both the first and last event in a stay has a purchase unit that is not ‘EXCLU’ or if the first event in a stay has a purchase unit that is not ‘EXCLU’ i.e. the stay is a ‘casemix’ stay. This definition aligns with the publicly available acute stay reports.

8. Length of Stay in Hours: As we are only interested in this value for day cases, where the day difference between the spell_start_date and spell_end_date is zero then we show the difference in hours between the start datetime and end datetime. For non day cases this value is returned as NULL. Single event stays will have datetimes that reflect the original start and end datetimes from the source data.

9. Length of Stay in Days: This is the day difference between the spell_start_date and the spell_end_date. For day cases this value will be zero.

Open Issues/Comments

1. Identifying hospital stays from overlapping events prior to July 2011 is unreliable due to lack of timestamps being consistently available in the source data. Also, events where the time is missing default to 00:00:00, to deal with this we were more lenient with the time period and allowed up to 36 hours between events. This will be improved in a future iteration of the code module.

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.

Variable Descriptions

The module is setup to be a spell format: One row per spell, spells have a unique combination of person id, hospital stay id, spell_start_date and end_start_date.

Module Version & Change History

Code

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

/* Requires prepwork to convert varchar to datetime as normal cast and convert statements don't work due to formatting issues with months vs. days */
DROP TABLE IF EXISTS #ordering_table; ;
WITH prep_work AS(
    SELECT ev.[snz_moh_uid]
        , ev.[moh_evt_event_id_nbr]
        , DATEFROMPARTS([snz_birth_year_nbr],[snz_birth_month_nbr],'15') AS 'birth_date'
        , ev.[moh_evt_adm_type_code]
        , ev.[moh_evt_end_type_code]
        , CASE WHEN ev.[moh_evt_end_type_code] IN ('DA','DF','DO','DP','DT','DW','ET','EA') THEN 1 ELSE 0 END AS 'transfer_flag'
        , ev.[moh_evt_facility_code]
        , ev.[moh_evt_pur_unit_text]
        , ev.[moh_evt_facility_xfer_to_code] AS 'transfer_to'
        , ev.[moh_evt_drg_grp_type_code]
        , ev.[moh_evt_drg_31_code]
        , ev.[moh_evt_hlth_spec_code]
        , diag.[moh_dia_clinical_code]
        , ev.[moh_evt_dom_cd_code]
        , ev.[moh_evt_dhb_dom_code]
        , ev.[moh_evt_evst_date] AS 'st_datetime'
        , ev.[moh_evt_even_date] AS 'en_datetime'
        , CAST(moh_evt_evst_date AS DATE) AS 'st_date'
        , CAST(moh_evt_even_date AS DATE) AS 'en_date'
        , CAST(moh_evt_evst_date AS TIME) AS 'st_time'
        , CAST(moh_evt_even_date AS TIME) AS 'en_time'
    FROM [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_event] ev
    LEFT JOIN [$(idicleanversion)].[security].[concordance] nhi
    ON ev.[snz_moh_uid] = nhi.[snz_moh_uid]
    LEFT JOIN [$(idicleanversion)].[data].[personal_detail] pers
    ON nhi.[snz_uid] = pers.[snz_uid]
    LEFT JOIN [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_diag] diag
    ON ev.[moh_evt_event_id_nbr] = diag.[moh_dia_event_id_nbr]
    WHERE diag.[moh_dia_diagnosis_type_code] IN ('A') /* principal diagnosis type i.e. the main reason they are in hopsital */
    AND diag.[moh_dia_submitted_system_code] = diag.[moh_dia_clinical_sys_code] /* filter for latest clinical system code for distinct records */
    AND ev.[moh_evt_purchaser_code] IN ('20','34','35','A0') /* Filter for only publicly funded hospital events */
)

/* Get previous rows to find embedded events */
    , cte_pre AS(
    SELECT LAG([st_datetime],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_start'
        , LAG([en_datetime],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_end'
        , [snz_moh_uid]
        , [birth_date]
        , [moh_evt_event_id_nbr]
        , [moh_evt_adm_type_code]
        , [moh_evt_end_type_code]
        , [transfer_flag]
        , LAG([transfer_flag],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_tf'
        , [st_datetime]
        , [en_datetime]
        , [st_date]
        , [en_date]
        , [st_time]
        , [en_time]
        , LAG([st_date],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_st_date'
        , LAG([en_date],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_en_date'
        , LAG([st_time],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_st_time'
        , LAG([en_time],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[en_datetime],[st_datetime]) AS 'prev_row_en_time'
        , [moh_evt_facility_code]
        , [moh_evt_pur_unit_text]
        , [transfer_to]
        , [moh_evt_drg_grp_type_code]
        , [moh_evt_drg_31_code]
        , [moh_evt_hlth_spec_code]
        , [moh_dia_clinical_code]
        , [moh_evt_dom_cd_code]
        , [moh_evt_dhb_dom_code]
    FROM prep_work
)

/* Get new start date times and end date times based on overlapping event times */
    , date_ordering AS(
    SELECT CASE WHEN [st_datetime] < [prev_row_end]
    AND [st_datetime] ! = [prev_row_end] THEN 1 WHEN [en_datetime] < [prev_row_end]
    AND [en_datetime] ! = [prev_row_end] THEN 1 ELSE 0 END AS 'overlap_indicated'
        , *
    FROM cte_pre
)

    , date_ordering2 AS(
    SELECT CASE WHEN [st_datetime] < [prev_row_end]
    AND [overlap_indicated] = 1
    AND [st_date] >= [prev_row_st_date] THEN [prev_row_end] ELSE [st_datetime] END AS 'event_start_dtm'
        , CASE WHEN [en_datetime] < [prev_row_end]
    AND [overlap_indicated] = 1 THEN [prev_row_end] ELSE [en_datetime] END AS 'event_end_dtm'
        , *
    FROM date_ordering
)

    , date_ordering3 AS(
    SELECT LEAD([event_start_dtm],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[event_start_dtm]) AS 'next_new_start_dtm'
        , LEAD([event_end_dtm],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[event_start_dtm]) AS 'next_new_end_dtm'
        , LAG([event_start_dtm],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[event_start_dtm]) AS 'prev_new_start_dtm'
        , LAG([event_end_dtm],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[event_start_dtm]) AS 'prev_new_end_dtm'
        , *
    FROM date_ordering2
)

/* Get embedded events to exclude as thet start and end within the time period of another event which disrupts the sequencing of events */
SELECT CASE WHEN [event_start_dtm] > [next_new_start_dtm] AND [event_end_dtm] < [next_new_end_dtm] THEN 1 WHEN [event_start_dtm] > [prev_new_start_dtm]
AND [event_end_dtm] < [prev_new_end_dtm] THEN 1 ELSE 0 END AS 'embedded_event'
    , *
INTO #ordering_table
FROM date_ordering3

DROP TABLE IF EXISTS #embedded_events;
SELECT *
INTO #embedded_events
FROM #ordering_table
WHERE [embedded_event] = 1 ;

/* Get all events exluding embedded events and create lagged date times and transfer flags to adjust for the excluded events to get correct sequencing of events. */
DROP TABLE IF EXISTS #non_embedded_events; ;
WITH non_embedded AS(
    SELECT [snz_moh_uid]
        , DATEDIFF(YEAR, [birth_date], CAST([event_start_dtm] AS date)) AS 'age_at_admission'
        , [birth_date]
        , [moh_evt_event_id_nbr]
        , [moh_evt_adm_type_code]
        , [moh_evt_end_type_code]
        , CASE WHEN [moh_evt_end_type_code] IN ('DA','DF','DO','DP','DT','DW','ET','EA') THEN 1 ELSE 0 END AS 'transfer_flag'
        , [event_start_dtm] AS 'st_datetime'
        , [event_end_dtm] AS 'en_datetime'
        , [moh_evt_facility_code]
        , [moh_evt_pur_unit_text]
        , [transfer_to]
        , [moh_evt_drg_grp_type_code]
        , [moh_evt_drg_31_code]
        , [moh_evt_hlth_spec_code]
        , [moh_dia_clinical_code]
        , [moh_evt_dhb_dom_code]
    FROM #ordering_table
    WHERE [embedded_event] = 0
)

SELECT LAG([st_datetime],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'prev_row_start'
    , LAG([en_datetime],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'prev_row_end'
    , [snz_moh_uid]
    , [moh_evt_event_id_nbr]
    , [age_at_admission]
    , [moh_evt_adm_type_code]
    , [moh_evt_end_type_code]
    , [transfer_flag]
    , LAG([transfer_flag],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'prev_row_tf'
    , [st_datetime]
    , [en_datetime]
    , [moh_evt_facility_code]
    , [moh_evt_pur_unit_text]
    , [transfer_to]
    , [moh_evt_drg_grp_type_code]
    , [moh_evt_drg_31_code]
    , [moh_evt_hlth_spec_code]
    , [moh_dia_clinical_code]
    , [moh_evt_dhb_dom_code]
INTO #non_embedded_events
FROM non_embedded ;

/* Define overlapping events by indicating events that have a start datetime equal to the previous end datetime (or within 3 days) with the previous event ending in a transfer or where the absolute time difference between the two events is less than 6 hours. */
DROP TABLE IF EXISTS #ordered_stays; ;
WITH overlaps AS(
    SELECT CASE WHEN [st_datetime] = [prev_row_end]
    AND [prev_row_tf] = 1 THEN 1 WHEN ABS(DATEDIFF(DAY,[prev_row_end],[st_datetime])) <= 3
    AND [prev_row_tf] = 1 THEN 1 WHEN ABS(DATEDIFF(HOUR,[prev_row_end],[st_datetime])) <= 6 THEN 1 ELSE 0 END AS 'overlapping_event'
        , ABS(DATEDIFF(HOUR,[prev_row_end],[st_datetime])) AS 'time_diff'
        , *
    FROM #non_embedded_events
)

/* Create a flag that indicates if the next event is classified as an overlapping event */
    , lead_overlap AS(
    SELECT LEAD([overlapping_event],1) OVER (PARTITION BY [snz_moh_uid] ORDER BY [st_datetime], [en_datetime]) AS 'next_overlap_ind'
        , *
    FROM overlaps
)

/* Create a spell order variable that indicates if the event is a single event (no overlaps) or a multi-event stay (has overlaps) and for multi-event note the order of first, intermediate or last event in the stay */
SELECT CASE WHEN [overlapping_event] = 0 AND [next_overlap_ind] IS NULL THEN 0 /* single event stay */
     WHEN [overlapping_event] = 0 AND [next_overlap_ind] = 0 THEN 0 /* single event stay */
     WHEN [overlapping_event] = 0 AND [next_overlap_ind] = 1 THEN 1 /* first_event_of_cont_spell */
     WHEN [overlapping_event] = 1 AND [next_overlap_ind] = 1 THEN 2 /* middling_event_of_cont_spell */
     WHEN [overlapping_event] = 1 AND [next_overlap_ind] = 0 THEN 3 /* last_event_of_cont_spell */
     WHEN [overlapping_event] = 1 AND [next_overlap_ind] IS NULL THEN 3 /* last_event_of_cont_spell */
 ELSE 99 /* other */
 END AS 'spell_order'
    , *
INTO #ordered_stays
FROM lead_overlap ;

 /* Get table for all single event stays */
DROP TABLE IF EXISTS #single_event;
SELECT *
INTO #single_event
FROM #ordered_stays
WHERE spell_order = 0 ;

 /* Get table for all records that are the first event in a multi-event stay and assign a sequential number to indicate the spell count by date */ 
DROP TABLE IF EXISTS #first_event;
SELECT ROW_NUMBER() OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'first_row_num'
    , *
INTO #first_event
FROM #ordered_stays
WHERE spell_order = 1 ;

 /* Get table for all records that are the last event in a multi-event stay and assign a sequential number to indicate the spell count by date */ 
DROP TABLE IF EXISTS #last_event;
SELECT ROW_NUMBER() OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'last_row_num'
    , *
INTO #last_event
FROM #ordered_stays
WHERE spell_order = 3 ;

 /* Get all intermediary events from multi-event spells */
DROP TABLE IF EXISTS #cont_event;
SELECT ROW_NUMBER() OVER (PARTITION BY [snz_moh_uid] ORDER BY [snz_moh_uid],[st_datetime],[en_datetime]) AS 'cont_row_num'
    , *
INTO #cont_event
FROM #ordered_stays
WHERE spell_order = 2 ;

 /* Get the start and end attributes for the spells constructed from the identified first events matched on sequence to last events*/
DROP TABLE IF EXISTS #multi_events;
SELECT fe.[snz_moh_uid]
    , fe.[age_at_admission]
    , fe.[first_row_num]
    , fe.[moh_evt_adm_type_code] AS 'first_moh_evt_adm_type_code'
    , le.[moh_evt_adm_type_code] AS 'last_moh_evt_adm_type_code'
    , fe.[moh_evt_end_type_code] AS 'first_moh_evt_end_type_code'
    , le.[moh_evt_end_type_code] AS 'last_moh_evt_end_type_code'
    , fe.[st_datetime]
    , le.[en_datetime]
    , fe.[moh_evt_event_id_nbr] AS 'first_moh_evt_event_id_nbr'
    , le.[moh_evt_event_id_nbr] AS 'last_moh_evt_event_id_nbr'
    , fe.[moh_evt_facility_code] AS 'first_moh_evt_facility_code'
    , le.[moh_evt_facility_code] AS 'last_moh_evt_facility_code'
    , fe.[moh_evt_pur_unit_text] AS 'first_moh_evt_pur_unit_text'
    , le.[moh_evt_pur_unit_text] AS 'last_moh_evt_pur_unit_text'
    , fe.[moh_evt_drg_grp_type_code] AS 'first_drg_type_code'
    , le.[moh_evt_drg_grp_type_code] AS 'last_drg_type_code'
    , fe.[moh_evt_drg_31_code] AS 'first_moh_evt_drg_31_code'
    , le.[moh_evt_drg_31_code] AS 'last_moh_evt_drg_31_code'
    , fe.[moh_evt_hlth_spec_code] AS 'first_moh_evt_hlth_spec_code'
    , le.[moh_evt_hlth_spec_code] AS 'last_moh_evt_hlth_spec_code'
    , fe.[moh_dia_clinical_code] AS 'first_moh_dia_clinical_code'
    , le.[moh_dia_clinical_code] AS 'last_moh_dia_clinical_code'
    , fe.[moh_evt_dhb_dom_code] AS 'first_dhb_domicile'
    , le.[moh_evt_dhb_dom_code] AS 'last_dhb_domicile'
    , le.[transfer_to] AS 'last_event_transfer_to'
    , 2 AS 'no_events_in_stay'
    , CASE WHEN fe.[transfer_flag] = 1 OR le.[transfer_flag] = 1 THEN 1 ELSE 0 END AS 'transfer_ind'
INTO #multi_events
FROM #first_event fe
INNER JOIN #last_event le
ON fe.[snz_moh_uid] = le.[snz_moh_uid]
AND fe.[first_row_num] = le.[last_row_num] ;

 /* Create a count of events by spell */
DROP TABLE IF EXISTS #multi_event_stays;
SELECT me.[snz_moh_uid]
    , me.[age_at_admission]
    , me.[first_row_num]
    , me.[first_moh_evt_adm_type_code]
    , me.[last_moh_evt_adm_type_code]
    , me.[first_moh_evt_end_type_code]
    , me.[last_moh_evt_end_type_code]
    , me.[st_datetime]
    , me.[en_datetime]
    , me.[first_moh_evt_event_id_nbr]
    , me.[last_moh_evt_event_id_nbr]
    , me.[first_moh_evt_facility_code]
    , me.[last_moh_evt_facility_code]
    , me.[first_moh_evt_pur_unit_text]
    , me.[last_moh_evt_pur_unit_text]
    , me.[first_drg_type_code]
    , me.[last_drg_type_code]
    , me.[first_moh_evt_drg_31_code]
    , me.[last_moh_evt_drg_31_code]
    , me.[first_moh_evt_hlth_spec_code]
    , me.[last_moh_evt_hlth_spec_code]
    , me.[first_moh_dia_clinical_code]
    , me.[last_moh_dia_clinical_code]
    , me.[first_dhb_domicile]
    , me.[last_dhb_domicile]
    , me.[last_event_transfer_to]
    , COUNT(DISTINCT(ce.[moh_evt_event_id_nbr]))+2 AS 'no_events_in_stay'
    , SUM([transfer_flag]) AS 'transfer_ind'
INTO #multi_event_stays
FROM #multi_events me
INNER JOIN #cont_event ce
ON ce.[snz_moh_uid] = me.[snz_moh_uid]
AND(
    ce.[st_datetime] >= me.[st_datetime]
    AND ce.[en_datetime] <= me.[en_datetime]
)
GROUP BY me.[snz_moh_uid]
    , me.[age_at_admission]
    , me.[first_row_num]
    , me.[first_moh_evt_adm_type_code]
    , me.[last_moh_evt_adm_type_code]
    , me.[first_moh_evt_end_type_code]
    , me.[last_moh_evt_end_type_code]
    , me.[st_datetime]
    , me.[en_datetime]
    , me.[first_moh_evt_event_id_nbr]
    , me.[last_moh_evt_event_id_nbr]
    , me.[first_moh_evt_facility_code]
    , me.[first_moh_evt_facility_code]
    , me.[last_moh_evt_facility_code]
    , me.[first_moh_evt_pur_unit_text]
    , me.[last_moh_evt_pur_unit_text]
    , me.[first_drg_type_code]
    , me.[last_drg_type_code]
    , me.[first_moh_evt_drg_31_code]
    , me.[last_moh_evt_drg_31_code]
    , me.[first_moh_evt_hlth_spec_code]
    , me.[last_moh_evt_hlth_spec_code]
    , me.[first_moh_dia_clinical_code]
    , me.[last_moh_dia_clinical_code]
    , me.[first_dhb_domicile]
    , me.[last_dhb_domicile]
    , me.[last_event_transfer_to] ;

DROP TABLE IF EXISTS #multi_event_stays2;
SELECT me.[snz_moh_uid]
    , me.[age_at_admission]
    , me.[first_moh_evt_adm_type_code]
    , me.[last_moh_evt_adm_type_code]
    , me.[first_moh_evt_end_type_code]
    , me.[last_moh_evt_end_type_code]
    , me.[st_datetime]
    , me.[en_datetime]
    , me.[first_moh_evt_event_id_nbr]
    , me.[last_moh_evt_event_id_nbr]
    , me.[first_moh_evt_facility_code]
    , me.[last_moh_evt_facility_code]
    , me.[first_moh_evt_pur_unit_text]
    , me.[last_moh_evt_pur_unit_text]
    , me.[first_drg_type_code]
    , me.[last_drg_type_code]
    , me.[first_moh_evt_drg_31_code]
    , me.[last_moh_evt_drg_31_code]
    , me.[first_moh_evt_hlth_spec_code]
    , me.[last_moh_evt_hlth_spec_code]
    , me.[first_moh_dia_clinical_code]
    , me.[last_moh_dia_clinical_code]
    , me.[first_dhb_domicile]
    , me.[last_dhb_domicile]
    , me.[last_event_transfer_to]
    , CASE WHEN mes.[no_events_in_stay] IS NULL THEN me.[no_events_in_stay] ELSE mes.[no_events_in_stay] END AS 'no_events_in_stay'
    , CASE WHEN mes.[transfer_ind] IS NULL THEN me.[transfer_ind] ELSE mes.[transfer_ind] END AS 'transfer_ind'
INTO #multi_event_stays2
FROM #multi_events me
LEFT JOIN #multi_event_stays mes
ON me.[snz_moh_uid] = mes.[snz_moh_uid]
AND me.[first_row_num] = mes.[first_row_num] ;

 /* Assign a ■■■■■■■■■■ to single event stay spells */
DROP TABLE IF EXISTS #single_event_stays;
SELECT [snz_moh_uid]
    , [age_at_admission]
    , [moh_evt_adm_type_code] AS 'first_moh_evt_adm_type_code'
    , [moh_evt_adm_type_code] AS 'last_moh_evt_adm_type_code'
    , [moh_evt_end_type_code] AS 'first_moh_evt_end_type_code'
    , [moh_evt_end_type_code] AS 'last_moh_evt_end_type_code'
    , [st_datetime]
    , [en_datetime]
    , [moh_evt_event_id_nbr] AS 'first_moh_evt_event_id_nbr'
    , [moh_evt_event_id_nbr] AS 'last_moh_evt_event_id_nbr'
    , [moh_evt_facility_code] AS 'first_moh_evt_facility_code'
    , [moh_evt_facility_code] AS 'last_moh_evt_facility_code'
    , [moh_evt_pur_unit_text] AS 'first_moh_evt_pur_unit_text'
    , [moh_evt_pur_unit_text] AS 'last_moh_evt_pur_unit_text'
    , [moh_evt_drg_grp_type_code] AS 'first_drg_type_code'
    , [moh_evt_drg_grp_type_code] AS 'last_drg_type_code'
    , [moh_evt_drg_31_code] AS 'first_moh_evt_drg_31_code'
    , [moh_evt_drg_31_code] AS 'last_moh_evt_drg_31_code'
    , [moh_evt_hlth_spec_code] AS 'first_moh_evt_hlth_spec_code'
    , [moh_evt_hlth_spec_code] AS 'last_moh_evt_hlth_spec_code'
    , [moh_dia_clinical_code] AS 'first_moh_dia_clinical_code'
    , [moh_dia_clinical_code] AS 'last_moh_dia_clinical_code'
    , [moh_evt_dhb_dom_code] AS 'first_dhb_domicile'
    , [moh_evt_dhb_dom_code] AS 'last_dhb_domicile'
    , [transfer_to] AS 'last_event_transfer_to'
    , 1 AS 'no_events_in_stay'
    , CASE WHEN [transfer_flag] = 1 THEN 1 ELSE 0 END AS 'transfer_ind'
INTO #single_event_stays
FROM #single_event se ;

 /* Join single event stays and multi-event stays tables to get all stays */
DROP TABLE IF EXISTS #all_stays;
SELECT *
    , DATEDIFF(DAY,[st_datetime],[en_datetime]) AS 'day_difference'
    , DATEDIFF(HOUR,[st_datetime],[en_datetime]) AS 'hour_difference'
INTO #all_stays
FROM (
    SELECT *
    FROM #single_event_stays
    UNION ALL
    SELECT *
    FROM #multi_event_stays2
) a ;

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

SELECT [snz_moh_uid]
    , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS 'hospital_stay_id'
    , [first_moh_evt_facility_code] AS 'facility_id_first_evt'
    , [last_moh_evt_facility_code] AS 'facility_id_last_evt'
    , [st_datetime] AS 'spell_start_date'
    , [en_datetime]AS 'spell_end_date'
    , [first_dhb_domicile] AS 'dhb_domicile_first_evt'
    , [last_dhb_domicile] AS 'dhb_domicile_last_evt'
    , CASE WHEN [transfer_ind] > 0 THEN 'Y' ELSE 'N' END AS 'transfer_flag'
    , [first_moh_evt_adm_type_code] AS 'admission_type_code'
    , [last_moh_evt_end_type_code] AS 'evt_end_type_code'
    , [first_drg_type_code] AS 'drg_type_code_first_evt'
    , [last_drg_type_code] AS 'drg_type_code_last_evt'
    , [first_moh_evt_drg_31_code] AS 'drg_first_evt'
    , [last_moh_evt_drg_31_code] AS 'drg_last_evt'
    , [first_moh_evt_hlth_spec_code] AS 'hlth_spec_code_first_evt'
    , [last_moh_evt_hlth_spec_code] AS 'hlth_spec_code_last_evt'
    , [first_moh_dia_clinical_code] AS 'clinical_code_first_evt'
    , [last_moh_dia_clinical_code] AS 'clinical_code_last_evt'
    , CASE WHEN [age_at_admission] > 74 AND [first_moh_evt_hlth_spec_code] IN ('D01','D02','D03','D04','D10','D11','D12','D13','D14','D20','D21','D22','D23','D24','D25','D26','D27','D28','D29','D30','D31','D32','D33','D34') THEN 'Y' ELSE 'N' END AS 'geriatric_services_flag'
    , CASE WHEN [first_moh_evt_pur_unit_text] NOT IN ('EXCLU') AND [last_moh_evt_pur_unit_text] NOT IN ('EXCLU') THEN 'casemix'
			WHEN [first_moh_evt_pur_unit_text] NOT IN ('EXCLU') AND [last_moh_evt_pur_unit_text] IN ('EXCLU') THEN 'casemix' ELSE 'non_casemix' END AS 'casemix_stay_ind'
    , [no_events_in_stay]
    , CASE WHEN [day_difference] = 0 THEN 'Y' ELSE 'N' END AS 'day_case_flag'
    , CASE WHEN [first_moh_evt_adm_type_code] = 'AC' 
 AND (([first_moh_evt_pur_unit_text] NOT IN ('EXCLU') AND [last_moh_evt_pur_unit_text] NOT IN ('EXCLU')) 
     OR ([first_moh_evt_pur_unit_text] NOT IN ('EXCLU') AND [last_moh_evt_pur_unit_text] IN ('EXCLU')))  THEN 'Y' ELSE 'N' END AS 'acute_stay_flag'
    , CASE WHEN [day_difference] = 0 THEN [hour_difference] ELSE NULL END AS 'length_of_stay_hours'
    , [day_difference] AS 'length_of_stay_days'

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

FROM #all_stays
WHERE [last_event_transfer_to] IS NULL
OR [last_event_transfer_to] LIKE '8%'
OR [last_event_transfer_to] LIKE '9%' /* Exclude stays which end in a transfer to another publicly funded facility. */

ORDER BY [snz_moh_uid]
    , [st_datetime]
    , [en_datetime] ;