By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| @@version |
|---|
| 8.0.27 |
CREATE TABLE shifts (
id int NOT NULL,
unique_id varchar(255) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE appointments (
id int NOT NULL,
unique_id varchar(255) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
shift_id int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (shift_id) REFERENCES shifts(id)
);
INSERT INTO `shifts` (`id`, `unique_id`, `start_date`, `end_date`)
VALUES
('4600', '614fc0c5dab2485bba8cbbd9eb98aa53', '2022-03-15 10:00:00', '2022-03-15 17:00:00')
, ('4601', '614fc0c5dab2485bba8cbbd9eb98aa54', '2022-03-16 09:00:00', '2022-03-16 17:00:00')
, ('4602', '614fc0c5dab2485bba8cbbd9eb98aa55', '2022-03-17 09:00:00', '2022-03-17 17:00:00')
;
INSERT INTO `appointments` (`id`, `unique_id`, `start_date`, `end_date`, `shift_id`)
VALUES
('1', '17875012cad44c3fb890ac12ea9840dc', '2022-03-15 10:30:00', '2022-03-15 11:30:00', 4600)
, ('2', 'e5b7c274f9d444a6b7560c8e3230aab2', '2022-03-15 11:30:00', '2022-03-15 12:15:00', 4600)
, ('3', 'abc3e702f77b423d8bac6250a5be7408', '2022-03-15 15:00:00', '2022-03-15 15:40:00', 4600)
;
CREATE TABLE tasklist (
id int auto_increment primary key
, ts_start timestamp default current_timestamp
, ts_end timestamp default current_timestamp
, pri int
, code VARCHAR(3)
, task VARCHAR(70)
, unique index (ts_start, code)
);
CREATE VIEW v_task AS
WITH edge1 AS (
SELECT t2.task
, t2.code
, t2.pri
, t1.ts_start
, t2.id
, ROW_NUMBER() OVER (PARTITION BY t1.ts_start ORDER BY t2.pri DESC) AS rnk
FROM tasklist AS t1
LEFT JOIN tasklist AS t2
ON t1.ts_start >= t2.ts_start
AND t1.ts_start < t2.ts_end
)
, edge2 AS (
SELECT t2.task
, t2.code
, t2.pri
, t1.ts_end
, t2.id
, ROW_NUMBER() OVER (PARTITION BY t1.ts_end ORDER BY t2.pri DESC) AS rnk
FROM tasklist AS t1
LEFT JOIN tasklist AS t2
ON t1.ts_end > t2.ts_start
AND t1.ts_end < t2.ts_end
AND t1.id <> t2.id
)
-- Insert the test data, both appointments and shift detail:
INSERT IGNORE INTO tasklist (ts_start, ts_end, pri, code, task)
SELECT CAST(start_date AS datetime), CAST(end_date AS datetime), 10, 'SFT', 'Available Shift Time' FROM shifts
UNION
SELECT CAST(start_date AS datetime), CAST(end_date AS datetime), 20, 'APT', 'Appointment' FROM appointments
;
-- Produce reports:
-- The Available Shift Time rows represent remaining open spots for appointments.
-- hours represents the duration of this entry.
-- total represents the overall sum of time for this task for the day
SELECT * FROM v_task ORDER BY ts_start;
SELECT DISTINCT DATE(ts_start) AS date, code, total
FROM v_task
ORDER BY date, code
;
| id | code | task | pri | ts_start | ts_end | hours | total |
|---|---|---|---|---|---|---|---|
| 1 | SFT | Available Shift Time | 10 | 2022-03-15 10:00:00 | 2022-03-15 10:30:00 | 0.50 | 4.58 |
| 4 | APT | Appointment | 20 | 2022-03-15 10:30:00 | 2022-03-15 11:30:00 | 1.00 | 2.42 |
| 5 | APT | Appointment | 20 | 2022-03-15 11:30:00 | 2022-03-15 12:15:00 | 0.75 | 2.42 |
| 1 | SFT | Available Shift Time | 10 | 2022-03-15 12:15:00 | 2022-03-15 15:00:00 | 2.75 | 4.58 |
| 6 | APT | Appointment | 20 | 2022-03-15 15:00:00 | 2022-03-15 15:40:00 | 0.67 | 2.42 |
| 1 | SFT | Available Shift Time | 10 | 2022-03-15 15:40:00 | 2022-03-15 17:00:00 | 1.33 | 4.58 |
| 2 | SFT | Available Shift Time | 10 | 2022-03-16 09:00:00 | 2022-03-16 17:00:00 | 8.00 | 8.00 |
| 3 | SFT | Available Shift Time | 10 | 2022-03-17 09:00:00 | 2022-03-17 17:00:00 | 8.00 | 8.00 |
| date | code | total |
|---|---|---|
| 2022-03-15 | APT | 2.42 |
| 2022-03-15 | SFT | 4.58 |
| 2022-03-16 | SFT | 8.00 |
| 2022-03-17 | SFT | 8.00 |