0

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.

Sample Data Sample Data Continued

Now I want output like this :-

Output

2

Let assume by using below input:

enter image description here

And the expected result as:

enter image description here

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)) > 0 THEN 'Start'
                WHEN CONVERT(int,t1.hoops) - CONVERT(int,Coalesce(t2.hoops,t1.hoops)) < 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] <> '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 <> '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
  • Thank you so much, expected result is correct. Can you help me getting the result when in the continuous sequence of 1's there is a single 0 in between. we have to ignore single zero if it is more than that then break it into diff start or end time otherwise continue as a series. – Puskar Jun 25 at 20:32
  • @Puskar: it was already in my script. My sample was having the case with 0 in between a series of 1 also. – Dat Nguyen Jun 26 at 0:02
  • Ngyuen..I understand...but What I am saying is consider 1st output of urs for 25-06-2019 - 9.30 AM - 11AM. If at 10 AM there is zero and 10.30 again 1 until 11 AM, we still have to consider 11 AM. But, in case both 10 and 10.30 are 0 and 1 starts from 11.30 then end date will be at 10 and next start will be at 11.30 – Puskar Jun 26 at 0:42
  • @Puskar: I have updated my answer. Hope that helps. Cheers – Dat Nguyen Jun 26 at 1:19
  • Thank you so much!!! It's perfectly fine. – Puskar Jun 26 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.