Remove Dates from table t-sql

All we need is an easy explanation of the problem, so here it is.

I need the table to go from this:

 CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:39  NULL          NULL             NULL
 2017-09-23 08:40  NULL          NULL             NULL
 2017-09-23 08:41  NULL          NULL             NULL
 2017-09-23 08:42  NULL          NULL             NULL
 2017-09-23 08:43    1      2017-09-23 08:43       3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

to this:

CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:43    1      2017-09-23 08:43        3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

In this example i would want the next 4 rows removed from the row where 4 is shown in the ‘GapinMinutes’ column (but this can be any number > 3 and therefore whatever number of rows removed is based on the number shown in the ‘GapinMinutes’ column, so if the column has a 5, remove the next five rows, if its a 7 remove the next 7 rows, if its a 10, remove the next 10 rows but if its a a number <= 3, keep the next 1,2 or 3 rows…

Here’s the script i have so far:

With VentHours (ptID, t, ET, VGM)  AS  
(
    SELECT sig.PatientID, sig.Time,


        Case When sig.PatientID = LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) AND DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) between 0 and 720 THEN DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) ELSE 0 END Vent_Gap_Mins

        FROM GCUH_Reporting.dbo.VIEW_GCUH_Vent_Signals sig 
        inner join GCUH_Archive.dbo.Patients p ON sig.PatientID = p.PatientID
),

JoinTime as 
(
    Select *
    from GCUH_Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = convert(DateTime,vh.t) 
)

select jt.DateTime, jt.ptid, jt.et, jt.vgm



from JoinTime jt

--where  jt.ptID   is not null

order by jt.DateTime

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is Sql server 2008 script.i think it can be easily translated to other rdbms.

Secondly your sample data is wrong.It should be 08:43 instead of 09:43

create table #t (CalanderDateTime datetime, PtID int,EventDateTime  datetime,GapinMinutes int)
insert into #t VALUES
 ('2017-09-23 08:34',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:35',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:36',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:37',    1  ,    '2017-09-23 08:30'   ,  1 )
 ,('2017-09-23 08:38',    1  ,    '2017-09-23 08:35'   ,  4 )
 ,('2017-09-23 08:39',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:40',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:41',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:42',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:43',    1  ,    '2017-09-23 09:40'   , 3  )
 ,('2017-09-23 08:44',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:45',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:46',  NULL ,         NULL          ,NULL  )

 ;WITH CTE as
 (
  select * 
 ,ROW_NUMBER()OVER(ORDER BY CalanderDateTime)rn
 from #t
 )
,CTE1 AS(
 select c.rn from cte c
cross apply(select rn from cte c1 
 where c1.GapinMinutes is not null and c1.GapinMinutes>3 and  
 (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1)) )ca
 )
 select * from cte c
 where not exists (select rn from cte1  c1 where c.rn=c1.rn)

 drop TABLE #t

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply