Detect overlapping date ranges from the same table

by

If you already have entries for each day that should work, but if you don’t the overhead is significant, and if that query is used often, if will affect performance.

If the data is in this format, you can detect overlaps using simple date arithmetic, because an overlap is simply one interval starting after a given interval, but before the given is finished, something like

select dr1.* from date_ranges dr1
inner join date_ranges dr2
on dr2.start > dr1.start -- start after dr1 is started
  and dr2.start < dr1.end -- start before dr1 is finished

If you need special handling for interval that are wholly within another interval, or you need to merge intervals, i.e.

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  20/01/2010  S
02    15/01/2010  31/01/2010  S

yielding

Start       End         Type
=====       ===         ====
01/01/2010  31/01/2010  S

you will need more complex calculation.

In my experience with this kind of problems, once you get how to do the calculation by hand, it’s easy to transfer it into SQL 🙂

More Related Contents:

  1. Syntax not valid on word ‘ON’ in SQL join statement [closed]
  2. Need SQL Script
  3. How do I use parameters in VBA in the different contexts in Microsoft Access?
  4. What is an index in SQL?
  5. SQL to LINQ Tool [closed]
  6. Escaping keyword-like column names in Postgres
  7. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  8. TSQL Pivot without aggregate function
  9. Why are there gaps in my IDENTITY column values?
  10. GROUP BY + CASE statement
  11. Pass R variable to RODBC’s sqlQuery? [duplicate]
  12. The Next-gen Databases [closed]
  13. How do I return rows with a specific value first?
  14. Execute Stored Procedure from a Function
  15. How to count occurrences of a column value efficiently in SQL?
  16. Explode (transpose?) multiple columns in Spark SQL table
  17. How do I use ROW_NUMBER()?
  18. How to SUM two fields within an SQL query
  19. How to compare two dates to find time difference in SQL Server 2005, date manipulation
  20. Store multiple bit values in a single table column
  21. How to select records with maximum values in two columns?
  22. How do I loop through a set of records in SQL Server?
  23. Group All Related Records in Many to Many Relationship, SQL graph connected components
  24. How can I make an average of dates in MySQL?
  25. adding primary key to sql view
  26. How do I call an Oracle stored procedure from an Excel VBA script?
  27. What is the best way to generate ranks in MYSQL?
  28. SQL SERVER DATETIME FORMAT
  29. SQL WITH clause example [duplicate]
  30. Insert all values of a table into another table in SQL

Leave a Comment