Outputs:

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

Inputs:

Module Version & Change History

Purpose

Corrections updated the IDI data supply in 2025 to provide greater flexibility for researchers in the data supplied to the IDI, to serve a greater number of use cases.
Previously Corrections provided a management period table and major management period table that allowed researchers to understand the experiences of people in Corrections management.

After the discontinuation of the major management period table, there was a need articulated from the community of interest for this table. This code module is intended to allow people to recreate the major management periods of people under Corrections management with a replication of the logic applied by Corrections.
Due to limitation in the current Corrections data supply to the IDI, some attributes created in this code module might need careful consideration when using dependent on use case. These are discussed further under the “Limitations” and “Data quality and other known Issues” sections below.

Key Concepts

Management Period: All possible managed directive types and their management periods based on directive and muster tables in IDI for prison sentences and remands. And all managed directive types and their management periods based on directive data for community sentences and community orders.

Major Management Period: Primary directive management periods where overlapping spells are prioritised based on directive type to create a sequential records of all major management periods.

Variable Descriptions

Key Business Rules

Define management spell:

Step 1. Create all possible managed directive types and their management periods based on [IDI_Clean_YYYYMM].[cor_clean].[directive] and [IDI_Clean_YYYYMM].[cor_clean].[muster] tables.

• In this step, muster periods from [IDI_Clean_YYYYMM].[cor_clean].[muster] are used as cutters, cutting each directive period from [IDI_Clean_YYYYMM].[cor_clean].[directive] into smaller periods for each individual. Some parts of the directive periods will be excluded after this step, as the muster is a more accurate reflection of reality - see below for more detail.
• The directive table has [cor_dir_management_end_date] which is the legislative end date from courts which may be different to actual end date/ release date/ termination date for each directive.
• The muster table has [cor_mus_muster_end_date] which is a more accurate source of the actual end date/ release date/ termination date of the management period.
• Additionally, please note that the muster table is only relevant for prison and remand management spells as it is a record of the national prison muster used operationally. Community based sentences will therefore not appear in the muster.

Define major management spell:

Step 2. Begin with the output from Step 1 where all management spells are defined.

• All directives with overlapping periods will be separated into segments to separate the overlapping intervals.
• The overlapping periods are prioritised (based on directive type) to determine the major directive type for that period. In this step, community sentences and orders will also be considered.

Step 3. Combine/Chain consecutive management periods of similar directive type to get the major management period under each directive type.

• Get attributes of the defined major management period.

Multiple concurrent periods:

Remand directive type data does not have lead_offence_code at the moment in [IDI_Clean_202510].[cor_clean].[directive] and Seriousness_Scores for each offence_code is also not available in [IDI_Metadata_202510].[cor].[offence_concord].
These two fields are crucial in identifying the most seriousness (lead/index offence) offence when there are multiple overlapping directives - either different directive types or the same. Therefore, to ensure the reproducability of data each time the spell is run (specifically, offence_code and directive_id), the following rules are applied:

1. Periods will be sorted to select the preceding directive type. Ex: If a sentence and a remand period overlap, then the Sentence will be prioritised over remand.
2. If multiple periods are selected in the step 1, then the one with the “longest imposed_days” will be prioritised (note, this is not relevant to remand periods as there is no imposed sentence length - instead for remand records the date difference between the derived period start date and derived period end date is calculated and given the label “imposed days” and used in the prioritisation).
3. If multiple periods result from step 2, then those will be ordered by directive_id in ascending order to select one. Ex: if there are three remand periods with same start_date and end_date then these three will be ordered by their directive_id in ascending order and select the first. This step is merely to ensure the reproducability.
4. Imposed_days_at_start and imposed_days_at_end variables are

Changes affecting data interpretation

From the 202506 refresh onwards, the Corrections updated the format and the way they provide the data into the IDI. The new provision is more informative and sustainable,
However, users will have to navigate through and process the data to create the actual management type and the period of each person under Corrections management in a give point in time.

External validation

Testing against internal Corrections data we have acceptable count matching and trends for the following:

