RE: friday afternoon brain fog -> SQL question
Date: Fri, 20 Mar 2015 21:13:13 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC8BEA92D9_at_lopez.pti-nps.com>
Here's something similar that I had to do. I was looking for contiguous date ranges, but it'll get you in the neighborhood.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3193440300346307017
hth,
T. J.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iggy Fernandez
Sent: Friday, March 20, 2015 4:06 PM
To: xt.and.r_at_gmail.com; Andrew Kerber
Cc: rjanuary_at_gmail.com; Chris Stephens; oracle-l_at_freelists.org
Subject: RE: friday afternoon brain fog -> SQL question
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 Production
With 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
) v2
group by id,grp
order 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> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With 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<mailto:xt.and.r_at_gmail.com> To: andrew.kerber_at_gmail.com<mailto:andrew.kerber_at_gmail.com> CC: rjanuary_at_gmail.com<mailto:rjanuary_at_gmail.com>; Chris.Stephens_at_adm.com<mailto:Chris.Stephens_at_adm.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> This is well-known approach named as "start_of_group" on our russian forum(sql.ru<http://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<mailto: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<mailto: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<mailto: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<mailto: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 - 22:13:13 CET