By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| @@version |
|---|
| 10.4.20-MariaDB |
CREATE TABLE `unit` (
`id` int(11) NOT NULL,
`parent_unit_id` int(11) DEFAULT NULL,
`name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `unit`
ADD PRIMARY KEY (`id`),
ADD KEY `IDX_C3A3B3698AF5044B` (`parent_unit_id`);
ALTER TABLE `unit`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `unit`
ADD CONSTRAINT `FK_C3A3B3698AF5044B` FOREIGN KEY (`parent_unit_id`) REFERENCES `unit` (`id`);
INSERT INTO `unit` (parent_unit_id, name) VALUES
(NULL, "Car"),
(1, "Tire 1"),
(1, "Tire 2");
CREATE TABLE `unit_calendar_entry` (
`id` int(11) NOT NULL,
`unit_id` int(11) NOT NULL,
`entry_start` datetime NOT NULL,
`entry_end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `unit_calendar_entry`
ADD PRIMARY KEY (`id`),
ADD KEY `IDX_7260175CA40A2C8` (`unit_id`);
ALTER TABLE `unit_calendar_entry`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `unit_calendar_entry`
SELECT * FROM unit;
| id | parent_unit_id | name |
|---|---|---|
| 1 | null | Car |
| 2 | 1 | Tire 1 |
| 3 | 1 | Tire 2 |
SELECT * FROM unit_calendar_entry;
| id | unit_id | entry_start | entry_end |
|---|---|---|---|
| 1 | 1 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 |
| 2 | 2 | 2021-08-03 00:00:00 | 2021-08-07 00:00:00 |
| 3 | 3 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 |
| 4 | 3 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 |
-- all times from table "unit_calendar_entry"
select unit_id, entry_start, entry_end
from unit_calendar_entry
union -- plus those for the tires when the car is booked
select p.id, u.entry_start, u.entry_end
from unit p
join unit q
on q.id = p.parent_unit_id
join unit_calendar_entry u
on q.id = u.unit_id
union -- plus those for the car when one of the tires is booked
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
order by unit_id, entry_start;
| unit_id | entry_start | entry_end |
|---|---|---|
| 1 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 |
| 1 | 2021-08-03 00:00:00 | 2021-08-07 00:00:00 |
| 1 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 |
| 1 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 |
| 2 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 |
| 2 | 2021-08-03 00:00:00 | 2021-08-07 00:00:00 |
| 3 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 |
| 3 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 |
| 3 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 |
with subtab as (
select unit_id, entry_start, entry_end
from unit_calendar_entry
union
select p.id, u.entry_start, u.entry_end
from unit p
join unit q
on q.id = p.parent_unit_id
join unit_calendar_entry u
on q.id = u.unit_id
union
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
order by unit_id, entry_start
)
select c.*, sum(case when prev_end < entry_start then 1 else 0 end) over (order by unit_id, entry_start) as grouping
from (
select subtab.*, max(entry_end) over (partition by unit_id order by entry_start rows between unbounded preceding and 1 preceding) as prev_end from subtab
) c;
| unit_id | entry_start | entry_end | prev_end | grouping |
|---|---|---|---|---|
| 1 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 | null | 0 |
| 1 | 2021-08-03 00:00:00 | 2021-08-07 00:00:00 | 2021-08-03 00:00:00 | 0 |
| 1 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 | 2021-08-07 00:00:00 | 0 |
| 1 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 | 2021-08-07 00:00:00 | 1 |
| 2 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 | null | 1 |
| 2 | 2021-08-03 00:00:00 | 2021-08-07 00:00:00 | 2021-08-03 00:00:00 | 1 |
| 3 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 | null | 1 |
| 3 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 | 2021-08-03 00:00:00 | 2 |
| 3 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 | 2021-08-06 00:00:00 | 3 |
-- getting all continuous timespans where units are booked (but we actually want the opposite)
select unit_id, grouping, min(entry_start), max(entry_end)
from (
select c.*, sum(case when prev_end < entry_start then 1 else 0 end) over (order by unit_id, entry_start) as grouping
from (
select d.*, max(entry_end) over (partition by unit_id order by entry_start rows between unbounded preceding and 1 preceding) as prev_end
from (
select unit_id, entry_start, entry_end
from unit_calendar_entry
union
select p.id, u.entry_start, u.entry_end
from unit p
join unit q
on q.id = p.parent_unit_id
join unit_calendar_entry u
on q.id = u.unit_id
union
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
) d
) c
) b
group by unit_id, grouping;
| unit_id | grouping | min(entry_start) | max(entry_end) |
|---|---|---|---|
| 1 | 0 | 2021-07-31 00:00:00 | 2021-08-07 00:00:00 |
| 1 | 1 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 |
| 2 | 1 | 2021-07-31 00:00:00 | 2021-08-07 00:00:00 |
| 3 | 1 | 2021-07-31 00:00:00 | 2021-08-03 00:00:00 |
| 3 | 2 | 2021-08-04 00:00:00 | 2021-08-06 00:00:00 |
| 3 | 3 | 2021-08-09 00:00:00 | 2021-08-10 00:00:00 |
-- getting all continuous timespans where units are available
with tab as (
select c.*, sum(case when prev_end < entry_start then 1 else 0 end) over (order by unit_id, entry_start) as grouping
from (
select d.*, max(entry_end) over (partition by unit_id order by entry_start rows between unbounded preceding and 1 preceding) as prev_end
from (
select unit_id, entry_start, entry_end
from unit_calendar_entry
union
select p.id, u.entry_start, u.entry_end
from unit p
join unit q
on q.id = p.parent_unit_id
join unit_calendar_entry u
on q.id = u.unit_id
union
select p.parent_unit_id as unit_id, u.entry_start, u.entry_end
from unit p
join unit_calendar_entry u
on p.id = u.unit_id
and p.parent_unit_id is not null
) d
) c
)
select distinct unit_id, NULLIF((min(ifnull(prev_end,'1000-01-01')) over (partition by unit_id, grouping)),'1000-01-01') as available_from, min(entry_start) over (partition by unit_id, grouping) as available_til
from tab
union
select distinct unit_id, max(entry_end) over (partition by unit_id) as available_from, null as available_til
from tab
order by unit_id, available_from
| unit_id | available_from | available_til |
|---|---|---|
| 1 | null | 2021-07-31 00:00:00 |
| 1 | 2021-08-07 00:00:00 | 2021-08-09 00:00:00 |
| 1 | 2021-08-10 00:00:00 | null |
| 2 | null | 2021-07-31 00:00:00 |
| 2 | 2021-08-07 00:00:00 | null |
| 3 | null | 2021-07-31 00:00:00 |
| 3 | 2021-08-03 00:00:00 | 2021-08-04 00:00:00 |
| 3 | 2021-08-06 00:00:00 | 2021-08-09 00:00:00 |
| 3 | 2021-08-10 00:00:00 | null |