Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here


Posting Guidelines


Students Click Here


Find overlaping dates for one specific Code

Find overlaping dates for one specific Code

Find overlaping dates for one specific Code

Using SQLServer2008, I trying to write a query that will tell me for a specific code, if the effective date and end dates overlap. Here is an example of a record with 3 codes with effective and end dates.

Code Effect_date End_date
AHS 1/1/1900 7/6/2007
AHS 7/7/2007 12/31/2099
AHS 1/1/2009 12/31/2099 (overlay)

RE: Find overlaping dates for one specific Code

what you need to do is compare each row for every code to every other row with the same code

this can be done by an INNER JOIN joining only on the code

then, you have to compare their ranges

let's identify the effect_date and end_date for the two rows being compared as startA to endA and startB to endB as in the following diagram --


startA                endA          
                     |                   |               
1   startB-----endB  |                   |               
                     |                   |               
2            startB--|--endB             |               
                     |                   |               
3                    |  startB-----endB  |               
                     |                   |               
                     |                   |               
5                    |           startB--|--endB 
                     |                   |               
6                    |                   |  startB-----endB 

then you can easily find all the overlaps like this --


... WHERE endB >= startA  /* eliminates case 1 */
      AND startB <= endA  /* eliminates case 6 */ 

notice that it's an AND which means both of those have to be true

so applying this to your table...


SELECT this.code
     , this.effect_date
     , this.end_date
     , that.effect_date
     , that.end_date
  FROM daTable AS this
  JOIN daTable AS that
    ON this.code = that.code 
   AND that.end_date >= this.effect_date
   AND that.effect_date <= this.end_date 

let me know how that works for you

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Find overlaping dates for one specific Code

Absolutely, I will try it out. Thanks again!

Red Flag This Post


Red Flag Submitted


Reply To This Thread



Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close