0

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
  • Please edit the question to show what the desired output looks like. – Michael Green Dec 12 '17 at 10:28
  • Your table needs a primary key to ensure the records are physically ordered, otherwise, your requirement of "[x] rows AFTER ..." is not valid. – jyao Dec 12 '17 at 18:16
  • Ok, so if I add a primary key, how would I go about achieving what I'm after – cyberblitz Dec 12 '17 at 19:38
  • Is it a typo in your sample data in the CalanderDateTime column? Do you really have repeating values in that column? Can you define "next" row using CalanderDateTime for sorting? – Vladimir Baranov Dec 12 '17 at 23:17
  • Yea, its a typo. The data in that column is unique. What do you mean by next row sorting?? – cyberblitz Dec 12 '17 at 23:28
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
  • You, my friend, are a genius!! Perfect. Thanks – cyberblitz Dec 13 '17 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.