• Remand starts by financial year
• Remand ends by financial year
• Prison sentence starts by financial year
• Prion sentence ends by financial year
• All Community sentence related starts by financial year
• All Community order related starts by financial year
  Time series comparisons to the old IDI major management period table should be approached with caution as improvements to the business logic (both internally at Corrections and within this Code Module) have been made.
  Generally, Corrections advises caution using data before 2000.

Limitations

1. Recalls and EM Bail data are not included in the IDI corrections source data - therefore they are not included in the management and major management periods.
2. Released Time-served (TS) should not be calculated using the major management period output as zero-length directives are excluded.
   • Released Time-served is when a person is released at sentencing when time spent in custody on remand prior to sentencing is equal or greater than the length of their sentence.
   • In the context of this code module, to identify “Released Time-servedâ€<9d>, it is necessary to be able to identify if a person is having a remand period ended following an sentencing with an immediate release (i.e. sentence start date = sentences actual release date, in other words “zero-lengthâ€<9d> sentences) and have an actual muster release (i.e. person should be actually released from the muster, sometimes this is not the case for people. Ex: A person on remand with multiple charges would sentenced for one charge and immediately released from that sentencing as “Released time-servedâ€<9d> but be still in prion on remand for the hearing of other charges). Given that, code module excludes “zero lengthâ€<9d> sentences, identification of “Released Time-servedâ€<9d> are not possible. However, they will still have the remand record for where they were in Corrections management.
   • For coherence, in capturing post-release management spells, zero-length directives are excluded.
3. It is possible for a person to serve multiple community sentences concurrently (e.g., Supervision, Community Work (CW), and Home Detention (HD) can all be served at the same time). However, the code module prioritises concurrent directive types to determine the primary directive for each major management period (in the above example, only Supervision will be created). Therefore, the code module should be used only to identify major management periods and their primary directives. This issue applies only to community sentences or orders. Users can instead use #ra_mgmt_period_a (a by-product of the code module) or other IDI source tables, such as [IDI_Clean_YYYYMM].[cor_clean].[directive], to identify these records, provided they understand which community sentences or orders can be served concurrently.

Data quality and other known issues

1. In #ra_major_mgmt_period_a, the logic used to build major management periods creates some inaccurate periods which have not actually taken place.

• Any records that have an end date that exceeds the latest data upload date are end dated with the latest data upload date.
• Two additional columns are created to identify these occurrences [is_directive_end_dated_flag] to indicate to researchers that the directive is not completed but should be end dated; and [is_directive_completed_flag] which can be used to identify if the directive is completed or not.

2. Due to data availability in the current IDI supply, selecting the major directive period is problematic when the same directive type is overlapping multiple times under different directive_id and lead_offence_code.

• Remand directive types do not have lead_offence_code and the offence_concord table does not have seriousness_score for each offence_code.
• Usually, for remand directives, seriousness_score is used to select the lead/index offence type during a particular remand period when a person is having multiple remand warrants for different charges in each overlapping or major remand period.
• For sentences, longest imposed sentence will be the lead/index offence type under an aggregated sentence id/period and if the imposed sentence lengths are similar then again seriousness_score is used to select the lead/index offence type. This business rule cannot currently be applied due to lack of data availability.
• The following rules are applied to increase the accuracy and ensure the reproducibility of the results:
• a) First, the major directive type is selected based on priority ranking. E.g. if a remand and a prison sentence period are overlapped, then sentence period is prioritised over the remand period.
• b) Then, if multiple directive_ids are selected in the first iteration above, imposed length in days will be used to prioritise the period with the longest length over the other periods. For remand directives, difference between management_start_date and management_end_date is used.
• c) Finally, if still multiple periods are still selected in the second iteration, directive_ids will be ordered in ascending order to select a directive_id and lead_offence_code for the relevant overlapped period. This is merely to ensure the reproducibility of the data when the spell is run each time.

3. There is a possibility that the actual directive type the person is serving in a certain period will be overridden by an older directive.

• E.g. If a person is released on Parole and then recalled to prison. Then this person is released on Released on Conditions (RoC) instead of parole. This spell will prioritise Parole over RoC for the overlapping period even though the person is on a RoC.

