let’s set up a timeline to illustrate the problem
let’s say that the user enters numbers in a START and END range, and you have transactions defined using RangeFrom and RangeTo numbers
here are all the ways that a transaction overlap the user’s numbers –
START END
| |
1 RangeFrom---RangeTo | |
| |
2 RangeFrom--|--RangeTo |
| |
3 | RangeFrom---RangeTo |
| |
4 RangeFrom---|---------------------|---RangeTo
| |
5 | RangeFrom--|--RangeTo
| |
6 | | RangeFrom---RangeTo
with me so far?
now, all we have to do is choose every transaction that satisfies one of the conditions 2 through 5
the easiest way is like this –
... WHERE RangeTo >= START /* eliminates case 1 */
AND RangeFrom <= END /* eliminates case 6 */
notice that it’s an AND which means both of those have to be true