Get Consecutive time slots per Employee

July 28, 2022

I have a table called slots with identity (id).

ID  Employee    Times                   Available
1   Anna        1900-01-01 07:00:00.000    0
2   Anna        1900-01-01 07:15:00.000    0
3   Anna        1900-01-01 07:30:00.000    1
4   Anna        1900-01-01 07:45:00.000    1
5   Anna        1900-01-01 08:00:00.000    1
6   Anna        1900-01-01 08:15:00.000    0
7   Charley     1900-01-01 07:00:00.000    0
8   Charley     1900-01-01 07:15:00.000    1
9   Charley     1900-01-01 07:30:00.000    1
10  Charley     1900-01-01 07:45:00.000    1
11  Charley     1900-01-01 08:00:00.000    0
12  Charley     1900-01-01 08:15:00.000    0
13  Charley     1900-01-01 08:30:00.000    0
14  Charley     1900-01-01 08:45:00.000    1
15  Charley     1900-01-01 09:00:00.000    0

and I need to get the time periods that each Employee has Available = 1

Anna    1900-01-01 07:30:00.000 1900-01-01 08:00:00.000
Charley 1900-01-01 07:15:00.000 1900-01-01 07:45:00.000
Charley 1900-01-01 08:45:00.000 1900-01-01 08:45:00.000

I did find some help here but for the life of me, I cannot make it work

>Solution :

This is a classic gaps and island issue. It’s been a long time since I’ve used SQL Server 2008 (R2) (as I mentioned, it’s been completely unsupported for 3 years now), but I think that such logic is still available:

WITH CTE AS(
    SELECT ID,
           Employee,
           Times,
           Available,
           ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Times) - 
           ROW_NUMBER() OVER (PARTITION BY Employee, Available ORDER BY Times) AS Island
    FROM (VALUES(1 ,'Anna   ',CONVERT(datetime,'1900-01-01T07:00:00.000'),0),
                (2 ,'Anna   ',CONVERT(datetime,'1900-01-01T07:15:00.000'),0),
                (3 ,'Anna   ',CONVERT(datetime,'1900-01-01T07:30:00.000'),1),
                (4 ,'Anna   ',CONVERT(datetime,'1900-01-01T07:45:00.000'),1),
                (5 ,'Anna   ',CONVERT(datetime,'1900-01-01T08:00:00.000'),1),
                (6 ,'Anna   ',CONVERT(datetime,'1900-01-01T08:15:00.000'),0),
                (7 ,'Charley',CONVERT(datetime,'1900-01-01T07:00:00.000'),0),
                (8 ,'Charley',CONVERT(datetime,'1900-01-01T07:15:00.000'),1),
                (9 ,'Charley',CONVERT(datetime,'1900-01-01T07:30:00.000'),1),
                (10,'Charley',CONVERT(datetime,'1900-01-01T07:45:00.000'),1),
                (11,'Charley',CONVERT(datetime,'1900-01-01T08:00:00.000'),0),
                (12,'Charley',CONVERT(datetime,'1900-01-01T08:15:00.000'),0),
                (13,'Charley',CONVERT(datetime,'1900-01-01T08:30:00.000'),0),
                (14,'Charley',CONVERT(datetime,'1900-01-01T08:45:00.000'),1),
                (15,'Charley',CONVERT(datetime,'1900-01-01T09:00:00.000'),0))V(ID,Employee,Times,Available))
SELECT Employee,
       MIN(Times) AS Min,
       MAX(Times) AS Max
FROM CTE
WHERE Available = 1
GROUP BY Employee,
         Island
ORDER BY Employee ASC,
         Min;