Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each  day is divided into 24hrs/2 = 48. There is a time slot.




Now I want output like this :-
Let assume by using below input:



And the expected result as:



AND, my script as to do it as below:

DROP TABLE IF EXISTS Timeline
GO
CREATE TABLE Timeline (agent_key int, agent_date date, agent_date_30_min datetime, hoops bit)
GO
INSERT INTO dbo.Timeline ( agent_key, agent_date, agent_date_30_min, hoops )
VALUES 
( 5, '2019-06-25', '2019-06-25 09:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 09:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 12:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 12:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 15:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 5, '2019-06-26', '2019-06-26 12:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 6, '2019-06-25', '2019-06-25 15:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 6, '2019-06-26', '2019-06-26 12:00:00', 1 )
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DROP TABLE IF EXISTS #temp
DROP TABLE IF EXISTS #temp2

SELECT      t1.*,
            Coalesce(t2.hoops,t1.hoops) AS next_hoops,
            CASE 
                WHEN CONVERT(int,t1.hoops) - CONVERT(int,Coalesce(t2.hoops,t1.hoops)) &gt; 0 THEN 'Start'
                WHEN CONVERT(int,t1.hoops) - CONVERT(int,Coalesce(t2.hoops,t1.hoops)) &lt; 0 THEN 'End' 
                ELSE 'No changes'
            END AS [status],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate]
INTO        #temp
FROM        TimeLine t1
LEFT JOIN   TimeLine t2 
    ON      t1.agent_key = t2.agent_key
    AND     t1.agent_date_30_min = DATEADD(MINUTE,30,t2.agent_date_30_min)

SELECT      t1.agent_key, 
            t1.agent_date,
            t1.agent_date_30_min,
            t1.hoops,
            (CASE WHEN t1.[idx_keydate] = 1 THEN 'Start' ELSE t1.[status] END) AS [status],
            (CASE WHEN t1.[status] = 'Start' OR t1.[idx_keydate] = 1 THEN t1.agent_date_30_min END) AS [start],
            (CASE WHEN t1.[status] = 'End' THEN DATEADD(MINUTE,-30,t1.agent_date_30_min) END) AS [end],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key ORDER BY t1.agent_date, t1.agent_date_30_min) AS [idx_key]
INTO        #temp2
FROM        #temp t1
WHERE       t1.[idx_keydate] = 1
    OR      t1.[status] &lt;&gt; 'No changes'

--INPUT
SELECT      * 
FROM        Timeline
--RESULT
SELECT      t1.agent_key,
            t1.agent_date,
            t1.[start],
            Coalesce(t2.[end],t1.[start]) AS [end]
FROM        #temp2 t1
LEFT JOIN   #temp2 t2
    ON      t2.agent_key = t1.agent_key
    AND     t2.[idx_key] = t1.[idx_key]+1
    AND     t2.[idx_keydate] = t1.[idx_keydate]+1
WHERE       t1.[start] IS NOT NULL


Update with new request: single zero between series of 1 will be ignored.

DROP TABLE IF EXISTS Timeline
GO
CREATE TABLE Timeline (agent_key int, agent_date date, agent_date_30_min datetime, hoops bit)
GO
INSERT INTO dbo.Timeline ( agent_key, agent_date, agent_date_30_min, hoops )
VALUES 
( 5, '2019-06-25', '2019-06-25 09:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 09:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 11:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 12:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 12:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 15:00:00', 1 ),

( 5, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 5, '2019-06-26', '2019-06-26 12:00:00', 1 ),

( 6, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 6, '2019-06-25', '2019-06-25 15:00:00', 1 ),

( 6, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 6, '2019-06-26', '2019-06-26 12:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 12:30:00', 1 )
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DROP TABLE IF EXISTS #temp
DROP TABLE IF EXISTS #temp2

SELECT      t1.*,
            Coalesce(t2.hoops,t1.hoops) AS prev_hoops,
            Coalesce(t3.hoops,t2.hoops,t1.hoops) AS prev_prev_hoops,
            CASE 
                WHEN t1.hoops = 1 AND Coalesce(t2.hoops,t1.hoops) = 0 AND Coalesce(t3.hoops,t2.hoops,t1.hoops) = 0
                    THEN 'Start'
                WHEN t1.hoops = 0 AND Coalesce(t2.hoops,t1.hoops) = 0 AND Coalesce(t3.hoops,t2.hoops,t1.hoops) = 1
                    THEN 'End' 
                ELSE 'No changes'
            END AS [status],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min DESC) AS [idx_keydate_desc]
INTO        #temp
FROM        TimeLine t1
LEFT JOIN   TimeLine t2 
    ON      t1.agent_key = t2.agent_key
    AND     t1.agent_date_30_min = DATEADD(MINUTE,30,t2.agent_date_30_min)
LEFT JOIN   TimeLine t3
    ON      t2.agent_key = t3.agent_key
    AND     t2.agent_date_30_min = DATEADD(MINUTE,30,t3.agent_date_30_min)

SELECT      t1.agent_key, 
            t1.agent_date,
            t1.agent_date_30_min,
            t1.hoops,
            CASE WHEN (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN 'Start'
                WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1) THEN 'End'
                ELSE t1.[status]
            END AS [status],
            (CASE WHEN t1.[status] = 'Start' OR (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN t1.agent_date_30_min END) AS [start],
            CASE WHEN t1.[status] = 'End' THEN DATEADD(MINUTE,-60,t1.agent_date_30_min) 
                WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1)  THEN t1.agent_date_30_min
            END AS [end],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
            ROW_NUMBER() OVER (PARTITION BY t1.agent_key ORDER BY t1.agent_date, t1.agent_date_30_min) AS [idx_key]
INTO        #temp2
FROM        #temp t1
WHERE       CASE WHEN (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN 'Start'
                WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1) THEN 'End'
                ELSE t1.[status]
            END &lt;&gt; 'No changes'

--INPUT
SELECT      * 
FROM        Timeline
--RESULT
SELECT      t1.agent_key,
            t1.agent_date,
            t1.[start],
            Coalesce(t2.[end],t1.[start]) AS [end]
FROM        #temp2 t1
LEFT JOIN   #temp2 t2
    ON      t2.agent_key = t1.agent_key
    AND     t2.[idx_key] = t1.[idx_key]+1
    AND     t2.[idx_keydate] = t1.[idx_keydate]+1
WHERE       t1.[start] IS NOT NULL