4. Consecutive management periods for the same directive type are combined/chained only if the later period is immediately started. Spells can be adjusted to chain any directive that is started in a few days after the first one ended.
5. Even though “Other Imprisonment”, “Life Imprisonment” and “Preventive Detention” are all prison sentence types, these are not chained to create a major management period where there exist such consecutive prison sentences. This was done to avoid complicating the spell and any user will have to chain these directives using the #ra_major_period_a if necessary.
6. From 1970 to 2000s the Community supervision numbers are peaked - this is a source data issue and will be addressed in the IDI data supply for a future refresh.
7. Index offence code is missing for many records where directive type is “Other Imprisonment” in the source data - this is a source data issue and will be addressed in the IDI data supply for a future refresh.

Module development team

Stewardship team

Code

/* Turn on SQLCMD mode to assign parameters */
/*
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar idicleanversion "{idicleanversion}"
:setvar idimetaversion "{idimetaversion}"
*/

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

/* STEP 1: CREATE A TEMP TABLE WITH ALL DIRECTIVE TYPES AND THEIR PRIORITY RANKS. THIS WILL BE USED TO PRIORITISE PRECEDING DIRECTIVE TYPE AT A GIVEN POINT IN TIME WHEN MULTIPLE DIRECTIVES PERIODS ARE OVERLAPPED.
EX: IF A PERSON HAS COMMUNITY WORK AND A REMAND PERIOD WITH OVERLAPPING TIME PERIODS THE PERSON SHOULD BE ACTUALLY SERVING THE REMAND IN PRISON DURING THE OVERLAPPING PERIOD. 
SO, REMAND PERIOD WILL BE PICKED FOR THE OVERLAPPING TIME PERIOD AS MAJOR MANAGEMENT. */
DROP TABLE IF EXISTS #tmp_cor_codes;

CREATE TABLE #tmp_cor_codes(
    short_desc_code VARCHAR(10)
        , dir_description VARCHAR(40)
        , code_Rank INT NOT NULL
        , new_code VARCHAR(40)
        , old_code VARCHAR(40)
);

INSERT INTO #tmp_cor_codes
VALUES(
    'ERROR'
        , 'Period code NOT properly handled'
        , 1
        , ''
        , ''
)
    , (
    'PRISON'
        , 'Indeterminate prison sentence'
        , 10
        , 'LIFE IMPRISONMENT'
        , ''
)
    , (
    'PRISON'
        , 'Indeterminate prison sentence'
        , 20
        , 'PREVENTIVE DETENTION [IMPRISONMENT]'
        , ''
)
    , (
    'PRISON'
        , 'Determinate prison sentence'
        , 30
        , 'OTHER IMPRISONMENT'
        , 'IMPRISONMENT'
)
    , (
    'REMAND'
        , 'Remanded IN custody'
        , 40
        , 'REMAND (ACCUSED / CONVICTED)'
        , 'REMAND'
)
    , (
    'ESO'
        , 'Extended supervision ORDER'
        , 50
        , 'EXTENDED SUPERVISION ORDER'
        , 'EXTENDED SUPERVISION ORDER'
)
    , (
    'PAROLE'
        , 'Paroled'
        , 60
        , 'PAROLE'
        , 'PAROLE'
)
    , (
    'ROC'
        , 'Released WITH conditions'
        , 70
        , 'RELEASED ON CONDITIONS'
        , 'RELEASED ON CONDITIONS'
)
    , (
    'ROO'
        , 'Returning offender ORDER'
        , 80
        , 'RETURNING OFFENDER ORDER'
        , 'RETURNING OFFENDER ORDER'
)
    , (
    'HD_SENT'
        , 'Home detention sentenced'
        , 90
        , 'HOME DETENTION'
        , 'HOME DETENTION'
)
    , (
    'HD_REL'
        , 'Released to HD'
        , 90
        , 'RELEASED TO HOME DETENTION'
        , 'RELEASED TO HOME DETENTION'
)
    ,  /* HD_SENT AND REL_HD are placed in the same seriousness score scope.*/
