select concat('insert into rental(rental_id, rental_date, return_date, customer_id) values(',rental_id
,',to_date(''',rental_date,''',''yyyy-mm-dd hh24:mi:ss'')'
,',to_date(''',return_date,''',''yyyy-mm-dd hh24:mi:ss'')'
,',',customer_id
,')') s
, rental_id, rental_date, return_date, customer_id
from rental where customer_id = 1
;
select rental_date, rental_date, return_date, rental_id
, 1, cast(rental_id as CHAR(30000))
from (
select case
when rental_date > max(return_date) over(order by rental_date rows between unbounded preceding and 1 preceding)
then 1 end x
, rental_date, return_date, rental_id
from rental where customer_id = 1
) a
where x=1
;
with RECURSIVE cte(from_date, rental_date, return_date, rental_id, l, path) as (
select rental_date, rental_date, return_date, rental_id
, 1, cast(rental_id as CHAR(30000))
from rental where customer_id = 1
union all
select cte.from_date, r.rental_date, greatest(r.return_date, cte.return_date), r.rental_id, l+1
, concat(path,'-',r.rental_id)
from cte
, lateral (select * from rental r
where r.customer_id = 1
and r.rental_id <> cte.rental_id
and r.rental_date >= cte.rental_date
and r.rental_date <= cte.return_date
order by rental_date limit 1
) r
)
select min(from_date) from_date, to_date
from(
select from_date, max(return_date) to_date
from cte
group by from_date
)t group by to_date
Telegram: DB developers channel