0

I've been told to redo this as people are becoming confused.

I would like to go from this:

   RN  CalanderDateTime   PtID      EventDateTime    GapinMinutes
   1  2017-09-23 08:34   NULL          NULL              NULL
   2  2017-09-23 08:35   NULL          NULL              NULL
   3  2017-09-23 08:36   NULL          NULL              NULL
   4  2017-09-23 08:37    1      2017-09-23 08:37        1
   5  2017-09-23 08:38    1      2017-09-23 08:38        4
   6  2017-09-23 08:43    1      2017-09-23 08:43        3
   7  2017-09-23 08:44    NULL        NULL             NULL
   8  2017-09-23 08:45    NULL        NULL             NULL
   9  2017-09-23 08:46    NULL        NULL             NULL
   10 2017-09-23 08:47    2      2017-09-23 08:47        3
   11 2017-09-23 08:48    NULL       NULL             NULL
   12 2017-09-23 08:49    NULL       NULL             NULL
   13 2017-09-23 08:50    NULL       NULL             NULL

To this

   RN  CalanderDateTime   PtID      EventDateTime    GapinMinutes
   1  2017-09-23 08:34   NULL          NULL              NULL
   2  2017-09-23 08:35   NULL          NULL              NULL
   3  2017-09-23 08:36   NULL          NULL              NULL
   4  2017-09-23 08:37    1      2017-09-23 08:37        1
   5  2017-09-23 08:38    1      2017-09-23 08:38        4
   6  2017-09-23 08:43    1      2017-09-23 08:43        3
   7  2017-09-23 08:44    1          NULL             NULL
   8  2017-09-23 08:45    1          NULL             NULL
   9  2017-09-23 08:46    1          NULL             NULL
   10 2017-09-23 08:47    2      2017-09-23 08:47        3
   11 2017-09-23 08:48    2          NULL             NULL
   12 2017-09-23 08:49    2          NULL             NULL
   13 2017-09-23 08:50    2          NULL             NULL

It almost like smearing the PtID to fill the the next NULL placeholder rows based on the value contained in the GapinMinutes

  • @cyberblitz,why didn't you raise issue then ? Also you din't mention how much data will be process.how much time it takes.Now wait for my answer. – KumarHarsh Dec 20 '17 at 2:54
  • @KumarHarsh at the time i thought i had the solution. How much data, over 4 million rows.. – cyberblitz Dec 20 '17 at 3:28
1

Previous Requirement

Below query is far improve version of previous one.

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

let the index be commented now.you don't need the commented CTE1.

New Requirement, (Here your output require is quite change and I am only working on part of your dataset).

Main trick is to implement it seamlessly as part of your main query.

create table #ttt (RN int,CalanderDateTime datetime,PtID int,EventDateTime datetime,GapinMinutes int)
insert into #ttt values
(1  ,'2017-09-23 08:34',NULL ,        NULL          ,   NULL)
,(2  ,'2017-09-23 08:35',NULL ,        NULL          ,   NULL)
,(3  ,'2017-09-23 08:36',NULL ,        NULL          ,   NULL)
,(4  ,'2017-09-23 08:37', 1   ,  '2017-09-23 08:37'  ,     1)
,(5  ,'2017-09-23 08:38', 1   ,  '2017-09-23 08:38'  ,     4)
,(6  ,'2017-09-23 08:43', 1   ,  '2017-09-23 08:43'  ,     3)
,(7  ,'2017-09-23 08:44', NULL,       NULL           , NULL )
,(8  ,'2017-09-23 08:45', NULL,       NULL           , NULL )
,(9  ,'2017-09-23 08:46', NULL,       NULL           , NULL )
,(10 ,'2017-09-23 08:47', 2   ,  '2017-09-23 08:47'  ,     3)
,(11 ,'2017-09-23 08:48', NULL,      NULL            ,NULL  )
,(12 ,'2017-09-23 08:49', NULL,      NULL            ,NULL  )
,(13 ,'2017-09-23 08:50', NULL,      NULL            ,NULL  )

CREATE clustered INDEX x_ix11 ON #ttt(rn);
CREATE INDEX x_ix1 ON #ttt(ptid);

--drop index x_ix11 on #ttt

select t.RN ,t.CalanderDateTime 
, case when t.PtID is not null then t.ptid else ca.ptid end ptid
,t.EventDateTime ,t.GapinMinutes  
from #ttt t
outer apply(select top 1 t1.ptid 
from #ttt t1 
where t.rn>t1.rn and t1.PtID is not null 
and t.ptid is NULL
 order by t1.rn desc)ca

Minor change in query.

Index should not be created by understanding just part of dataset.

Here dataset is very small.But after creating index aim getting one Index Seek and one index scan i.e. query plan seem improve.

IMHO, given outer apply is one best way to do so (80%) .

But with million of rows already, outer apply increase the the rows by 50%. inequality operator t.rn>t1.rn is doing problem

May be it can be implemented from view itself.

It belong to which table and how many rows this table returns ?

Tried to optimize your main query as well as implemented new requirement

