How to check if a set rows is covering the whole time range

Question

I have a table in which there are two columns StartTime and EndTime. Values are filled in it as:

 declare @tbl Table(
colA VARCHAR(50),
colS VARCHAR(50),
DATES DATE,
STARTTIME TIME,
ENDTIME TIME,
ID BIGINT NOT NULL IDENTITY(1,1)
)

INSERT INTO @tbl
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'12:45'),convert(TIME,'13:30')
UNION ALL
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'13:15'),convert(TIME,'13:45')
UNION ALL
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'13:30'),convert(TIME,'16:50')
UNION ALL
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'13:15'),convert(TIME,'13:50')

Hence, I want to check whether StartTime and EndTime in all rows of this table covers the whole time period between Minimum StartTime and Maximum EndTime of this table. Hence from these rows, it is clear that it is true. However for the following set of rows, it wont be true because of time gap in them. So I just want True or False as the result.

INSERT INTO @tbl
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'08:45'),convert(TIME,'09:15')
UNION ALL
SELECT 'A','S',convert(date,'2015-09-21'),convert(TIME,'11:10'),convert(TIME,'11:25')

I have managed to complete all other tasks and this is the end result of them. This is the final task and I am completely clueless how to do it. Any help would be appreciated.

[Packing Intervals by Itzik Ben-Gan](http://blogs.solidq.com/en/sqlserver/packing-intervals/) — Vladimir Baranov, Jun 25 '16 at 13:24

score 2 · Answer 1 · edited Jun 26 '16 at 00:13

Generally intervals may be of any length, so comparing adjacent rows is far from complete solution. Besides Itzik Ben-Gan's Packing Intervals solution cited in above comment another complete solution is to check every minute (or other granualarity).

select uncoverdMinutes=count(*) -- 0 is true
from(
    select colA, colS, t.m
    from ( 
        select colA, colS
          , mst = min(starttime)
          , cnt = datediff(minute, min(starttime), max(endtime)) + 1
        from @tbl
        group by colA, colS 
    ) prm 
    cross apply (
        select top(prm.cnt)
            m = dateadd(minute
                      ,row_number() over(order by (select null)) - 1
                      ,prm.mst)                                    
        from sys.all_objects -- use tally table instead, if you have one
        ) t
  ) mi
where not exists (
    select 1 
    from @tbl t
    where mi.m between t.starttime and t.endtime)

nazark · Answer 2 · 2016-06-25T13:15:01.020

1

try

;with a as (select *,row_number() over (order by DATES,starttime) rn from @tbl)

select a.*,
case when isnull(b.endtime,a.endtime)>=a.STARTTIME then 'true' else 'false' end 
 from a left join a b on a.rn=b.rn+1

edited Jun 25 '16 at 13:15

answered Jun 25 '16 at 12:23

nazark

1,240
2
10
15

Thanks for the reply, but I have sql server 2008 and not 2012, so I don't think lag and lead work in sql 2008. – Hemant Sisodia Jun 25 '16 at 12:28
try the updated self join query, i have only sql 2012 with me – nazark Jun 25 '16 at 13:05

gofr1 · Answer 3 · 2016-06-25T15:14:56.427

If there is enough to check if next row's time interval is crossing with the next row interval then OUTER APPLY should do things for you:

SELECT  t.*, 
        CASE WHEN t.ENDTIME between p.STARTTIME and p.ENDTIME OR p.ENDTIME IS NULL THEN 'TRUE' ELSE 'FALSE' END as Seq
FROM @tbl t
OUTER APPLY (
    SELECT TOP 1 * 
    FROM @tbl 
    WHERE t.STARTTIME < STARTTIME and t.colA = colA and t.colS = colS and t.DATES = DATES
    ORDER BY STARTTIME ASC) p
ORDER BY t.STARTTIME

Output for given data:

colA    colS    DATES       STARTTIME           ENDTIME             ID  Seq
A       S       2015-09-21  08:45:00.0000000    09:15:00.0000000    5   FALSE
A       S       2015-09-21  11:10:00.0000000    11:25:00.0000000    6   FALSE
A       S       2015-09-21  12:45:00.0000000    13:30:00.0000000    1   TRUE
A       S       2015-09-21  13:15:00.0000000    13:45:00.0000000    2   TRUE
A       S       2015-09-21  13:15:00.0000000    13:50:00.0000000    4   TRUE
A       S       2015-09-21  13:30:00.0000000    16:50:00.0000000    3   TRUE

score 1 · Accepted Answer · answered Jun 25 '16 at 14:31

-- The last column will be 1 with your first example data and 0 with your second.

    SELECT t.*, 
    CASE WHEN t.ENDTIME < m.MaxStartTime THEN 0 
         WHEN t.STARTTIME > m.MinEndTime THEN 0 
         ELSE 1 END AS Covered
    FROM @tbl AS t
    JOIN 
         (SELECT DATES,
                 MIN(ENDTIME) MinEndTime,
                 MAX(STARTTIME) MaxStartTime
                 FROM @tbl
                 GROUP BY DATES) AS m
    ON t.DATES = m.DATES

How to check if a set rows is covering the whole time range

4 Answers4