Создать диапазон из дат

Рейтинг: 0Ответов: 1Опубликовано: 12.01.2023

Помогите пожалуйста собрать одинокие даты в отдельные диапазоны по условию,что даты должны идти друг за другом,если не идут,то начинается другой диапазон. Пример дат:

with cte as (
select to_date ('01.01.2023') as dt
from dual
union all
select to_date ('02.01.2023') as dt
from dual
union all
select to_date ('03.01.2023') as dt
from dual
union all
select to_date ('04.01.2023') as dt
from dual
union all
select to_date ('10.01.2023') as dt
from dual
union all
select to_date ('11.01.2023') as dt
from dual
)

На выходе должно получиться два диапазона: 01.01.2023 - 04.01.2023 и 10.01.2023 - 11.01.2023.

plsql
Источник: Stack Overflow на русском

Ответы

▲ 1Принят

@Akina ,без проблем и "траха" перевел на Oracle

WITH 
cte1 AS (  
  SELECT to_date ('2023-01-01', 'YYYY-MM-DD') as  dt from dual 
  UNION ALL
  SELECT to_date ('2023-01-02' , 'YYYY-MM-DD') as  dt from dual 
  UNION ALL
  SELECT to_date ('2023-01-03' , 'YYYY-MM-DD') as  dt from dual 
  UNION ALL
  SELECT to_date ('2023-01-04' , 'YYYY-MM-DD') as  dt from dual 
  UNION ALL
  SELECT to_date ('2023-01-10' , 'YYYY-MM-DD') as  dt from dual 
  UNION ALL
  SELECT to_date ('2023-01-11' , 'YYYY-MM-DD') as  dt from dual 
),
cte2 AS (
  SELECT dt, CASE WHEN dt- LAG(dt) OVER (ORDER BY dt) = 1
                  THEN 0
                  ELSE 1
                  END AS adjacent
  FROM cte1
),
cte3 AS (
  SELECT dt, SUM(adjacent) OVER (ORDER BY dt) group_number
  FROM cte2
)
SELECT MIN(dt) dt_start,
       MAX(dt) dt_end
FROM cte3
GROUP BY group_number;