We help IT Professionals succeed at work.

finding the  Overlapping record  from Start date to end date

roy_sanu
roy_sanu asked
on
361 Views
Last Modified: 2014-11-13
I  have a  data which contain table ibc1 columns are cid, Bkid is a composite key, start date and end date


my data is

cid, bkid,    startdate(mm/dd/yyyy),    end date(mm/dd/yyyy)

C1    B1      01/01/2014                           06/06/2014

C1    B2      06/05/2014                          06/09/2014

C1    B3      06/01/2014                          12/31/2014

C2    B1     05/01/2012                           12/02/2013

C3   B1     12/03/2013                             12/03/2014


My question is i need a procedure(PL/SQL) or a SQL  which will list down list of cid  which has are  Overlapping  within the range from start date to end date
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Jim, This question is for Oracle, but for tsql - in your article, sqlsever v 2012 also supports LEAD and LAG, thus saving some extra io in your self-joins.

The row_number and self-join approach could work in oracle too, but it doesn't scale as well


just fyi,I  hope it helps
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Yep.  The question mentioned SQL, and for some odd reason this question appeared in my 'All recommended news' (btw do you know where settings can be edited for this?), so I thought I'd answer.    The original code was written for SQL 2008, and instead of rewriting for LEAD and LAG I just made a one-sentence mention of it.

I'm also going on the wild assumption that anyone programming in any other database will be able to read this, and emulate it to meet their specific database needs.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
So what would you like for a result from your sample data and why?
roy_sanudeveloper

Author

Commented:
when i run the query

it tells me


ORA-00923: FROM keyword not found where expected

roy_sanudeveloper

Author

Commented:
sorry i missed some comma, its working

thanks
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for roy_sanu's comment #a40294187

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Objection.  'I forgot a comma', when the asker never posted their SQL in the first place, is not a useful question.
Recommend either a delete or a split between sdstuber's likely correct answer, and my badassery article on (pause for effect.  wait for it...)  how to detect duplicate rows in a time series, which is this question title.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I object to a delete and to the roy_sanu's post as an answer.

My post answered the question.   Even if the asker's code (which isn't posted) was only missing a comma, the fact is the question was asked and answered.

I'm ok with a split to the article too
Force-accepted to close.

Netminder
Senior Admin
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.