11 Replies Latest reply on Jun 8, 2015 8:21 PM by Stew Ashton

    Query Output as Merge Date Ranges

    Zeeshan Shaikh

      Dear All,

       

      I have a table with Data shown below:-

       

      1-Jun-2015 to 10-Jun-2015

      3-Jun-2015 to 5-Jun-2015

      8-Jun-2015 to 10-Jun-2015

      8-Jun-2015 to 15-Jun-2015

      17-Jun-2015 to 25-Jun-2015

      20-Jun-2015 to 26-Jun-2015

      28-Jun-2015 to 29-Jun-2015

       

      I want to write a query with output shown below:-

       

      1-Jun-2015 to 15-Jun-2015

      17-Jun-2015 to 26-Jun-2015

      28-Jun-2015 to 29-Jun-2015

       

      Looking forward for Help on how to write such query

       

      Thanks

       

      Zeeshan Sheikh

        • 2. Re: Query Output as Merge Date Ranges
          Karthick2003

          You have it as single column or two column. What is the data type of the columns? How about providing CREATE TABLE and INSERT script to replicate your data?

          • 3. Re: Query Output as Merge Date Ranges
            Manik

            Jiri.Machotka-Oracle wrote:

             

            You need to make a union of all date intervals.

             

            See e.g. sql - Merge overlapping date intervals - Stack Overflow

            Is that oracle?

             

            Cheers,

            Manik.

            • 4. Re: Query Output as Merge Date Ranges
              Zeeshan Shaikh

              Yes it is Oracle

              • 5. Re: Query Output as Merge Date Ranges
                Zeeshan Shaikh

                I have 2 columns and its a leave table with start_date and end_date.

                • 6. Re: Query Output as Merge Date Ranges
                  Stew Ashton

                  The StackOverflow solutions join the table to itself. Analytics avoid this, so they perform better.

                   

                  Since you didn't provide test data, here is test data from my blog post on this subject (https://stewashton.wordpress.com/2014/03/16/merging-contiguous-date-ranges/)

                   

                  create table t ( id int, start_date date, end_date date );
                  Insert into T values (1, DATE '2014-01-01', DATE '2014-01-03');
                  Insert into T values (2, DATE '2014-01-03', DATE '2014-01-05');
                  Insert into T values (3, DATE '2014-01-05', DATE '2014-01-07');
                  Insert into T values (4, DATE '2014-01-08', DATE '2014-02-01');
                  Insert into T values (5, DATE '2014-02-01', DATE '2014-02-10');
                  Insert into T values (6, DATE '2014-02-05', DATE '2014-02-28');
                  Insert into T values (7, DATE '2014-02-10', DATE '2014-02-15');
                  
                  with grp_starts as (
                    select start_date, end_date,
                    case
                      when start_date <= lag(end_date) over(order by start_date, end_date)
                      then 0 else 1
                    end grp_start
                    from t
                  )
                  , grps as (
                    select start_date, end_date,
                    sum(grp_start) over(order by start_date, end_date) grp
                    from grp_starts
                  )
                  select min(start_date) start_date,
                  max(end_date) end_date
                  from grps
                  group by grp
                  order by 1, 2;
                  
                  -- STARTING WITH VERSION 12c
                  select * from t
                  match_recognize(
                    order by start_date, end_date
                    measures first(start_date) start_date, max(end_date) end_date
                    pattern(A B*)
                    define B as start_date <= prev(end_date)
                  );
                  
                  
                  

                   

                  In both cases the output is:

                   

                  START_DATEEND_DATE
                  2014/01/01 00:00:002014/01/07 00:00:00
                  2014/01/08 00:00:002014/02/28 00:00:00

                   

                  Note: you use inclusive end dates. I use exclusive end dates because they work for dates and date/time data. If you insist on using inclusive end dates, just adjust the comparisons.

                   

                  I'll provide a specific solution if you provide CREATE TABLE and INSERT statements.

                  • 7. Re: Re: Query Output as Merge Date Ranges
                    Karthick2003

                    > Note: you use inclusive end dates. I use exclusive end dates because they work for dates and date/time data. If you insist on using inclusive end dates, just adjust the comparisons.

                     

                    Am I missing something

                     

                    with t
                    as
                    (
                    select to_date('01-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('03-Jun-2015', 'dd-mon-yyyy') start_date, to_date('05-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('06-Jun-2015', 'dd-mon-yyyy') start_date, to_date('07-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('15-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('17-Jun-2015', 'dd-mon-yyyy') start_date, to_date('19-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('21-Jun-2015', 'dd-mon-yyyy') start_date, to_date('25-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('20-Jun-2015', 'dd-mon-yyyy') start_date, to_date('26-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                    select to_date('28-Jun-2015', 'dd-mon-yyyy') start_date, to_date('29-Jun-2015', 'dd-mon-yyyy') end_date from dual
                    )
                    

                     

                    How would you modify your query to achieve the output on this data set without self-join.

                    • 8. Re: Query Output as Merge Date Ranges
                      Zeeshan Shaikh

                      Thank you it worked

                      • 9. Re: Re: Query Output as Merge Date Ranges
                        Karthick2003

                        Are you sure?

                         

                        SQL> with t
                          2  as
                          3  (
                          4  select to_date('01-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          5  select to_date('03-Jun-2015', 'dd-mon-yyyy') start_date, to_date('05-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          6  select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          7  select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('15-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          8  select to_date('17-Jun-2015', 'dd-mon-yyyy') start_date, to_date('25-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          9  select to_date('20-Jun-2015', 'dd-mon-yyyy') start_date, to_date('26-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                         10  select to_date('28-Jun-2015', 'dd-mon-yyyy') start_date, to_date('29-Jun-2015', 'dd-mon-yyyy') end_date from dual
                         11  )
                         12  ,grp_starts as (
                         13    select start_date, end_date,
                         14    case
                         15      when start_date <= lag(end_date) over(order by start_date, end_date)
                         16      then 0 else 1
                         17    end grp_start
                         18    from t
                         19  )
                         20  , grps as (
                         21    select start_date, end_date,
                         22    sum(grp_start) over(order by start_date, end_date) grp
                         23    from grp_starts
                         24  )
                         25  select min(start_date) start_date,
                         26  max(end_date) end_date
                         27  from grps
                         28  group by grp
                         29  order by 1, 2;
                        
                        START_DAT END_DATE
                        --------- ---------
                        01-JUN-15 10-JUN-15
                        08-JUN-15 15-JUN-15
                        17-JUN-15 26-JUN-15
                        28-JUN-15 29-JUN-15
                        
                        • 10. Re: Re: Re: Query Output as Merge Date Ranges
                          Stew Ashton

                          I adjusted your input to correspond to the OP's data, thanks for going to that trouble.

                           

                          In fact, my original solution only accounted for "partial overlaps", not for date ranges that were included in other date ranges. Here is a corrected solution.

                           

                          with t
                          as
                          (
                          select to_date('01-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('03-Jun-2015', 'dd-mon-yyyy') start_date, to_date('05-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('10-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('08-Jun-2015', 'dd-mon-yyyy') start_date, to_date('15-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('17-Jun-2015', 'dd-mon-yyyy') start_date, to_date('25-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('20-Jun-2015', 'dd-mon-yyyy') start_date, to_date('26-Jun-2015', 'dd-mon-yyyy') end_date from dual union all
                          select to_date('28-Jun-2015', 'dd-mon-yyyy') start_date, to_date('29-Jun-2015', 'dd-mon-yyyy') end_date from dual
                          )
                          , grp_starts as (
                            select start_date, end_date,
                            case
                              when start_date <= max(end_date) over(
                                order by start_date, end_date rows between unbounded preceding and 1 preceding
                              )
                              then 0 else 1
                            end grp_start
                            from t
                          )
                          , grps as (
                            select start_date, end_date,
                            sum(grp_start) over(order by start_date, end_date) grp
                            from grp_starts
                          )
                          select min(start_date) start_date,
                          max(end_date) end_date
                          from grps
                          group by grp
                          order by 1, 2;
                          
                          

                           

                          START_DATEEND_DATE
                          2015/06/01 00:00:002015/06/15 00:00:00
                          2015/06/17 00:00:002015/06/26 00:00:00
                          2015/06/28 00:00:002015/06/29 00:00:00
                          • 11. Re: Query Output as Merge Date Ranges
                            Stew Ashton

                            In case anyone is interested, I wrote a blog post explaining my solution in a bit more detail:

                             

                            https://stewashton.wordpress.com/2015/06/08/merging-overlapping-date-ranges/