RE: friday afternoon brain fog -> SQL question
- From: TJ Kiernan <tkiernan@xxxxxxxxxxx>
- To: "'iggy_fernandez@xxxxxxxxxxx'" <iggy_fernandez@xxxxxxxxxxx>, "xt.and.r@xxxxxxxxx" <xt.and.r@xxxxxxxxx>, Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Fri, 20 Mar 2015 21:13:13 +0000
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Iggy Fernandez
Sent: Friday, March 20, 2015 4:06 PM
To: xt.and.r@xxxxxxxxx; Andrew Kerber
Cc: rjanuary@xxxxxxxxx; Chris Stephens; oracle-l@xxxxxxxxxxxxx
Subject: RE: friday afternoon brain fog -> SQL question
Very nice.
[oracle@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@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@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@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>
To: andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>
CC: rjanuary@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>;
Chris.Stephens@xxxxxxx<mailto:Chris.Stephens@xxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>> 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@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>> wrote:
I apologize, right after hitting send I noticed your last two sentences.
On Mar 20, 2015, at 3:29 PM, Ryan January
<rjanuary@xxxxxxxxx<mailto:rjanuary@xxxxxxxxx>> 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@xxxxxxx<mailto:Chris.Stephens@xxxxxxx>> 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
Other related posts:
