Re: friday afternoon brain fog -> SQL question

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 20 Mar 2015 23:54:16 +0300
Message-ID: <CAOVevU6LZxcDvQZbpi3=vaqAqFy3s7tEx2R06dGd-G0On3iB9w_at_mail.gmail.com>



This is well-known approach named as "start_of_group" on our russian forum( sql.ru):

select

   id,min(start_date),max(end_date)
from (

      select id,start_date,end_date
            ,count(start_of_group) over(partition by id order by
start_date) grp
      from (
            select id,start_date,end_date
                  ,case when start_date<=max(end_date)over(partition by id
order by start_date rows between unbounded preceding and 1 preceding)
                          then null
                        else 0
                   end start_of_group
            from t
           ) v1
     ) v2

group by id,grp
order by id,grp
/

test query:

with t ( ID , START_DATE       , END_DATE         ) as (
   select 1 , date'2015-01-01' , date'2015-02-01' from dual union all
   select 1 , date'2015-01-05' , date'2015-01-06' from dual union all
   select 1 , date'2015-01-11' , date'2015-01-20' from dual union all
   select 1 , date'2015-01-22' , date'2015-02-25' from dual union all
   select 2 , date'2020-01-01' , date'2020-01-15' from dual union all
   select 2 , date'2020-01-15' , date'2020-02-01' from dual union all
   select 2 , date'2020-03-01' , date'2020-04-01' from dual union all
   select 2 , date'2020-03-05' , date'2020-03-11' from dual
)
select

   id,min(start_date),max(end_date)
from (

      select id,start_date,end_date
            ,count(start_of_group) over(partition by id order by
start_date) grp
      from (
            select id,start_date,end_date
                  ,case when start_date<=max(end_date)over(partition by id
order by start_date rows between unbounded preceding and 1 preceding)
                          then null
                        else 0
                   end start_of_group
            from t
           ) v1
     ) v2

group by id,grp
order by id,grp

On Fri, Mar 20, 2015 at 11:40 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> This is a serious brain teaser, and you send it at 330 central time on a
> Friday. Are you trying to keep us awake thinking about all weekend?
>
> On Fri, Mar 20, 2015 at 3:31 PM, Ryan January <rjanuary_at_gmail.com> wrote:
>
>> I apologize, right after hitting send I noticed your last two sentences.
>>
>> On Mar 20, 2015, at 3:29 PM, Ryan January <rjanuary_at_gmail.com> wrote:
>>
>> I think a little more detail may be required for a full solution.
>>
>> What constitutes an overlap?
>> Do overlapping values share a common ID? If so, wouldn't you be looking
>> for min(start_date) and max(end_date)?
>>
>>
>> On Mar 20, 2015, at 3:24 PM, Stephens, Chris <Chris.Stephens_at_adm.com>
>> wrote:
>>
>> I am trying to coalesce a table with many overlapping intervals of
>> start/end dates for every ID so that any overlapping ranges for any
>> particular ID are merged. I’ve started down the PL/SQL route but I was
>> thinking that 1) there might be a pure SQL solution and 2) someone on this
>> list has already solved or would much more quickly solve this than me.
>>
>> Oracle 11.2.
>>
>> Table T
>> =============
>> ID NUMBER
>> START_DATE DATE
>> END_DATE DATE
>>
>> There are many overlapping ranges for any particular ID and there are
>> many non-overlapping ranges for any particular ID. I want to merge the
>> overlapping ranges.
>>
>> Any help?
>>
>>
>>
>> CONFIDENTIALITY NOTICE:
>> This message is intended for the use of the individual or entity to which
>> it is addressed and may contain information that is privileged,
>> confidential and exempt from disclosure under applicable law. If the reader
>> of this message is not the intended recipient or the employee or agent
>> responsible for delivering this message to the intended recipient, you are
>> hereby notified that any dissemination, distribution or copying of this
>> communication is strictly prohibited. If you have received this
>> communication in error, please notify us immediately by email reply.
>>
>>
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

-- 
Best regards,
Sayan Malakshinov
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 20 2015 - 21:54:16 CET

Original text of this message