Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find overlaping dates for one specific Code

Status
Not open for further replies.

Almie

Technical User
Nov 28, 2006
39
US
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)


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

Code:
                   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 --

Code:
... 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...

Code:
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
 
Absolutely, I will try it out. Thanks again!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top