T-SQL – How to Remove Dates from a Table

t-sql

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

Best Answer

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

Related Solutions

Sql-server – Query Performance Issue

You say

Above plan is for subid = 11 or 7 in @t table variable

I think you may be under a misapprehension here. SQL Server does not look at the contents of the table variable and choose a plan based upon the values it contains.

The statement is compiled before the table variable contains any rows at all and you will get the same plan (that assumes a single row) regardless of whether it eventually contains 2 (and would match 95.5% of the rows) or 1 (and would match only 0.0008%).

The table variable may of course also contain multiple rows but SQL Server will not take account of that except if you use the OPTION (RECOMPILE) hint and even then there are no statistics on table variables so it cannot take any account of actual values.

Some alternate plans are below

plan 1

plan 2

These require finding all matching rows and sorting them.

Because NCx_1 is not declared as a unique index the include(QueueItemID) is ignored (as explained in More About Nonclustered Index Keys) and QueueItemID gets added as an index key column instead. This means that SQL Server can seek on IsProcessed, QCode and the matching rows will be ordered by QueueItemID.

The plan in your question therefore avoids a sort operation but performance is entirely reliant upon how many rows in practice need to be evaluated before the first one matching the SubID IN (SELECT SubID FROM @t) predicate is found and the range seek can stop.

Of course this can vary wildly depending on how common the SubID values contained in @t are and whether there is any skew in the distribution of these values with respect to QueueItemID (You say that both around 350k rows match the seek predicate and that around 350k end up being seeked so for SubID = 7 it sounds like these are all at the end or perhaps no rows match at all - which would be the worse case for this plan).

It would be interesting to know what the estimated number of rows coming out of the seek is. Presumably this is much less than 350,000 and thus SQL Server chooses the plan you see based on this estimated cost.

If the table variable will always just have few rows you might find this rewrite works better for you.

SELECT TOP 1 QueueItemID
FROM   @t
       CROSS APPLY (SELECT TOP 1 t.QueueItemID
                    FROM   QueueTable t
                    WHERE  t.IsProcessed = 0
                           AND t.QCode = 'USA'
                           AND SubID = [@t].SubID
                    ORDER  BY t.QueueItemID) CA 
ORDER BY QueueItemID

For me it gives the plan below where it seeks into the index on subid,isprocessed,qcode,queueitemid as many times as you have rows in the table variable. It is similar to the first plan shown but may be slightly more efficient as each seek stops after the first row is returned.

plan

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

Best Answer

Related Solutions

Sql-server – Query Performance Issue

Sql-server – Why does OBJECT_ID return NULL in a computed column

Related Question