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
row (1, '2024-07-23 09:21:17', ADDDATE('2024-07-23 09:21:17', 7)),
row (2, ADDDATE('2024-07-23 09:21:17', 1), ADDDATE('2024-07-23 09:21:17', 8)), -- should be this valid_until date as starting
row (3, ADDDATE('2024-07-23 09:21:17', 2), ADDDATE('2024-07-23 09:21:17', 9)),
row (4, ADDDATE('2024-07-23 09:21:17', 3), ADDDATE('2024-07-23 09:21:17', 10)),
row (5, ADDDATE('2024-07-23 09:21:17', 20), ADDDATE('2024-07-23 09:21:17', 27)),
row (6, ADDDATE('2024-07-23 09:21:17', 20), ADDDATE('2024-07-23 09:21:17', 27)),
row (7, ADDDATE('2024-07-23 09:21:17', 20), ADDDATE('2024-07-23 09:21:17', 27))
;
select * from intervals;
Records: 7 Duplicates: 0 Warnings: 0
| num | valid_from | valid_until |
|---|---|---|
| 1 | 2024-07-23 09:21:17 | 2024-07-30 09:21:17 |
| 2 | 2024-07-24 09:21:17 | 2024-07-31 09:21:17 |
| 3 | 2024-07-25 09:21:17 | 2024-08-01 09:21:17 |
| 4 | 2024-07-26 09:21:17 | 2024-08-02 09:21:17 |
| 5 | 2024-08-12 09:21:17 | 2024-08-19 09:21:17 |
| 6 | 2024-08-12 09:21:17 | 2024-08-19 09:21:17 |
| 7 | 2024-08-12 09:21:17 | 2024-08-19 09:21:17 |
with allevents as(
select num,dt,running,sum(running)over(order by dt)qty
,row_number()over(order by dt) rn
from(
select num,valid_from dt, 1 running
from intervals
union all
select num,valid_until, -1 running
from intervals
)x
)
,t as(
select *
,sum(newgr)over(order by dt)grn
from(
select num,dt,running,qty,rn
,case when qty>=3 and lag(qty,1,0)over(order by dt)<=2
or qty<=2 and lag(qty,1,0)over(order by dt)>=3
then 1 else 0 end newgr
from allevents
)running_qty
)
select *
from t
-- where qty<3 and maxnext<3
order by dt
-- limit 1
| num | dt | running | qty | rn | newgr | grn |
|---|---|---|---|---|---|---|
| 1 | 2024-07-23 09:21:17 | 1 | 1 | 1 | 0 | 0 |
| 2 | 2024-07-24 09:21:17 | 1 | 2 | 2 | 0 | 0 |
| 3 | 2024-07-25 09:21:17 | 1 | 3 | 3 | 1 | 1 |
| 4 | 2024-07-26 09:21:17 | 1 | 4 | 4 | 0 | 1 |
| 1 | 2024-07-30 09:21:17 | -1 | 3 | 5 | 0 | 1 |
| 2 | 2024-07-31 09:21:17 | -1 | 2 | 6 | 1 | 2 |
| 3 | 2024-08-01 09:21:17 | -1 | 1 | 7 | 0 | 2 |
| 4 | 2024-08-02 09:21:17 | -1 | 0 | 8 | 0 | 2 |
| 5 | 2024-08-12 09:21:17 | 1 | 3 | 9 | 1 | 3 |
| 6 | 2024-08-12 09:21:17 | 1 | 3 | 10 | 0 | 3 |
| 7 | 2024-08-12 09:21:17 | 1 | 3 | 11 | 0 | 3 |
| 5 | 2024-08-19 09:21:17 | -1 | 0 | 12 | 1 | 4 |
| 6 | 2024-08-19 09:21:17 | -1 | 0 | 13 | 0 | 4 |
| 7 | 2024-08-19 09:21:17 | -1 | 0 | 14 | 0 | 4 |
with allevents as(
select num,dt,running,sum(running)over(order by dt)qty
,row_number()over(order by dt) rn
from(
select num,valid_from dt, 1 running
from intervals
union all
select num,valid_until, -1 running
from intervals
)x
)
,t as(
select *
,sum(newgr)over(order by dt)grn
from(
select num,dt,running,qty,rn
,case when qty>=3 and lag(qty,1,0)over(order by dt)<=2
or qty<=2 and lag(qty,1,0)over(order by dt)>=3
then 1 else 0 end newgr
from allevents
)running_qty
)
,grint as(
select grn,min(rn)n1,max(rn)n2
,min(dt)fromdt,max(dt) todt
,min(qty)qtymin,max(qty) qtymax
,lead(min(dt))over(order by grn) nextgrdt
,case when adddate(min(dt),7)<lead(min(dt))over(order by grn)
or lead(min(dt))over(order by grn) is null
then 1 else 0 end slot
-- ,timediff(nextEvent,dt) td
from t
group by grn
)
-- select * from grint order by grn;
| fromdt | nextgrdt |
|---|---|
| 2024-07-31 09:21:17 | 2024-08-12 09:21:17 |
| 2024-08-19 09:21:17 | null |