This part of query only indicate that what correct should be done,it may not run as it is.you have to fix the bug.

With VentHours   AS  
(
    SELECT sig.PatientID ptID, convert(DateTime,sig.Time) t,

        LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) LeadTime,

         LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) LeadPatient,
        p.rAdmitDate adt,
        p.rDischDate dit
        FROM Reporting.dbo.VIEW_Vent_Signals sig 
        inner join Reporting.dbo.ICU_Adult_Patients p ON sig.PatientID = p.ptID

),

    Select ts.ID rn
    , case when vh1.ptid is not null then vhi.ptid else vh.ptID end ptid
    , ts.DateTime,  case when vh.t is null then ts.DateTime else vh.t end t, vh.adt, vh.dit
    , Case When DateDiff(MINUTE,sig.Time,LeadTime BETWEEN 2 AND 720 THEN 1 ELSE 0 END et
    ,Case When sig.PatientID = LeadPatient AND DateDiff(MINUTE,sig.Time,LeadTime) between 0 and 720 
        THEN DateDiff(MINUTE,sig.Time,LeadTime) ELSE 0 END VGM
        ,vh1.ptid
    from Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = vh.t
    outer apply(select top 1 vh1.ptid 
from VentHours vh1 
where vh.ID>vh1.ID and vh1.PtID is not null
 order by vh1.id desc)ca
  • I can't insert entire table, its over 4 million rows. I'm only interested with inserting the PtID number into the following NULL placeholder rows – cyberblitz Dec 20 '17 at 3:35
  • Thanks for your solution but after trying it out it took even longer than using the CROSS APPLY solution, twice as long. Is there anyone who could help me with my current problem?? – cyberblitz Dec 20 '17 at 3:54
  • sorry for my ignorance but the reason i haven't answered your doubt is becasue your English isn't very good and i can't understand what you're trying to say – cyberblitz Dec 20 '17 at 3:56
  • sorry for language,i asked you need to throw table structure of your table along with any existing index.Secondly there is no CROSS APPLY in my script.do you mean first solution is little better than second one ? – KumarHarsh Dec 20 '17 at 4:02
  • 1
    Thanks, but it takes far too long to run, > 15mins. I had used a Quirky Update, and this runs far faster: UpdATE @MainSQL SET @v = PTID = CASE WHEN PTID IS NULL THEN @v ELSE PTID END – cyberblitz Dec 21 '17 at 21:35
1

After nearly two weeks nutting this out, I've finally done it, with very little performance issues. This takes at least 30 secs to run, at most 5 mins. This is pretty impressive considering the amount of data it's churning (over 5 million rows)

Here is the code:

With Digit AS 
(
SELECT A.Num + (B.Num*10) + (C.Num*100) + (D.Num * 1000) + (E.Num *10000) AS Num
  FROM Reporting.dbo.[ZeroTo9] as A 
  CROSS JOIN Reporting.dbo.[ZeroTo9] as B 
  CROSS JOIN Reporting.dbo.[ZeroTo9] as C 
  CROSS JOIN Reporting.dbo.[ZeroTo9] as D 
  CROSS JOIN Reporting.dbo.[ZeroTo9] as E
),

TimeDim as
(
     SELECT Pat.ptID, pat.rAdmitDate, pat.rDischDate, pat.VentStart, Pat.VentFin, DateAdd(minute, B.Num, pat.VentStart) AS minute_Start, b.Num
        FROM Reporting.dbo.ICU_Adult_Patients as Pat
        CROSS JOIN Digit b
        WHERE  DATEADD(minute, B.Num, pat.VentStart) between pat.VentStart and Pat.VentFin
),

VentHours  AS  
(
       SELECT p.Num rn,
       p.ptID,
       p.minute_Start,

       sig.PatientID, 
            sig.Time,
            Case When DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) BETWEEN 2 AND 720 THEN 1 ELSE 0 END EventType,
            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 vgm,
            p.rAdmitDate,
            p.rDischDate
            FROM TimeDim p
            left outer join Reporting.dbo.VIEW_Vent_Signals sig ON sig.PatientID = p.ptID and sig.Time = p.minute_Start
),

CrxApp AS
(
select c.rn, c.PtID
from VentHours c
cross apply
     (
        select rn 
        from VentHours c1 
        where c1.VGM is not null and c1.VGM > 720 and (c.rn > c1.rn and c.rn < (c1.rn + c1.VGM + 1))
     ) ca
 ),
 FinalSQL as

 (

  select c.RN, c.ptID, c.minute_Start, c.Time, c.rAdmitDate, c.rDischDate, c.EventType, c.VGM, dateadd(hour,datediff(hour,0,c.minute_Start),0) as [DateTimehour]
     from VentHours c
     where not exists (select rn from CrxApp c1 where c.rn = c1.rn) 
)

      select PtID, [DateTimehour], count([DateTimehour]) vent_mins
 from FinalSQL a
 Group By  PtID, [DateTimehour]
 Having count([DateTimehour]) > 0 

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.