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.

Answer 1

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