Coalesce date ranges if < 24 hours between episodes and intervening either nothing or a lower level of support (higher EPI_CAT number)

coalesceRespEpisodes.sql

-- =============================================
-- Author:      Brent McSharry
-- Create date: Aug 2020
-- Description: Any periods off respiratory support OR with less respiratory support
--              (i.e. lower resp support = having a higher integer categorisation EPI_CAT)
--              which lasts for less than 24 hours, results in the episodes with greater
--              levels of support coalescing into a single episode
-- =============================================
CREATE FUNCTION [dbo].[ANZICSEpisodeSubmissionFields]
(     
       -- Add the parameters for the function here
       @start DATE,
       @end DATE
)

RETURNS @anzpicEpisodes TABLE(
       id int identity primary key,
       [ICU_NO] int not null,
       [EPI_CAT] tinyint not null,
       [EPICOM_DT] datetime2(0) not null,
       [EPICEAS_DT] datetime2(0) not null,
       EXTUB tinyint
)
AS
BEGIN
       DECLARE @anzicsEpiOverlap TABLE(
              id_h int not null,
              com_h DATETIME2(0) not null,
              ceas_h DATETIME2(0) not null,
              com_l DATETIME2(0) not null,
              ceas_l DATETIME2(0) not null,
              ICU_NO int not null,
              epicat_h tinyint not null
       );

       /****** Any episode < 24 hours apart must be grouped into a single episode ******/
       WITH e as (
              SELECT en.[ICU_NO]
                     ,[EPI_CAT]
                     ,[EPICOM_DT]
                     ,[EPICEAS_DT]
                     ,CASE
                           -- If the prior level of support was a lower number (more support) then the current episode (with less support)
                           -- should not expand datetime boundaries
                           WHEN LAG(EPI_CAT, 1) OVER (PARTITION BY en.ICU_NO ORDER BY EPICOM_DT) < EPI_CAT
                                  -- if null (beginning of partition) or > 24 hour gap then create a new episode.
                                  -- 2084 has NO intrinsic value other than to ensure null returns true,
                                  -- and any number > 1440 would have sufficed
                                  OR COALESCE(DATEDIFF(n, LAG(EPICEAS_DT, 1) OVER (PARTITION BY en.ICU_NO, EPI_CAT ORDER BY EPICOM_DT), EPICOM_DT), 2048) > 1440
                           THEN 1
                           ELSE 0
                      END newepisode
              FROM [dbo].tblPICUEpisodeNew en
                     JOIN dbo.tblPICU t ON t.ICU_NO = en.ICU_NO
              WHERE  @start <= DIS_DT AND DIS_DT < @end AND EPI_CAT <=3
       ),

       s as (
              SELECT *, SUM(newEpisode) OVER (ORDER BY ICU_NO, EPI_CAT, EPICOM_DT) epi_number
              FROM e
       )

       INSERT INTO @anzpicEpisodes
       SELECT MIN(ICU_NO) ICU_NO, MIN(EPI_CAT) EPI_CAT, MIN(EPICOM_DT) EPICOM_DT, MAX(EPICEAS_DT) EPICEAS_DT, 9 EXTUB
       FROM s
       GROUP BY epi_number

       /* NOW get rid of overlaps */
       insert into @anzicsEpiOverlap
       SELECT j1.id id_h, j1.EPICOM_DT com_h, j1.EPICEAS_DT ceas_h,
              j2.EPICOM_DT com_l, j2.EPICEAS_DT ceas_l, j1.ICU_NO, j1.EPI_CAT epicat_h
       FROM @anzpicEpisodes j1
              JOIN @anzpicEpisodes j2
                     ON (j1.ICU_NO = j2.ICU_NO AND j1.EPI_CAT > j2.EPI_CAT AND j1.EPICOM_DT <= j2.EPICEAS_DT and j1.EPICEAS_DT >= j2.EPICOM_DT)
       ORDER BY j2.EPI_CAT DESC

       DELETE e
       FROM @anzpicEpisodes e
       JOIN @anzicsEpiOverlap o ON e.id = o.id_h
       WHERE com_l <= com_h AND ceas_h <= ceas_l

       UPDATE e SET e.EPICEAS_DT = o.com_l
       FROM @anzpicEpisodes e
       JOIN @anzicsEpiOverlap o ON e.id = o.id_h
       WHERE com_h < com_l

       UPDATE e SET e.EPICOM_DT = o.ceas_l
       FROM @anzpicEpisodes e
       JOIN @anzicsEpiOverlap o ON e.id = o.id_h
       WHERE ceas_l < ceas_h AND com_l <= com_h

       /*
       if the higher number (lower resp support) surrounds (begins before and ends after)
       the 1st update statement above will create the earlier episode
       but we will need to add the later episode of lower support
       */
       INSERT INTO @anzpicEpisodes
       SELECT ICU_NO, epicat_h, ceas_l, ceas_h, 9
              FROM @anzicsEpiOverlap
              WHERE com_h < com_l AND ceas_l < ceas_h

       -- finaly add back in the episodes of intubation and ECMO, which should not have been coalesced into single episodes
       INSERT INTO @anzpicEpisodes
       SELECT en.[ICU_NO]
                     ,[EPI_CAT]
                     ,[EPICOM_DT]
                     ,[EPICEAS_DT]
                     ,EXTUB
       FROM [dbo].[tblPICUEpisodeNew] en
              JOIN dbo.tblPICU t ON t.ICU_NO = en.ICU_NO
       WHERE  @start <= DIS_DT AND DIS_DT < @end AND EPI_CAT > 3
       RETURN
END