Home » SQL & PL/SQL » SQL & PL/SQL » overlap interval (oracle 9i , window xp)
overlap interval [message #380226] Fri, 09 January 2009 11:15 Go to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello,

how to detect if date intervals overlap?
This is the table:
table contrat
(
numcontrat number(5);
datedeb date;
dateend date;
)

example :
numcontrat datedeb dateend
1 02/02/2008 10/03/2008
2 20/03/2008 30/04/2008
3 10/02/2008 15/04/2008
4 16/04/2008 15/05/2008
5 16/05/2008 31/08/2008
6 01/10/2008 31/10/2008
7 10/11/2008 31/12/2008
8 10/11/2008 15/12/2008

numcontrat 3 overlap 1 and 2
numcontrat 4 overlap 2
numcontrat 8 overlap 7

an idea?
Re: overlap interval [message #380227 is a reply to message #380226] Fri, 09 January 2009 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 28 October 2008 14:45
...
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


What should be the result of your data?
Have a look at LAG/LEAD functions.

Regards
Michel

Re: overlap interval [message #380229 is a reply to message #380227] Fri, 09 January 2009 11:30 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Sorry for the format!

I want to know all the numcontrat that overlap.
Re: overlap interval [message #380233 is a reply to message #380226] Fri, 09 January 2009 11:40 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
an idea?

What about using a simple logic? Two intervals A and B overlap, when A.DATEDEB <= B.DATEEND AND A.DATEEND >= B.DATEDEB, which may be deduced e.g. from a picture:
A  |---------|
DATEDEB   DATEEND

B       |-------------------|
     DATEDEB             DATEEND

Use self join. When 8 overlap 7, then 7 overlap 8 - if you do not include the second detection, add appropriate WHERE condition (A.NUMCONTRAT > B.NUMCONTRAT). In the end, aggregate B interval numbers.
Re: overlap interval [message #380244 is a reply to message #380229] Fri, 09 January 2009 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Haler wrote on Fri, 09 January 2009 18:30
Sorry for the format!

I want to know all the numcontrat that overlap.

Post the result as the result of a query.
With your data, what should be the output?

Regards
Michel
Re: overlap interval [message #381993 is a reply to message #380226] Tue, 20 January 2009 09:34 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello,
and now if I have the following table:

table contrat
(numsal number(4),
numcontrat number(5),
datedeb date,
dateend date
);

what is the script that can give me this?

see attached doc.
  • Attachment: doc.txt
    (Size: 0.81KB, Downloaded 213 times)
Re: overlap interval [message #381994 is a reply to message #380226] Tue, 20 January 2009 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: overlap interval [message #382001 is a reply to message #381993] Tue, 20 January 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 09 January 2009 18:21
From your previous topic:
Michel Cadot wrote on Tue, 28 October 2008 14:45
...
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


What should be the result of your data?
...
Regards
Michel

Re: overlap interval [message #382012 is a reply to message #382001] Tue, 20 January 2009 11:11 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
hello Michel,

I do not understand your request for formatting:
I have attached a formatted text file.

It contains my application and the result I want.

I lined my text by typing, alignment disappears in preview!

Why?
Re: overlap interval [message #382016 is a reply to message #382012] Tue, 20 January 2009 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post it online. Some of us can't or don't want to download.
AND your posted table is not formatted.
AND this is not the only section of guide.

Regards
Michel
Re: overlap interval [message #382087 is a reply to message #381993] Tue, 20 January 2009 22:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Haler wrote on Fri, 09 January 2009 18:15
an idea?

Haler wrote on Tue, 20 January 2009 16:34
what is the script that can give me this?

I am extremely curious, what do you not understand on the idea I gave you. Or you are not interested in query and need "script" (whatever it means) instead?
If you do not know what self join is (as other parts of the query I already provided), search in SQL Reference book, available with all Oracle documentation e.g. online on http://tahiti.oracle.com/.

By the way, when e.g "numcontrat 8 overlap 7", then also "numcontrat 7 overlap 8". Why did you not specify it too?
Re: overlap interval [message #382494 is a reply to message #382087] Thu, 22 January 2009 11:34 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello,

Forget this message.

Thanks for attention.
Re: overlap interval [message #382501 is a reply to message #382494] Thu, 22 January 2009 14:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The lack of formatting is way over stated here. The OP posted more than enough info to answer the question. Lack of formatting is almost never an issue on Oracle forums - see their simple guideleines ==> http://forums.oracle.com/forums/ann.jspa?annID=886

Of course we all appreciate formatting - but that's not what this forum is about. It's about helping others & getting answers.

Some hopefully useful links on overlapping dates...
http://www.orafaq.com/node/2067
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101
Re: overlap interval [message #382505 is a reply to message #382501] Thu, 22 January 2009 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The OP posted more than enough info to answer the question.

This is your opinion.
For myself I asked for the result as I didn't understand well.
Flyboy too made appropriate remarks.
Both are not answered.

Quote:
Lack of formatting is almost never an issue on Oracle forums - see their simple guideleines ==> http://forums.oracle.com/forums/ann.jspa?annID=886

This is also your opinion and other forum guidelines does not imply this is this forum guidelines.

Quote:
It's about helping others

I agree and learning them how to post and helping to express/explain the question is for me the FIRST help I should give as this is the first step to answer a problem.

Quote:
& getting answers.

Not for me, learning is the most important point not getting an answer.

This does not mean that you can't answer but in your side you have not to tell me I can't ask for formatting or more information or detailed explainations.

Quote:
Some hopefully useful links on overlapping dates...

Maybe OP didn't want them, maybe he just want a solution.
Anyway we'll never know it as he doesn't give feedback.
I think "Forget this message" means I got the answer.

Regards
Michel

[Updated on: Thu, 22 January 2009 15:17]

Report message to a moderator

Re: overlap interval [message #382620 is a reply to message #382505] Fri, 23 January 2009 04:16 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello all,

I think the answer is more important than the format 
(to a certain point ,I agree).
The problem is that the formatting is lost,
and we spend time to translate into English.
(Yes we can!)
I put two tags (code and /code)!

Thank you anyway for your answers.
Re: overlap interval [message #382636 is a reply to message #382505] Fri, 23 January 2009 05:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Personally, I feel the forum would be greatly improved if the number of posts that only ask posters to read the guidelines and format their code were much lower.

By all means include these points in a post, but if the above is the only content in a post, I think it is worth considering whether the post actually adds anything to the thread.

People come here to learn, or to get answers to questions that they cannot solve by themselves. They don't come here to be lectured and nagged about their posting style, and if the current levels of nagging continue, I'm sure many of them will simply post elsewhere, and the whole forum will be worse off for this.

Re: overlap interval [message #382750 is a reply to message #382620] Sat, 24 January 2009 04:00 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Haler wrote on Fri, 23 January 2009 11:16
I think the answer is more important than the format
(to a certain point ,I agree).

I think that also giving feedback is important too (instead of repeating the same question).
So, did you tried any way posted here or anywhere else? What was the result?
Re: overlap interval [message #383145 is a reply to message #382750] Tue, 27 January 2009 07:02 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello,
I think my problem solved this way:

SQL> ed
écrit file afiedt.buf

  1  SELECT *
  2  FROM   CONTRAT A, CONTRAT B
  3  WHERE  (A.DATEDEB,A.DATEEND) OVERLAPS (B.DATEDEB,B.DATEEND)
  4* and a.datedeb<>b.datedeb and a.dateend<>b.dateend
SQL> r
  1  SELECT *
  2  FROM   CONTRAT A, CONTRAT B
  3  WHERE  (A.DATEDEB,A.DATEEND) OVERLAPS (B.DATEDEB,B.DATEEND)
  4* and a.datedeb<>b.datedeb and a.dateend<>b.dateend

    NUMSAL NUMCONTRAT DATEDEB  DATEEND      NUMSAL NUMCONTRAT DATEDEB  DATEEND
---------- ---------- -------- -------- ---------- ---------- -------- --------
         1          1 02/02/08 10/03/08          1          3 10/02/08 15/04/08
         1          2 20/03/08 30/04/08          1          3 10/02/08 15/04/08
         1          2 20/03/08 30/04/08          1          4 16/04/08 15/05/08
         1          3 10/02/08 15/04/08          1          1 02/02/08 10/03/08
         1          3 10/02/08 15/04/08          1          2 20/03/08 30/04/08
         1          4 16/04/08 15/05/08          1          2 20/03/08 30/04/08

6 ligne(s) sélectionnée(s).


Script sql for test:
--------------------
create table contrat
(numsal number(4),
 numcontrat number(5),
 datedeb date,
 dateend date
);

insert into contrat values (1,1,'02/02/2008','10/03/2008');
insert into contrat values (1,2,'20/03/2008','30/04/2008');
insert into contrat values (1,3,'10/02/2008','15/04/2008');
insert into contrat values (1,4,'16/04/2008','15/05/2008');
insert into contrat values (1,5,'16/05/2008','31/08/2008');
insert into contrat values (1,6,'01/10/2008','31/10/2008');
insert into contrat values (1,7,'10/11/2008','31/12/2008');
insert into contrat values (1,8,'10/11/2008','15/12/2008');
commit;

it remains to eliminate the symmetry of overlap.
What do you think about this?
Re: overlap interval [message #383167 is a reply to message #383145] Tue, 27 January 2009 08:12 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Haler wrote on Tue, 27 January 2009 08:02

  3  WHERE  (A.DATEDEB,A.DATEEND) OVERLAPS (B.DATEDEB,B.DATEEND)



wow, I though I was pretty much up on things, but I never heard of the OVERLAPS function.

Quote:

insert into contrat values (1,1,'02/02/2008','10/03/2008');

What do you think about this?


That part is still bad. You are inserting strings into DATE columns. Where I come from, West of the Atlantic, the second string is October 3rd.
Re: overlap interval [message #383170 is a reply to message #383167] Tue, 27 January 2009 08:21 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
1) See Oracle Faq's "undocumented OVERLAPS Function".
2) for me the format of the date field is good.
Re: overlap interval [message #383171 is a reply to message #380233] Tue, 27 January 2009 08:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Haler wrote on Tue, 27 January 2009 14:02
it remains to eliminate the symmetry of overlap.
What do you think about this?

flyboy wrote on Fri, 09 January 2009 18:40
When 8 overlap 7, then 7 overlap 8 - if you do not include the second detection, add appropriate WHERE condition (A.NUMCONTRAT > B.NUMCONTRAT).

You were many times asked to specify which of them should be eliminated and which should be left (e.g the one with greater A.NUMCONTRAT should be left). After stating this, it is quite easy to construct the additional WHERE condition, is not it?

Also surprised with OVERLAPS function, as I did not find any remark about it in the documentation. But, the conditions I stated earlier are not more complex; and they let you specify the border conditions (whether e.g. intervals 1-2 and 2-3 overlap or no).
Re: overlap interval [message #383194 is a reply to message #383170] Tue, 27 January 2009 12:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Haler wrote on Tue, 27 January 2009 09:21

2) for me the format of the date field is good.


But you posted a test script for US to test, which will not work for anyone other than yourself (possibly). Why don't you do it correctly and prevent yourself from getting into bad habits early on?
FOO SCOTT>create table contrat
  2  (numsal number(4),
  3  numcontrat number(5),
  4  datedeb date,
  5  dateend date
  6  )
  7  /

Table created.

FOO SCOTT>insert into contrat values (1,2,'20/03/2008','30/04/2008');
insert into contrat values (1,2,'20/03/2008','30/04/2008')
                                *
ERROR at line 1:
ORA-01843: not a valid month
Re: overlap interval [message #383296 is a reply to message #383194] Wed, 28 January 2009 02:29 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hi,
why no try:
alter session set nls_date_format='DD/MM/YYYY';

?
Re: overlap interval [message #385167 is a reply to message #383296] Fri, 06 February 2009 15:47 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

why no try:
alter session set nls_date_format='DD/MM/YYYY';



Because that will in most cases not work in real live.

In real live a user will get the "ORA-01843: not a valid month" error in an application you wrote where he has no chance of altering the session.

That's why implicit conversions are a bad habit and should be avoided in ALL scripts that leak to some public place, like this forum.
Previous Topic: How to get these strings each in a line
Next Topic: partitions (merged)
Goto Forum:
  


Current Time: Sun Dec 04 00:44:50 CST 2016

Total time taken to generate the page: 0.09278 seconds