Selecting time windows based on availability in SQL

Question

I have a data set with several time windows, an availability indicator and a priority index.

Create data set:

CREATE TABLE TimeWindows(
TimeFrom DATETIME NOT NULL,
TimeTo DATETIME NOT NULL,
Priority INT NOT NULL,
Available BIT NOT NULL
);

INSERT INTO TimeWindows(TimeFrom, TimeTo, Priority, Available) VALUES
('2017-07-22 07:00:00', '2017-07-22 12:00:00', 1, 1),
('2017-07-22 13:00:00', '2017-07-22 17:00:00', 1, 1),
('2017-07-22 12:30:00', '2017-07-23 00:00:00', 3, 0),
('2017-07-23 07:00:00', '2017-07-23 12:00:00', 1, 1),
('2017-07-23 13:00:00', '2017-07-23 17:00:00', 1, 1),
('2017-07-23 00:00:00', '2017-07-24 00:00:00', 2, 0),
('2017-07-23 19:00:00', '2017-07-23 20:00:00', 4, 1),
('2017-07-24 07:00:00', '2017-07-24 12:00:00', 1, 1),
('2017-07-24 13:00:00', '2017-07-24 17:00:00', 1, 1),
('2017-07-24 15:00:00', '2017-07-24 16:00:00', 4, 0);

Example data set:

| TimeFrom            | TimeTo              | Priority | Available |
|---------------------|---------------------|----------|-----------|
| 2017-07-22 07:00:00 | 2017-07-22 12:00:00 |        1 |         1 |
| 2017-07-22 13:00:00 | 2017-07-22 17:00:00 |        1 |         1 |
| 2017-07-22 12:30:00 | 2017-07-23 00:00:00 |        3 |         0 |
| 2017-07-23 07:00:00 | 2017-07-23 12:00:00 |        1 |         1 |
| 2017-07-23 13:00:00 | 2017-07-23 17:00:00 |        1 |         1 |
| 2017-07-23 00:00:00 | 2017-07-24 00:00:00 |        2 |         0 |
| 2017-07-23 19:00:00 | 2017-07-24 20:00:00 |        4 |         1 |
| 2017-07-24 07:00:00 | 2017-07-24 12:00:00 |        1 |         1 |
| 2017-07-24 13:00:00 | 2017-07-24 17:00:00 |        1 |         1 |
| 2017-07-24 15:00:00 | 2017-07-24 16:00:00 |        4 |         0 |

Problem:

I want to generate a new set of time windows, which only represent the available time slots.

Business rules:

Time windows with a higher priority, will overrule conflicting time windows with a lower priority
Availability indicator is set to 1 if available and is set to 0 if not available
Overlapping time windows need to be consolidated
An available time window which is 'split' by a period of unavailability, will be converted into two separate available time windows

Desired results:

| TimeFrom            | TimeTo              |
|---------------------|---------------------|
| 2017-07-22 07:00:00 | 2017-07-22 12:00:00 |
| 2017-07-23 19:00:00 | 2017-07-23 20:00:00 |
| 2017-07-24 07:00:00 | 2017-07-24 12:00:00 |
| 2017-07-24 13:00:00 | 2017-07-24 15:00:00 |
| 2017-07-24 16:00:00 | 2017-07-24 17:00:00 |

Can anyone advise me on how to solve this problem in SQL?

Thanks in advance for your help.

I think it will be much easier to do with a programming language then with sql. — Zohar Peled, Jul 23 '17 at 10:14
Why time slot `2017-07-22 13:00:00 | 2017-07-22 17:00:00` is not in the desired result? — Evaldas Buinauskas, Jul 23 '17 at 11:11
@EvaldasBuinauskas I think it's because of this row: `2017-07-22 12:30:00 | 2017-07-23 00:00:00 | 3 | 0` it's higher in priority, unavailable and covers it completely. — Zohar Peled, Jul 23 '17 at 11:15
@EvaldasBuinauskas Because it is overruled by 2017-07-22 12:30:00 | 2017-07-23 00:00:00 which has a higher priority and is set to unavailable — David Burgers, Jul 23 '17 at 11:16
@ZoharPeled That might be it, other than that. Isn't this called gaps and islands problem? There are bunch of solutions in SQL to solve this, you'll have to adapt it to your case. But yet again, this is better done in a programming language than in SQL. — Evaldas Buinauskas, Jul 23 '17 at 11:16
It's not a classic gaps and islands, but it might be solvable using the same techniques. I'm not sure. — Zohar Peled, Jul 23 '17 at 11:26

