By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
CREATE TABLE intervals (
num int,
valid_from datetime,
valid_until datetime
);
INSERT INTO intervals
(num, valid_from, valid_until)
VALUES
(1, NOW(), ADDDATE(NOW(), 7)),
(2, ADDDATE(NOW(), 1), ADDDATE(NOW(), 8)),
(3, ADDDATE(NOW(), 2), ADDDATE(NOW(), 9)),
(4, ADDDATE(NOW(), 3), ADDDATE(NOW(), 10))
;
select * from intervals;
Records: 4 Duplicates: 0 Warnings: 0
| num | valid_from | valid_until |
|---|---|---|
| 1 | 2024-07-20 06:50:08 | 2024-07-27 06:50:08 |
| 2 | 2024-07-21 06:50:08 | 2024-07-28 06:50:08 |
| 3 | 2024-07-22 06:50:08 | 2024-07-29 06:50:08 |
| 4 | 2024-07-23 06:50:08 | 2024-07-30 06:50:08 |
with t as(
select *
,max(qty)over(order by dt rows between current row and unbounded following)maxnext
from(
select num,dt,running,sum(running)over(order by dt)qty
-- ,row_number()over(order by dt desc) rn
from(
select num,valid_from dt, 1 running
from intervals
union all
select num,valid_until, -1 running
from intervals
)allevents
)running_qty
)
select *
from t
where qty<3 and maxnext<3
order by dt
limit 1
| num | dt | running | qty | maxnext |
|---|---|---|---|---|
| 2 | 2024-07-28 06:50:08 | -1 | 2 | 2 |