Continuous Values

Another interesting SQL question one of my students has sent me recently. This question tests your understanding of SQL analytic fucntions. A bank company has 3 branches in Tel Aviv. In every branch there are 2 ATMs. The following table describes the periods of time in which these ATMs were active.

As you can see –

• The ATMs of branch number 1 were active between 08:30 and 09:55 AM (without any non-active period).
• The ATMs of branch number 2 were active between 09:20 and 09:25 AM. Then after a non-active period of 5 minutes, they were active from 09:30 to 9:55 AM. Finally, after another non-active period of 35 minutes, they were active between 10:30 and 11:55 AM.
• The ATMs of branch number 3 were active between 08:00 to 09:50 AM, after a non-active period of 90 minutes, they were active from 11:20 to 12:15 AM.

Write a query that will retrieve the continuous time periods in which these ATMs were active.  This query should retrieve:

• 1 row for the first bank – 08:30-09:55
• 3 rows for the second bank – 09:20-09:25, 09:30-09:55, 10:30-11:55.
• 2 rows for the third bank – 08:00-09:50, 11:20-12:50.

CREATE TABLE time_table
(bank_id int,
 atm_id int,
 strt_date datetime,
 end_date datetime) 

 INSERT INTO time_table VALUES (1, 1, '2015/04/12 08:30' , '2015/04/12 09:15')
 INSERT INTO time_table VALUES (1, 2, '2015/04/12 09:00' , '2015/04/12 09:45')
 INSERT INTO time_table VALUES (1, 1, '2015/04/12 09:20' , '2015/04/12 09:55')

 INSERT INTO time_table VALUES (2, 1, '2015/04/12 09:20' , '2015/04/12 09:25')
 INSERT INTO time_table VALUES (2, 2, '2015/04/12 09:30' , '2015/04/12 09:55')
 INSERT INTO time_table VALUES (2, 1, '2015/04/12 10:30' , '2015/04/12 11:30')
 INSERT INTO time_table VALUES (2, 2, '2015/04/12 11:15' , '2015/04/12 11:55')

 INSERT INTO time_table VALUES (3, 1, '2015/04/12 08:00' , '2015/04/12 09:00')
 INSERT INTO time_table VALUES (3, 2, '2015/04/12 08:55' , '2015/04/12 09:20')
 INSERT INTO time_table VALUES (3, 1, '2015/04/12 09:15' , '2015/04/12 09:50')
 INSERT INTO time_table VALUES (3, 1, '2015/04/12 11:20' , '2015/04/12 12:15')

-- part 1, create a view to identify the contiguousy
 CREATE VIEW contiguous_vu
 AS
 SELECT bank_id, strt_date, 
              ISNULL(end_date, '9999-12-31') end_date,
              CASE
              WHEN strt_date > MAX(ISNULL(end_date, '9999-12-31'))
                               over(PARTITION BY bank_id ORDER BY strt_date 
                                    ROWS BETWEEN UNBOUNDED PRECEDING
                                    AND 1 preceding)
              THEN 1
              END contiguous_identifier
              FROM time_table
-- part 2, group the different parts 
SELECT bank_id, MIN(strt_date), MAX(end_date)
FROM
    (   SELECT  bank_id, strt_date, end_date, contiguous_identifier ,
        SUM(contiguous_identifier) over(PARTITION BY bank_id ORDER BY strt_date) contiguousy
        FROM contiguous_vu
    )   TAB 
GROUP BY bank_id, contiguousy
ORDER BY 1, 2