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