Re: friday afternoon brain fog -> SQL question
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-lReceived on Fri Mar 20 2015 - 21:54:16 CET