finding the Overlapping record from Start date to end date

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
roy_sanuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
SELECT *
  FROM (SELECT cid,
               bkid,
               startdate,
               enddate,
               LAG(enddate, 1) OVER(PARTITION BY cid ORDER BY startdate) prevend,
               LEAD(startdate, 1) OVER(PARTITION BY cid ORDER BY startdate) nextstart
          FROM ibc1)
 WHERE startdate <= prevend OR enddate >= nextstart
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I wrote an article on this called T-SQL: Identify bad dates in a time series, where one of the definitions of 'bad' is rows with overlapping start date and end date.

Knock yourself out..
0
sdstuberCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
awking00Commented:
So what would you like for a result from your sample data and why?
0
roy_sanuAuthor Commented:
when i run the query

it tells me


ORA-00923: FROM keyword not found where expected

0
roy_sanuAuthor Commented:
sorry i missed some comma, its working

thanks
0
Steve WalesSenior Database AdministratorCommented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
sdstuberCommented:
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
0
NetminderCommented:
Force-accepted to close.

Netminder
Senior Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.