finding the  Overlapping record  from Start date to end date

Posted on 2014-08-28
Medium Priority
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
Question by:roy_sanu
  • 3
  • 3
  • 2
  • +3
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 40290135
               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
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 40290150
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..
LVL 74

Expert Comment

ID: 40290201
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 66

Expert Comment

by:Jim Horn
ID: 40290210
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.
LVL 32

Expert Comment

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

Author Comment

ID: 40294131
when i run the query

it tells me

ORA-00923: FROM keyword not found where expected


Author Comment

ID: 40294187
sorry i missed some comma, its working

LVL 23

Expert Comment

by:Steve Wales
ID: 40436470
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.
LVL 66

Expert Comment

by:Jim Horn
ID: 40436471
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.
LVL 74

Expert Comment

ID: 40437262
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

Expert Comment

ID: 40440839
Force-accepted to close.

Senior Admin

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question