SQL Server 2014 – How to Insert Data into NULL Rows from Previous Row

sql serversql server 2014t-sql

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

Best Answer

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
Related Question