(
    'PDC'
        , 'Post detention conditions'
        , 100
        , 'POST DETENTION CONDITIONS'
        , 'POST DETENTION CONDITIONS'
)
    , (
    'INT_SUPER'
        , 'Intensive supervision'
        , 110
        , 'INTENSIVE SUPERVISION'
        , 'INTENSIVE SUPERVISION'
)
    , (
    'SUPER'
        , 'Supervision'
        , 120
        , 'SUPERVISION'
        , 'SUPERVISION'
)
    , (
    'COM_DET'
        , 'Community detention'
        , 130
        , 'COMMUNITY DETENTION'
        , 'COMMUNITY DETENTION'
)
    , (
    'COM_PROG'
        , 'Community programme'
        , 140
        , 'COMMUNITY PROGRAMME'
        , 'COMMUNITY PROGRAMME'
)
    , (
    'CW'
        , 'Community work'
        , 150
        , 'COMMUNITY WORK'
        , 'COMMUNITY WORK'
)
    , (
    'PERIODIC'
        , 'Periodic detention'
        , 160
        , 'PERIODIC DETENTION'
        , 'PERIODIC DETENTION'
)
    , (
    'COM_SERV'
        , 'Community service'
        , 170
        , 'COMMUNITY SERVICE'
        , 'COMMUNITY SERVICE'
)
    , (
    'OTH_COM'
        , 'Other community'
        , 180
        , 'OTHER SENTENCING OUTCOME'
        , ''
)
 ;

/* STEP 2:CREATING AND INSERTING ALL DIRECTIVES (INCLUDING OVERLAPPING PERIODS) FROM DIRECTIVE AND MUSTER TABLES. */
DROP TABLE IF EXISTS #ra_mgmt_period_a;

CREATE TABLE #ra_mgmt_period_a(
    offender_id NVARCHAR(20)
        , directive_id NVARCHAR(20)
        , directive_type NVARCHAR(256)
        , directive_srt INT
        , period_start_date DATE
        , period_end_date DATE
        , lead_offence_code NVARCHAR(20)
        , imposed_days INT
);

/* INSERT ALL REMAND RECORDS FROM DIRECTIVE AND MUSTER TABLES */
INSERT INTO #ra_mgmt_period_a(
    offender_id
        , directive_id
        , directive_type
        , directive_srt
        , period_start_date
        , period_end_date
        , lead_offence_code
        , imposed_days
)
SELECT ccd.snz_uid
    , ccd.[snz_cor_directive_uid]
    , ccd.[cor_dir_directive_type_text]
    , tcc.code_rank AS directive_priority_rank
    , CASE 
        WHEN ccd.[cor_dir_management_start_date] > ccm.[cor_mus_muster_start_date] THEN ccd.[cor_dir_management_start_date] 
        ELSE ccm.[cor_mus_muster_start_date] 
    END AS period_start_date
    , CASE 
        WHEN ccd.[cor_dir_management_end_date] < ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) THEN ccd.[cor_dir_management_end_date] ELSE ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) END AS period_end_date
    , ccd.[cor_dir_index_offence_code]
    , NULL AS imposed_days
FROM (
    SELECT *
    FROM [$(idicleanversion)].[cor_clean].[directive] WHERE UPPER([cor_dir_directive_type_text]) IN ('REMAND (ACCUSED / CONVICTED)', 'REMAND') AND NOT ([cor_dir_management_start_date] > [cor_dir_management_end_date])) ccd
    JOIN [$(idicleanversion)].[cor_clean].[muster] ccm
    ON ccm.snz_uid = ccd.snz_uid
    AND ccm.[cor_mus_muster_start_date] < ccd.[cor_dir_management_end_date]
    AND ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) > ccd.[cor_dir_management_start_date]
    LEFT JOIN #tmp_cor_codes tcc
    ON UPPER(ccd.[cor_dir_directive_type_text]) = tcc.new_code

    UNION ALL

