The developer sketches out all the possible overlap scenarios and finds four:

1) End of range A overlaps start of range B:This leads to SQL like this (assuming all values are not null):

A----

B---------

2) Start of range A overlaps end of range B:

A--------

B----

3) Range A falls entirely within range B:

A---

B--------------

4) Range B falls entirely within range A:

A--------------

B---

where (a.start < b.startIf, as is often the case, the end dates are allowed to be null, meaning "forever", then the SQL becomes yet more complex. In some cases I have seen attempts at this where the developer has got it wrong and missed out one of the cases altogether.

and a.end between b.start and b.end)

or (a.start between b.start

and b.end and a.end > b.end)

or (a.start between b.start and b.end

and a.end between b.start and b.end)

or (b.start between a.start and a.end

and b.end between a.start and a.end)

In fact it is much easier to look at the cases where A and B

**do not**overlap, because there are only two such cases:

1) Range A ends before range B starts:This leads to the much simpler SQL:

A---

B-----

2) Range A starts after range B ends:

A-----

B---

where not (a.end < b.start or a.start > b.end)which can be rearranged to the even simpler (though perhaps less intuitive):

where a.end >= b.startEven if we have to allow for null end dates this is now very simple:

and a.start <= b.end

where nvl(a.end,b.start) >= b.startI don't claim that any of the above is original, I am sure this algorithm appears in many SQL and other books. But I see variants of the long-winded version (sometimes bug-ridden ones) so often I thought it worth documenting here so I can point to it in future.

and a.start <= nvl(b.end,a.start)

## 1 comment:

Elegant and simple.

Post a Comment