OraFAQ Forum: SQL & PL/SQL » SQL date overlapping

Home » SQL & PL/SQL » SQL & PL/SQL » SQL date overlapping

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL date overlapping [message #634975]

Wed, 18 March 2015 09:17

sanodani
Messages: 98
Registered: October 2014

Member

Hallo all..

I am trying to check date overlapping in SQL query, but i dont know, how can i do this.. can anyone please help me in this regards?

my case.. :

create table testpro (
        pid number,
        group_nr_from number,
        f_date date,
        group_nr_till number,
        t_date date
    );
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (123, 0, '01.02.2008',150, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (120, 151, '01.02.2008',200, '21.12.2008'); 
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (125, 201, '01.02.2008',300, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 250, '01.02.2008',500, '21.12.2008');  
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (130, 300, '01.02.2008', 500, '21.12.2008');

Hoping for your Kind cooperation.
thankx in advance
regards,

Report message to a moderator

Re: SQL date overlapping [message #634977 is a reply to message #634975]

Wed, 18 March 2015 09:22

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

What is the desired output? What are the rules to derive at the desired output? And, what did you try so far? Please post the required details.

Seems like you need to use LAG function.

Report message to a moderator

Re: SQL date overlapping [message #634978 is a reply to message #634977]

Wed, 18 March 2015 09:25

sanodani
Messages: 98
Registered: October 2014

Member

Hi Lalit..

thankx for your prompt reply..
well, i am trying to findout wheather the overlapping data (with the same Group_nr) in the table is? if it is, then it should Show the overlapping data.

regards,

Report message to a moderator

Re: SQL date overlapping [message #634984 is a reply to message #634978]

Wed, 18 March 2015 10:57

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once again show us what should be the result for the data you gave.
In addition, define "overlapping". Do group_nr_from and group_nr_till enter into play?

Report message to a moderator

Re: SQL date overlapping [message #635005 is a reply to message #634984]

Wed, 18 March 2015 18:13

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

You need to slow down and think a little first.

Think of these dotted lines as a date range. Obviously each date range has a start date and an end date. The start date is the first date a dotted line and the end date is the last dot. You can see from the visual representation, given two date ranges, which scenarios make for an overlap. Now all you have to do is code it using SQL date logic.

1.   .....
          .....

2.   .....
         .....

3.   .....
       .....

4.     .....
       .....

4b.    .....
        ...

4c.     ...
       .....

5.        .....
       .....

6.        .....
      .....

7.         .....
      .....

You read #1 as "the end date of the first date range is before the start date of the second date range".
You read #2 as "the end date of the first date range matches the start date of the second date range".
You read #3 as "the end date of the first date range is after the start date of the second date range".

and so on, you have to think about it. You will have to address the question of are matching start/end dates overlap. And you will have to deal with the fact that the date ranges can be swapped (which one is the first and which is the second?).

Does anyone see a mistake in the date range overlap diagram above? I don't but I have made mistakes with this before.

Kevin

Report message to a moderator

Re: SQL date overlapping [message #635016 is a reply to message #635005]

Thu, 19 March 2015 02:27

sanodani
Messages: 98
Registered: October 2014

Member

Hallo all,

Thank you very much for you Input, and Suggestion/Feedback.

Well, I am trying to find/check if there is any Group that they are overlapping the same date time.
like in my case example,
Group_nr_from: 250 and Group_nr_till: 500 is overlapping the above
Group_nr_from: 201 and Group_nr_till: 300 because the Groups 250 is already inside the 300

so i am trying to check if there any Group making this overlapping or not, if it is, then should Show which Group_nr_from/till.

I hope i make it bit clear.. Smile

Ihave tried this query but did not get the result what i wanted to have.. :/

select a.group_nr_from, a.group_nr_till  from testpro a where exists (select 1 from testpro b where b.f_date <= a.t_date and b.t_date >= a.f_date) order by 2,1;

thankx again
regards,

Report message to a moderator

Re: SQL date overlapping [message #635017 is a reply to message #635016]

Thu, 19 March 2015 02:32

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It s still not clear how "Group_nr_from" and " Group_nr_till" comes into play.
In addition, you did not take into account "pid".
How does it come into play in your overlapping issue?

Report message to a moderator

Re: SQL date overlapping [message #635018 is a reply to message #635017]

Thu, 19 March 2015 03:20

sanodani
Messages: 98
Registered: October 2014

Member

Hallo Michael
Thankx for your prompt reply.. Smile

well, 'Group_nr_from" and "Group_nr_till" Play role and also the "PID". The _from and _till Group nr is the Group that there lies different other groups within that range.

I have to check if there is any PID, that are in the same Group range, are overlapping the date?
if it is so, then should Show Output, which pid, Group_nr_from and _till are overlapping?

i have changed bit my insert query with one new insert Statement with the same date value.

 
insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 250, '01.02.2008',500, '21.12.2008');

I am sorry, its bit hard to explain and sorry to bother you...

regards,

Report message to a moderator

Re: SQL date overlapping [message #635019 is a reply to message #635018]

Thu, 19 March 2015 03:39

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just show the output we should have with the data you gave.
From your sentence "I have to check if there is any PID, that are in the same Group range, are overlapping the date?" I infer that, for this question, PID does not matter.

From this sentence " The _from and _till Group nr is the Group that there lies different other groups within that range." I infer that this is not only date overlaps but also group overlaps.

Are these correct?

Report message to a moderator

Re: SQL date overlapping [message #635020 is a reply to message #635019]

Thu, 19 March 2015 03:54

sanodani
Messages: 98
Registered: October 2014

Member

PID is also the important to check such duplication of data in the table
and Yes, the second question is right as you have mentioned. Smile

here is my Output of Testpro Table:

PID GROUP_NR_FROM F_DATE GROUP_NR_TILL T_DATE
120 151 01.02.2008 200 21.12.2008
123 0 01.02.2008 150 21.12.2008
125 201 01.02.2008 300 21.12.2008
127 250 01.06.2008 500 21.12.2008
127 250 01.02.2008 500 21.12.2008
127 250 01.02.2008 500 21.12.2008
130 300 01.02.2008 500 21.12.2008

in this Output, there is PID = 127 with the same group_nr_till and different f_date which is already lies in the date range of 01.02.2008 to 21.12.2008
in such case, i should check if there are any such data which have the same PID/Group_Nr and are overlapping the date.

Report message to a moderator

Re: SQL date overlapping [message #635021 is a reply to message #635020]

Thu, 19 March 2015 04:34

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, this is the output of your table which is not the one you gave, so please provide the INSERT statements for this table.
Now, what should be the output of the result?

Please read How to use [code] tags and make your code easier to read.
Align the columns in result.

Report message to a moderator

Re: SQL date overlapping [message #635022 is a reply to message #635021]

Thu, 19 March 2015 04:42

sanodani
Messages: 98
Registered: October 2014

Member

sry for that...

here is my insert Statement ..

 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (123, 0, '01.02.2008',150, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (120, 151, '01.02.2008',200, '21.12.2008'); 
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (125, 201, '01.02.2008',300, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 250, '01.02.2008',500, '21.12.2008');  
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (130, 300, '01.02.2008', 500, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 250, '01.06.2008',500, '21.12.2008'); 
 
 commit;

and the current Output is :

PID    Group_NR_FROM  F_DATE            GROUP_NR_TILL   T_DATE
120	151	      01.02.2008	200	        21.12.2008
123	0	      01.02.2008	150	        21.12.2008
125	201	      01.02.2008	300	        21.12.2008
127	250	      01.06.2008	500	        21.12.2008
127	250	      01.02.2008	500	        21.12.2008
127	250	      01.02.2008	500	        21.12.2008
130	300	      01.02.2008	500	        21.12.2008

thank you.
regards,

Report message to a moderator

Re: SQL date overlapping [message #635029 is a reply to message #635022]

Thu, 19 March 2015 06:07

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And the result output is (third time)?

Report message to a moderator

Re: SQL date overlapping [message #635035 is a reply to message #635029]

Thu, 19 March 2015 06:55

sanodani
Messages: 98
Registered: October 2014

Member

Hallo Michel

here is my insert query and its Output and my required Output(but its not the correct, what i want to have..)

insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (123, 0, '01.02.2008',150, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (120, 151, '01.02.2008',200, '21.12.2008'); 
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (125, 201, '01.02.2008',300, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 250, '01.02.2008',500, '21.12.2008');  
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (130, 300, '01.02.2008', 500, '21.12.2008');
 insert into testpro (pid, group_nr_from, f_date, group_nr_till, t_date) values (127, 260, '01.06.2008',300, '21.12.2008'); 
 
 commit;

PID     Group_nr_From    F_date         Group_nr_till   T_date
127	260	        01.06.2008	300	        21.12.2008
123	0	        01.02.2008	150	        21.12.2008
120	151	        01.02.2008	200	        21.12.2008
125	201	        01.02.2008	300	        21.12.2008
127	250	        01.02.2008	500	        21.12.2008
130	300	        01.02.2008	500	        21.12.2008

and here is my result Output, what i would like to have, but its not the correct, because i get the repeated pid which have the same pid and the group already resides another group range and date is overlapping :/

 select a.pid, a.group_nr_from, a.group_nr_till  from testpro a where exists (select 1 from testpro b where b.f_date <= a.t_date and b.t_date >= a.f_date) order by 2,1;

PID    Group_Nf-From    Group_Nr_Till
123	0	        150
120	151	        200
125	201	        300
127	250	        500
127	250	        500
127	250	        500
130	300	        500

where i would like to avoid such overlapping data.
thank you

regards

Report message to a moderator

Re: SQL date overlapping [message #635039 is a reply to message #635035]

Thu, 19 March 2015 07:00

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

and here is my result Output, what i would like to have, but its not the correct,

So is this or is this not your desired result?

Report message to a moderator

Re: SQL date overlapping [message #635042 is a reply to message #635039]

Thu, 19 March 2015 07:11

sanodani
Messages: 98
Registered: October 2014

Member

Hallo Michel
the last Output is my approx. desired Output but my question is, how could i check the overlapping data in the table? In the table PID = 127 is overlapping with the same group range and the date.
in this case it shold not Display such overlapping data.
thankx

Report message to a moderator

Re: SQL date overlapping [message #635053 is a reply to message #635042]

Thu, 19 March 2015 08:45

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

We still don't know what should be the result.

Report message to a moderator

Previous Topic:	Handle duplicates during direct load path in SQL LOADER
Next Topic:	Result in same rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 02:15:25 CDT 2026