/* INSERT ALL SENTENCE RECORDS FROM DIRECTIVE AND MUSTER TABLES */
    SELECT snz_uid
        , [snz_cor_directive_uid]
        , [cor_dir_directive_type_text]
        , directive_priority_rank
        , MIN(p_start_date) OVER (PARTITION BY snz_cor_directive_uid) AS period_start_date
        , MIN(p_end_date) OVER (PARTITION BY snz_cor_directive_uid) AS period_end_date
        , [cor_dir_index_offence_code]
        , CASE WHEN [cor_dir_directive_type_text] = 'LIFE IMPRISONMENT' THEN 99999 
         WHEN [cor_dir_directive_type_text] = 'PREVENTIVE DETENTION [IMPRISONMENT]' THEN 99998
         ELSE [cor_dir_directive_len_days_nbr]
    END AS imposed_days
    FROM (
        SELECT ccd.snz_uid
            , ccd.[snz_cor_directive_uid]
            , ccd.[cor_dir_directive_type_text]
            , tcc.code_rank AS directive_priority_rank
            , CASE 
            WHEN ccd.[cor_dir_management_start_date] > ccm.[cor_mus_muster_start_date] THEN ccd.[cor_dir_management_start_date] 
            ELSE ccm.[cor_mus_muster_start_date] 
        END AS p_start_date
            , CASE 
            WHEN ccd.[cor_dir_management_end_date] < ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) THEN ccd.[cor_dir_management_end_date] ELSE ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) END AS p_end_date
            , ccd.[cor_dir_index_offence_code]
            , ccd.[cor_dir_directive_len_days_nbr]
        FROM (
            SELECT *
            FROM [$(idicleanversion)].[cor_clean].[directive] WHERE UPPER([cor_dir_directive_type_text]) IN ('OTHER IMPRISONMENT','LIFE IMPRISONMENT','PREVENTIVE DETENTION [IMPRISONMENT]')AND NOT ([cor_dir_management_start_date] > [cor_dir_management_end_date])) ccd
            JOIN [$(idicleanversion)].[cor_clean].[muster] ccm
            ON ccm.snz_uid = ccd.snz_uid
            AND ccm.[cor_mus_muster_start_date] < ccd.[cor_dir_management_end_date]
            AND ISNULL(ccm.[cor_mus_muster_end_date], CONVERT(DATE, '9999-12-31')) > ccd.[cor_dir_management_start_date]
            LEFT JOIN #tmp_cor_codes tcc
            ON UPPER(ccd.[cor_dir_directive_type_text]) = tcc.new_code
        ) AS subquery

        UNION ALL

/* INSERT ALL COMMUNITY SENTENCES AND COMMUNITY ORDERS FROM DIRECTIVE TABLE */
        SELECT DISTINCT ccd.snz_uid
            , ccd.[snz_cor_directive_uid]
            , ccd.[cor_dir_directive_type_text]
            , tcc.code_rank AS directive_priority_rank
            , ccd.[cor_dir_management_start_date] AS period_start_date
            , ccd.[cor_dir_management_end_date]AS period_end_date
            , ccd.[cor_dir_index_offence_code]
            , ccd.[cor_dir_directive_len_days_nbr] AS imposed_days
        FROM [$(idicleanversion)].[cor_clean].[directive] ccd
        LEFT JOIN #tmp_cor_codes tcc
        ON UPPER(ccd.[cor_dir_directive_type_text]) = tcc.new_code
        WHERE ccd.[cor_dir_directive_type_text] IN (
    /* old */
    'PERIODIC DETENTION',
    'COMMUNITY PROGRAMME',
    'COMMUNITY SERVICE',
    /* sentences */
    'HOME DETENTION',
    'COMMUNITY DETENTION',
    'INTENSIVE SUPERVISION',
    'SUPERVISION',
    'COMMUNITY WORK',
    'OTHER SENTENCING OUTCOME',
    /* orders */
    'PAROLE',
    'RELEASED ON CONDITIONS',
    'POST DETENTION CONDITIONS',
    'RETURNING OFFENDER ORDER',
    'EXTENDED SUPERVISION ORDER'
  )
        AND NOT(
            [cor_dir_management_start_date] > [cor_dir_management_end_date]
        );

