Intersecting number ranges

[dgtulsaguy]

I am comparing a user input parameter begin and end number to a begin and end number stored as separate values in a database table for the purpose of selecting records. Also, if a dbend number is zero, it needs to be included. Currently, this is done in an inefficient way and am hoping someone can help optimize.

In the select expert is the following formula:

dbend >= 19010101 and dbstart >= paramstart and dbend <= paramend or
dbend >= 19010101 and dbstart <= paramstart and dbend <= paramend and dbend >=paramstart or
dbend >= 19010101 and dbstart <= paramstart and dbend >= paramend or
dbend >= 19010101 and dbstart >= paramstart and dbstart <= paramend and dbend >= paramend or
dbstart >= paramstart and dbstart <= paramend and dbend = 0 or
dbstart < paramstart and dbend = 0


Any help is greatly appreciated.

[wolle]

dbend >= 19010101 and dbstart >= paramstart and dbend <= paramend or
dbend >= 19010101 and dbstart <= paramstart and dbend <= paramend and dbend >=paramstart or
dbend >= 19010101 and dbstart <= paramstart and dbend >= paramend or
dbend >= 19010101 and dbstart >= paramstart and dbstart <= paramend and dbend >= paramend or
dbstart >= paramstart and dbstart <= paramend and dbend = 0 or
dbstart < paramstart and dbend = 0

If you are allowed to use parentheses () you could "break out" dbend >= 19010101 and dbend = 0 and get the following:

dbend >= 19010101 and	(dbstart >= paramstart and dbend <= paramend or
			dbstart <= paramstart and dbend <= paramend and dbend >=paramstart or					dbstart <= paramstart and dbend >= paramend or
			dbstart >= paramstart and dbstart <= paramend and dbend >= paramend) or
dbend = 0 and		(dbstart >= paramstart and dbstart <= paramend or
			dbstart < paramstart)

I note that the expressions within the first set of parentheses check for the 4 possible overlapping cases between the [dbstart .. dbend] and [paramstart .. paramend] intervals. I think it can be reduced, resulting in this,

dbend >= 19010101 and (dbend >= paramstart and dbstart <= paramend) or
dbend = 0  and        (dbstart >= paramstart and dbstart <= paramend or
	                dbstart < paramstart)

I think that also the second set of parentheses can be reduced,

dbend >= 19010101 and (dbend >= paramstart and dbstart <= paramend) or
dbend = 0  and        (dbstart <= paramend)

and then finally (dbstart <= paramend) can be broken out to,

(dbstart <= paramend) and (dbend >= 19010101 and dbend >= paramstart or dbend = 0)

This obviously has to be checked and tested carefully.

[wolle]

I note that the expressions within the first set of parentheses check for the 4 possible overlapping cases between the [dbstart .. dbend] and [paramstart .. paramend] intervals. I think it can be reduced,

This is my motivation for the reduction of the first set of parentheses in #2:

Say you have two intervals [dbstart .. dbend] and [paramstart .. paramend] and want to know if they overlap.

You can of course check if any of the four overlapping cases apply, but you can also start with the two non-overlapping cases, like

(dbend < paramstart) or (dbstart > paramend)

To get an expression for overlapping it is negated like,

not ((dbend < paramstart) or (dbstart > paramend))

Using deMorgan's theorem, it equals,

not (dbend < paramstart) and not (dbstart > paramend))

Finally, by flipping the expressions both not's disappear,

(dbend >= paramstart) and (dbstart <= paramend))

That is probably the optimal way to check for overlapping intervals.

[dgtulsaguy]

This is my motivation for the reduction of the first set of parentheses in #2:

Say you have two intervals [dbstart .. dbend] and [paramstart .. paramend] and want to know if they overlap.

You can of course check if any of the four overlapping cases apply, but you can also start with the two non-overlapping cases, like

(dbend < paramstart) or (dbstart > paramend)

To get an expression for overlapping it is negated like,

not ((dbend < paramstart) or (dbstart > paramend))

Using deMorgan's theorem, it equals,

not (dbend < paramstart) and not (dbstart > paramend))

Finally, by flipping the expressions both not's disappear,

(dbend >= paramstart) and (dbstart <= paramend))

That is probably the optimal way to check for overlapping intervals.

Thank you for taking the time to answer this question! This did the trick once I factored the open dbend. I was also able to learn a little about deMorgan's theorem.