CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
Xander Kage
Xander Kage

Reputation: 199

find time slots with sql

I have a scenario (SQL 2008) where I need to find the occupied timeframes /non-gaps from the below table. For e.g . I have created this dummy table. 

CREATE TABLE Job
(
JobID INT NOT NULL,
  WorkerID INT NOT NULL,
  JobStart DATETIME NOT NULL,
  JobEnd DATETIME NOT NULL
);

INSERT INTO Job (JobID, WorkerID, JobStart, JobEnd)
    VALUES (1, 25, '2012-11-17 16:00', '2012-11-17 17:00'),
        (2, 25, '2012-11-17 16:00', '2012-11-17 16:50'),
        (3, 25, '2012-11-19 18:00', '2012-11-19 18:30'),
        (4, 25, '2012-11-19 17:30', '2012-11-19 18:10'),
        (5, 26, '2012-11-18 16:00', '2012-11-18 17:10'),
        (6, 26, '2012-11-18 16:00', '2012-11-19 16:50');

so for this , the qry shd return data like this:

WorkerID | StartDate | EndDate
25      2012-11-17 16:00   2012-11-17 17:00
25      2012-11-17 17:30   2012-11-17 18:30
26      2012-11-18 16:00    2012-11-18 17:10

I am able to get the result but I am using while loop and its a pretty iterative method. Any chance , I can avoid using while to get the result

Upvotes: 1

Views: 1579

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is a Packing Date and Time Interval problem. Itzik Ben-Gan has published an article that provides many solutions to this problem. Using one of Itzik's solution, here is a query to solve your problem:

SQL Fiddle

WITH C1 AS(
    SELECT
        JobID, WorkerId, JobStart AS ts, +1 AS type, NULL AS e,
        ROW_NUMBER() OVER(PARTITION BY WorkerId ORDER BY JobStart, JobId) AS s
    FROM Job
    UNION ALL
    SELECT
        JobID, WorkerId, JobEnd AS ts, -1 AS type,
        ROW_NUMBER() OVER(PARTITION BY WorkerId ORDER BY JobEnd, JobId) AS e,
        NULL AS s
    FROM Job
),
C2 AS(
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY WorkerId ORDER BY ts, type DESC, JobId) AS se
    FROM C1
),
C3 AS(
    SELECT ts, WorkerId,
        FLOOR((ROW_NUMBER() OVER(PARTITION BY WorkerId ORDER BY ts) - 1) / 2 + 1) AS grpnum
    FROM C2
    WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0
)
SELECT 
    WorkerId,
    MIN(ts) AS StartDate,
    MAX(ts) AS EndDate
FROM C3
GROUP BY WorkerID, grpnum
ORDER BY WorkerID

Result

WorkerId    StartDate               EndDate
----------- ----------------------- -----------------------
25          2012-11-17 16:00:00.000 2012-11-17 17:00:00.000
25          2012-11-19 17:30:00.000 2012-11-19 18:30:00.000
26          2012-11-18 16:00:00.000 2012-11-19 16:50:00.000

Upvotes: 4

Related Questions