Insert Data Into NULL Rows from Previous Row (t-SQL)

Question

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

• KumarHarsh: @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.
• cyberblitz: @KumarHarsh at the time i thought i had the solution. How much data, over 4 million rows..

Answer 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

• cyberblitz: 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: 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: 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
• KumarHarsh: 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 ?
• cyberblitz: 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

Answer 2

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