/* STEP 3: CREATING A NEW EMPTY TABLE TO INSERT THE MAJOR MANAGEMENT PERIODS */

        DROP TABLE IF EXISTS #management_spells_temp;
        WITH dates AS(
            SELECT DISTINCT [offender_id]
                , [period_start_date] AS derived_cutoff_date
            FROM #ra_mgmt_period_a
            UNION ALL
            SELECT DISTINCT [offender_id]
                , period_end_date AS derived_cutoff_date
            FROM #ra_mgmt_period_a
        )

            , dataset1 AS(
            SELECT DISTINCT a.*
                , d.derived_cutoff_date AS rnk1
            FROM #ra_mgmt_period_a a
            JOIN dates d
            ON a.offender_id = d.offender_id
            AND d.derived_cutoff_date BETWEEN a.period_start_date AND a.period_end_date
        )

            , dataset2 AS(
            SELECT DISTINCT x.offender_id
                , x.directive_id
                , x.directive_type
                , x.directive_srt
                , x.period_start_date
                , x.period_end_date
                , x.lead_offence_code
                , ISNULL(imposed_days,DATEDIFF(DAY,period_start_date,period_end_date)) AS imposed_days
                , ISNULL(rnk2, period_start_date) AS comb_start_date1
                , rnk1 AS comb_end_date1
            FROM (
                SELECT DISTINCT d.*
                    , LAG(rnk1) OVER (PARTITION BY offender_id,directive_id, period_start_date, period_end_date ORDER BY rnk1 ASC) AS rnk2 /* This step need to be review the use of directive_id in ther partition part (For Nisa's reference).*/
                FROM dataset1 d
            ) x
            WHERE ISNULL(rnk2, period_start_date) <> rnk1
        )

            , dataset3 AS(
            SELECT DISTINCT d.*
                , ROW_NUMBER() OVER (PARTITION BY d.offender_id, d.comb_start_date1, d.comb_end_date1 ORDER BY directive_srt ASC,imposed_days DESC, directive_id ASC) AS major_dim_type /* Given that we do not have offence_code for Remand directives and seriousness score for any offence, had to use this order by rule to ensure the repeatability/reproducability of the directive_ids in the major management period table.*/
            FROM dataset2 d
        )

            , dataset4_1 AS(
            SELECT DISTINCT offender_id
                , directive_id
                , directive_type
                , imposed_days
                , comb_start_date1
                , comb_end_date1
                , CASE 
            WHEN directive_type = LAG(directive_type) OVER (PARTITION BY offender_id, directive_type ORDER BY comb_end_date1)
                 AND comb_start_date1 = LAG(comb_end_date1) OVER (PARTITION BY offender_id, directive_type ORDER BY comb_end_date1)
            THEN 1
            ELSE 0
        END AS chain_to_prev
            FROM dataset3
            WHERE major_dim_type = 1
        )

            , dataset4_2 AS(
            SELECT a.offender_id
                , a.directive_id
                , a.directive_type
                , a.imposed_days
                , a.comb_start_date1
                , a.comb_end_date1
                , CASE 
            WHEN a.chain_to_prev = 1 THEN '1900-01-01' 
            ELSE a.comb_start_date1 
        END AS comb_start_date2
            FROM dataset4_1 a
        )

            , dataset4_3 AS(
            SELECT b.offender_id
                , b.directive_id
                , b.directive_type
                , b.imposed_days
                , b.comb_start_date1
                , b.comb_end_date1
                , b.comb_start_date2
                , MAX(b.comb_start_date2) OVER (PARTITION BY b.offender_id ORDER BY b.comb_start_date1 ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS comb_period_start_date
            FROM dataset4_2 b
        )

            , dataset4_4 AS(
            SELECT DISTINCT c.offender_id
                , c.directive_id
                , c.directive_type
                , c.imposed_days
                , c.comb_start_date1
                , c.comb_end_date1
                , c.comb_start_date2
                , c.comb_period_start_date
                , MAX(c.comb_end_date1) OVER (PARTITION BY c.offender_id, c.comb_period_start_date) AS comb_period_end_date
            FROM dataset4_3 c
        )

            , dataset4_5 AS(
            SELECT DISTINCT d.offender_id
                , d.directive_type
                , d.comb_period_start_date
                , d.comb_period_end_date
                , first_value(
                d.directive_id
            ) OVER (PARTITION BY d.offender_id,d.directive_type,d.comb_period_start_date,d.comb_period_end_date ORDER BY comb_start_date1 ASC,imposed_days DESC,directive_id ASC) AS directive_id_at_start
                , first_value(
                d.directive_id
            ) OVER (PARTITION BY d.offender_id,d.directive_type,d.comb_period_start_date,d.comb_period_end_date ORDER BY comb_start_date1 DESC,imposed_days DESC,directive_id ASC) AS directive_id_at_end
            FROM dataset4_4 d
            WHERE d.comb_period_start_date ! = '1900-01-01'
        )

            , dataset4_6 AS(
            SELECT DISTINCT d.offender_id
                , d.directive_type
                , d.comb_period_start_date
                , d.comb_period_end_date
                , d.directive_id_at_start
                , d.directive_id_at_end
                , dir1.cor_dir_index_offence_code AS offence_code_at_start
                , dir2.cor_dir_index_offence_code AS offence_code_at_end
                , CASE WHEN  d.directive_type IN ('REMAND (ACCUSED / CONVICTED)','COMMUNITY WORK') THEN NULL 
			ELSE dir1.cor_dir_directive_len_days_nbr
	   END AS drvd_directive_length_days_at_start
                , CASE WHEN  d.directive_type IN ('REMAND (ACCUSED / CONVICTED)','COMMUNITY WORK') THEN NULL 
			ELSE dir2.cor_dir_directive_len_days_nbr
	   END AS drvd_directive_length_days_at_end
                , '2025-07-10' AS directive_end_dated_date
                ,  /* This date will be the cutoff date that'll be used to end date the periods to avoid using future dates - based on max modification date of directive table but ideally would be based on the directive table extraction date. */
       CASE WHEN comb_period_end_date >= '2025-07-10' THEN 'Y' ELSE 'N' END AS is_directive_end_dated_flag
                , CASE WHEN comb_period_end_date < '2025-07-10' THEN 'Y' ELSE 'N' END AS is_directive_completed_flag
                , LAG(d.directive_type) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date) AS prev_directive_type
                , LEAD(d.directive_type) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date) AS next_directive_type
                , CASE WHEN d.comb_period_start_date BETWEEN LAG(d.comb_period_end_date) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date)AND DATEADD(DAY,3,LAG(d.comb_period_end_date) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date)) THEN 'Y' ELSE 'N' END AS immediate_prev_directive_type
                , CASE WHEN d.comb_period_end_date BETWEEN LEAD(d.comb_period_start_date) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date) AND DATEADD(DAY,3,LEAD(d.comb_period_start_date) OVER (PARTITION BY d.offender_id ORDER BY d.comb_period_start_date)) THEN 'Y' ELSE 'N' END AS immediate_next_directive_type
            FROM dataset4_5 d
            LEFT JOIN [$(idicleanversion)].[cor_clean].[directive] dir1
            ON d.[directive_id_at_start] = dir1.[snz_cor_directive_uid]
            LEFT JOIN [$(idicleanversion)].[cor_clean].[directive] dir2
            ON d.[directive_id_at_end] = dir2.[snz_cor_directive_uid]
        )

            , dataset_final AS(
            SELECT DISTINCT d.offender_id
                , d.directive_type
                , d.comb_period_start_date
                , d.comb_period_end_date
                , d.directive_id_at_start
                , d.directive_id_at_end
                , d.offence_code_at_start
                , d.offence_code_at_end
                , ofn1.roc_offence_type AS offence_at_start
                , ofn2.roc_offence_type AS offence_at_end
                , d.drvd_directive_length_days_at_start AS imposed_days_at_start
                , d.drvd_directive_length_days_at_end AS imposed_days_at_end
                , d.directive_end_dated_date
                , d.is_directive_end_dated_flag
                , d.is_directive_completed_flag
                , d.prev_directive_type
                , d.next_directive_type
                , d.immediate_prev_directive_type
                , d.immediate_next_directive_type
            FROM dataset4_6 d
            LEFT JOIN [$(idimetaversion)].[cor].[offence_concord] ofn1
            ON d.[offence_code_at_start] = ofn1.[offence_code]
            LEFT JOIN [$(idimetaversion)].[cor].[offence_concord] ofn2
            ON d.[offence_code_at_end] = ofn2.[offence_code]
        )
        SELECT *
        INTO #management_spells_temp
        FROM dataset_final;

/* STEP 6: INSERT DATA INTO FINAL OUTPUT*/

        SELECT *

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

        FROM #management_spells_temp ;

 ;