How to find overlapping dates ranges with MSSQL

Applies to:

  • MS SQL Server 7
  • MS SQL Server 2000
  • MS SQL Server 2005

Data used

  1. USE sql911;
  2. go
  3.  
  4. SELECT projno, projname,
  5.        prstdate AS 'Start date',
  6.        prendate AS 'End date'
  7. FROM projects;
  8. go
projno projname                 Start date              End date
------ ------------------------ ----------------------- -----------------------
AD3100 ADMIN SERVICES           2004-01-01 00:00:00.000 2004-02-01 00:00:00.000
AD3110 GENERAL ADMINSYSTEMS     2004-02-02 00:00:00.000 2004-03-03 00:00:00.000
AD3111 PAYROLL PROGRAMMING      2001-03-01 00:00:00.000 2001-03-15 00:00:00.000
AD3112 PERSONNEL PROGRAMMING    2001-04-01 00:00:00.000 2001-07-01 00:00:00.000
AD3113 ACCOUNT PROGRAMMING      1999-01-01 00:00:00.000 1999-12-01 00:00:00.000
IF1000 QUERY SERVICES           2006-02-01 00:00:00.000 2006-02-15 00:00:00.000
IF2000 USER EDUCATION           2006-03-01 00:00:00.000 2006-03-20 00:00:00.000
MA2100 WELDLINE AUTOMATION      2006-04-01 00:00:00.000 2006-06-01 00:00:00.000
MA2110 W L PROGRAMMING          2006-05-01 00:00:00.000 2007-02-01 00:00:00.000
MA2111 W L PROGRAM DESIGN       2007-01-01 00:00:00.000 2007-02-02 00:00:00.000
MA2112 W L ROBOT DESIGN         2007-02-01 00:00:00.000 2007-02-02 00:00:00.000
MA2113 W L PRODCONT PROGS       2007-03-01 00:00:00.000 2007-04-01 00:00:00.000
OP1000 OPERATION SUPPORT        2007-04-01 00:00:00.000 2007-08-01 00:00:00.000
OP1010 OPERATION                2007-05-01 00:00:00.000 2007-08-01 00:00:00.000
OP2000 GEN SYSTEM SSERVICES     2006-02-01 00:00:00.000 2006-05-03 00:00:00.000
OP2010 SYSTEMS SUPPORT          2007-02-16 00:00:00.000 2006-05-02 00:00:00.000
OP2011 SCP SYSTEMS SUPPORT      2006-04-01 00:00:00.000 2007-07-01 00:00:00.000
OP2012 APPLICATIONS SUPPORT     2007-04-01 00:00:00.000 2007-07-01 00:00:00.000
OP2013 DB/DCSUPPORT             2006-05-01 00:00:00.000 2006-07-01 00:00:00.000
PL2100 WELD LINE PLANNING       2005-01-01 00:00:00.000 2005-03-01 00:00:00.000

(20 row(s) affected)

Overlapping dates

  1. SELECT b.projno, b.projname, 'Overlaps ' + a.projno + ' ' + a.projname AS 'Overlaps'
  2. FROM projects a, projects b
  3. WHERE a.projno != b.projno and
  4.       b.prstdate >= a.prstdate and
  5.       b.prstdate <= a.prendate
  6. ORDER BY b.projno;
  7. go
projno projname                 Overlaps
------ ------------------------ ----------------------------------------
IF1000 QUERY SERVICES           Overlaps OP2000 GEN SYSTEM SSERVICES
IF2000 USER EDUCATION           Overlaps OP2000 GEN SYSTEM SSERVICES
MA2100 WELDLINE AUTOMATION      Overlaps OP2000 GEN SYSTEM SSERVICES
MA2100 WELDLINE AUTOMATION      Overlaps OP2011 SCP SYSTEMS SUPPORT
MA2110 W L PROGRAMMING          Overlaps MA2100 WELDLINE AUTOMATION
MA2110 W L PROGRAMMING          Overlaps OP2000 GEN SYSTEM SSERVICES
MA2110 W L PROGRAMMING          Overlaps OP2011 SCP SYSTEMS SUPPORT
MA2110 W L PROGRAMMING          Overlaps OP2013 DB/DCSUPPORT
MA2111 W L PROGRAM DESIGN       Overlaps MA2110 W L PROGRAMMING
MA2111 W L PROGRAM DESIGN       Overlaps OP2011 SCP SYSTEMS SUPPORT
MA2112 W L ROBOT DESIGN         Overlaps MA2110 W L PROGRAMMING
MA2112 W L ROBOT DESIGN         Overlaps MA2111 W L PROGRAM DESIGN
MA2112 W L ROBOT DESIGN         Overlaps OP2011 SCP SYSTEMS SUPPORT
MA2113 W L PRODCONT PROGS       Overlaps OP2011 SCP SYSTEMS SUPPORT
OP1000 OPERATION SUPPORT        Overlaps MA2113 W L PRODCONT PROGS
OP1000 OPERATION SUPPORT        Overlaps OP2011 SCP SYSTEMS SUPPORT
OP1000 OPERATION SUPPORT        Overlaps OP2012 APPLICATIONS SUPPORT
OP1010 OPERATION                Overlaps OP1000 OPERATION SUPPORT
OP1010 OPERATION                Overlaps OP2011 SCP SYSTEMS SUPPORT
OP1010 OPERATION                Overlaps OP2012 APPLICATIONS SUPPORT
OP2000 GEN SYSTEM SSERVICES     Overlaps IF1000 QUERY SERVICES
OP2010 SYSTEMS SUPPORT          Overlaps OP2011 SCP SYSTEMS SUPPORT
OP2011 SCP SYSTEMS SUPPORT      Overlaps MA2100 WELDLINE AUTOMATION
OP2011 SCP SYSTEMS SUPPORT      Overlaps OP2000 GEN SYSTEM SSERVICES
OP2012 APPLICATIONS SUPPORT     Overlaps MA2113 W L PRODCONT PROGS
OP2012 APPLICATIONS SUPPORT     Overlaps OP1000 OPERATION SUPPORT
OP2012 APPLICATIONS SUPPORT     Overlaps OP2011 SCP SYSTEMS SUPPORT
OP2013 DB/DCSUPPORT             Overlaps MA2100 WELDLINE AUTOMATION
OP2013 DB/DCSUPPORT             Overlaps MA2110 W L PROGRAMMING
OP2013 DB/DCSUPPORT             Overlaps OP2000 GEN SYSTEM SSERVICES
OP2013 DB/DCSUPPORT             Overlaps OP2011 SCP SYSTEMS SUPPORT

(31 row(s) affected)
  1. A project can and does overlap more than other project
  2. To find the overlapping dates do:
  • A self join to generate all the possible combinations.
FROM projects a, projects b
  • Eliminate the duplicates projects
  1. WHERE a.projno != b.projno
  • We want:
    • Different project number [it can't overlap itself].
    • The other project starting has to start between the starting and ending date of the project.
  1. WHERE a.projno != b.projno and
  2.       b.prstdate >= a.prstdate and
  3.       b.prstdate <= a.prendate
  • Note that we had 20 projects, but we have 31 overlapping dates. A project can overlap more than one other project.