Zohar Peled · Accepted Answer · 2017-07-23T11:30:30.153

Well, It's been a while since I've solved a good sql challenge, so I got the hard part for you done. Using a CTE with some case statements and another query with some more case statements to get all the bussiness rules but one -

Overlapping time windows need to be consolidated.

but that's really the easy part. You'll see a lot of comments in the code that explains the logic, and of course, I could only test myself against your sample data, but it should get you started.

There might be other, better solutions for this, but this is what I came up with:

;WITH CTE AS
(

SELECT  t0.TimeFrom As AvailableFrom,
        t0.TimeTo As AvailableTo,
        t1.TimeFrom As UnavailableFrom,
        t1.TimeTo As UnavailableTo,
        CASE WHEN t1.Available IS NULL THEN 
            -- no overlapping records with higher priority and available = 0, use t0 start and end.
            1 -- t0.TimeFrom, t0.TimeTo
        ELSE
            CASE 
                WHEN t0.TimeFrom > t1.TimeFrom AND t0.TimeTo < t1.TimeTo THEN
                -- t0 is inside t1, record of t0 can't be used.
                0
                WHEN t0.TimeFrom < t1.TimeFrom AND t0.TimeTo < t1.TimeTo THEN
                -- t0 starts before t1 starts, and also ends before t1 ends. this means that the start will be t0 start.
                2 -- t0.TimeFrom, t1.TimeFrom
                WHEN t0.TimeFrom > t1.TimeFrom AND t0.TimeTo > t1.TimeTo THEN
                -- t0 starts after t1, but also ends after it. this means that the start will be t1 end.
                3 -- t1.TimeTo, t0.TimeTo
                WHEN t0.TimeFrom < t1.TimeFrom AND t0.TimeTo > t1.TimeTo THEN
                -- t1 is inside t0, need to create 2 records for this.
                4 -- 2 records - t0.TimeFrom, t1.TimeFrom and also t1.TimeTo and t0.TimeTo
            END
        END As RecordType
FROM TimeWindows t0
LEFT JOIN TimeWindows t1 ON t0.TimeFrom <= t1.TimeTo -- t1 overlaps t0
                        AND t0.TimeTo >= t1.TimeFrom -- t1 overlaps t0
                        AND t0.Priority < t1.Priority -- t1 priority is higher than t0 priority
                        AND t1.Available = 0 -- t1 records are unavaialbe
WHERE t0.Available = 1 -- t0 records are available
-- t0 holds all the available time slots, 
-- while t1 holds all the unavailable time slots that overlap t0 records and have a higher priority. (otherwise they don't matter...)
)

SELECT  CASE RecordType
            WHEN 1 THEN
                AvailableFrom
            WHEN 2 THEN
                AvailableFrom
            WHEN 3 THEN
                UnavailableTo
            WHEN 4 THEN
            AvailableFrom
        END As TimeFrom,
        CASE RecordType
            WHEN 1 THEN
                AvailableTo
            WHEN 2 THEN
                UnavailableFrom
            WHEN 3 THEN
                AvailableTo
            WHEN 4 THEN
            UnavailableFrom
        END As TimeTo
FROM CTE 
WHERE RecordType > 0
UNION ALL
SELECT  UnavailableTo,
        AvailableTo
FROM CTE 
WHERE RecordType =4

Results:

TimeFrom                TimeTo
22.07.2017 07:00:00     22.07.2017 12:00:00
23.07.2017 19:00:00     23.07.2017 20:00:00
24.07.2017 07:00:00     24.07.2017 12:00:00
24.07.2017 13:00:00     24.07.2017 15:00:00
24.07.2017 16:00:00     24.07.2017 17:00:00

You can see a live demo on rextester.

Selecting time windows based on availability in SQL

1 Answers1