Get day ranges from date column in SQL-CodePudding

I have a table with a date column (date_for), and I'd like to get the consecutive day ranges of this column.

select
  date_for
from (select to_date('20210910','yyyymmdd') date_for from dual union all
      select to_date('20210911','yyyymmdd') date_for from dual union all
      select to_date('20210912','yyyymmdd') date_for from dual union all
      select to_date('20210913','yyyymmdd') date_for from dual union all
      select to_date('20210915','yyyymmdd') date_for from dual union all
      select to_date('20210916','yyyymmdd') date_for from dual union all
      select to_date('20210917','yyyymmdd') date_for from dual union all
      select to_date('20211011','yyyymmdd') date_for from dual union all
      select to_date('20211012','yyyymmdd') date_for from dual union all
      select to_date('20211108','yyyymmdd') date_for from dual
      )

The desired output is something like:

2021.09.10-2021.09.13
2021.09.15-2021.09.17
2021.10.11-2021.10.12
2021.11.08-2021.11.08

How can I query that?

CodePudding user response：

You'll have to isolate islands. For example:

SQL> WITH
  2     test
  3     AS
  4        (SELECT TO_DATE ('20210910', 'yyyymmdd') date_for FROM DUAL
  5         UNION ALL
  6         SELECT TO_DATE ('20210911', 'yyyymmdd') date_for FROM DUAL
  7         UNION ALL
  8         SELECT TO_DATE ('20210912', 'yyyymmdd') date_for FROM DUAL
  9         UNION ALL
 10         SELECT TO_DATE ('20210913', 'yyyymmdd') date_for FROM DUAL
 11         UNION ALL
 12         SELECT TO_DATE ('20210915', 'yyyymmdd') date_for FROM DUAL
 13         UNION ALL
 14         SELECT TO_DATE ('20210916', 'yyyymmdd') date_for FROM DUAL
 15         UNION ALL
 16         SELECT TO_DATE ('20210917', 'yyyymmdd') date_for FROM DUAL
 17         UNION ALL
 18         SELECT TO_DATE ('20211011', 'yyyymmdd') date_for FROM DUAL
 19         UNION ALL
 20         SELECT TO_DATE ('20211012', 'yyyymmdd') date_for FROM DUAL
 21         UNION ALL
 22         SELECT TO_DATE ('20211108', 'yyyymmdd') date_for FROM DUAL)
 23    SELECT MIN (date_for) fie, MAX (date_for) lie
 24      FROM (SELECT date_for,
 25                     TO_NUMBER (TO_CHAR (date_for, 'yyyymmdd'))
 26                   - ROW_NUMBER () OVER (ORDER BY date_for) diff
 27              FROM test)
 28  GROUP BY diff
 29  ORDER BY 1;

FIE        LIE
---------- ----------
2021.09.10 2021.09.13
2021.09.15 2021.09.17
2021.10.11 2021.10.12
2021.11.08 2021.11.08

SQL>

See some more options on Finding islands on OraFAQ.

CodePudding user response：

The simplest solution is this:

with cte as (
    select date_for
         , date_for - row_number() over (order by date_for) as grp_date
    from t
)
select min(date_for) as from_date
     , max(date_for) as to_date
from cte
group by grp_date
order by from_date

DB<>Fiddle

CodePudding user response：

An even simpler solution:

select
   min(date_for) st,
   max(date_for) en
from
(select
   date_for,
   date_for-rownum x
 from (select to_date('20210910','yyyymmdd') date_for from dual union all
       select to_date('20210911','yyyymmdd') date_for from dual union all
       select to_date('20210912','yyyymmdd') date_for from dual union all
       select to_date('20210913','yyyymmdd') date_for from dual union all
       select to_date('20210915','yyyymmdd') date_for from dual union all
       select to_date('20210916','yyyymmdd') date_for from dual union all
       select to_date('20210917','yyyymmdd') date_for from dual union all
       select to_date('20211011','yyyymmdd') date_for from dual union all
       select to_date('20211012','yyyymmdd') date_for from dual union all
       select to_date('20211108','yyyymmdd') date_for from dual
       )
 )
group by x
order by 1