RE: friday afternoon brain fog -> SQL question

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Fri, 20 Mar 2015 14:06:20 -0700
Message-ID: <BLU179-W92D6E3F01A34130760F8EEEB0E0_at_phx.gbl>

Very nice.
[oracle_at_localhost ~]$ sqlplus hr/oracle SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 20 17:05:10 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR:ORA-28002: the password will expire within 2 days

Last Successful login time: Wed Mar 18 2015 12:12:04 -04:00 Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1_at_ORCL> with t ( ID , START_DATE       , END_DATE         ) as (   select 1 , date'2015-01-01' , date'2015-02-02' from dual union all   select 1 , date'2015-02-01' , date'2015-03-02' from dual union all   select 1 , date'2015-03-01' , date'2015-04-01' from dual union all   select 1 , date'2015-04-02' , date'2015-05-01' from dual union all   select 1 , date'2015-05-02' , date'2015-06-01' from dual union all   select 1 , date'2015-06-02' , date'2015-07-01' 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     ) v2group by id,grporder by id,grp  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24  ;
	ID MIN(START MAX(END_D---------- --------- ---------	 1 01-JAN-15 01-APR-15	 1 02-APR-15 01-MAY-15	 1 02-MAY-15 01-JUN-15	 1 02-JUN-15 01-JUL-15

PDB1_at_ORCL> exitDisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Date: Fri, 20 Mar 2015 23:54:16 +0300
Subject: Re: friday afternoon brain fog -> SQL question From: xt.and.r_at_gmail.com
To: andrew.kerber_at_gmail.com
CC: rjanuary_at_gmail.com; Chris.Stephens_at_adm.com; oracle-l_at_freelists.org

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     ) v2group by id,grporder 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     ) v2group by id,grporder 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 NUMBERSTART_DATE DATEEND_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 - 22:06:20 CET