SQL Server – Query to Find Multiple Start and End Times on a Single Day

sql serversql-server-2008sql-server-2008-r2sql-server-2012subquery

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

Best Answer

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

Related Solutions

SQL Server – Insert Data Between 30 Minute Intervals

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.)

DECLARE @i INT = 30; -- interval in minutes

DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME;
SELECT @ft = MIN([Start Time]), @lt = MAX([End Time]) FROM dbo.aTable -- WHERE?;

;WITH d1(dt) AS
(
  SELECT TOP (DATEDIFF(MINUTE,@ft,@lt)/@i+2) 
    DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(MINUTE,@i*(ROW_NUMBER() 
    OVER (ORDER BY [object_id])-1),@ft))/@i*@i,0)
  FROM sys.all_columns
), d2(s,e) AS (SELECT dt,DATEADD(MINUTE,@i,dt) FROM d1
), t AS
(
  SELECT [User],[Start Time],[End Time],
    StartInterval = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Start Time])/@i*@i, 0),
    EndInterval   = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [End Time])/@i*@i, 0)
  FROM dbo.aTable -- WHERE?
),
n AS
(
  SELECT d2.s, d2.e, [User], [Start Time], [End Time],
    sr = CASE WHEN [Start Time] >= d2.s AND [Start Time] < d2.e THEN 1 END,
    er = CASE WHEN [End Time] > d2.s AND [End Time] <= d2.e THEN 1 END
   FROM d2 INNER JOIN t ON d2.s BETWEEN t.StartInterval AND t.EndInterval
 )
 SELECT [Interval From] = s, [Interval To] = e, [User], 
   [Elapsed Time] = CASE WHEN sr = 1 OR er = 1 THEN
       DATEDIFF(MINUTE, CASE WHEN sr = 1 THEN [Start Time] ELSE s END,
       CASE WHEN er = 1 THEN [End Time] ELSE e END)
   WHEN s = [End Time] THEN 0 ELSE @i END
 FROM n ORDER BY [User], [Interval From];

SQL Server – How to Detect If Two Time Periods Overlap

I realize this has been answered before on Stack Overflow. It got me going in the right direction.

I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering.

For anyone in the future who has a similar issue:

My new StartDate will be recorded as 08/22/2015 09:00.000 instead of 08/22/2015 00:00.000
The EndDate will be 8/23/2015 13:00:00.000

I can then compare as per the linked answer:

(
    (@Start > StartDate and @Start < EndDate)
    OR (@End > StartDate and @End < EndDate)
    OR (@Start < StartDate and @End >EndDate)
)

Best Answer

Related Solutions

SQL Server – Insert Data Between 30 Minute Intervals

SQL Server – How to Detect If Two Time Periods Overlap

Related Question