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