I have a table with the following data
PKey Start End Type ==== ===== === ==== 01 01/01/2010 14/01/2010 S 02 15/01/2010 31/01/2010 S 03 05/01/2010 06/01/2010 A
And want to get the following results
PKey Start End Type ==== ===== === ==== 01 01/01/2010 14/01/2010 S 03 05/01/2010 06/01/2010 A
Any ideas on where to start? A lot of the reading I’ve done suggests I need to create entries and for each day and join on matching days, is this the only way?
Advertisement
Answer
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 🙂