Find overlaping dates for one specific Code

[Almie]

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)

 

[r937]

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  |               
                     |                   |               
4            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
INNER
  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

 

[Almie]

Absolutely, I will try it out. Thanks again!