CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2021
    Posts
    2

    Intersecting number ranges

    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.

  2. #2
    Join Date
    Feb 2017
    Posts
    630

    Re: Intersecting number ranges

    Quote Originally Posted by dgtulsaguy View Post
    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:
    Code:
    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,

    Code:
    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,

    Code:
    dbend >= 19010101 and (dbend >= paramstart and dbstart <= paramend) or
    dbend = 0  and        (dbstart <= paramend)
    and then finally (dbstart <= paramend) can be broken out to,

    Code:
    (dbstart <= paramend) and (dbend >= 19010101 and dbend >= paramstart or dbend = 0)
    This obviously has to be checked and tested carefully.
    Last edited by wolle; August 28th, 2021 at 12:36 PM.

  3. #3
    Join Date
    Feb 2017
    Posts
    630

    Re: Intersecting number ranges

    Quote Originally Posted by wolle View Post
    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.
    Last edited by wolle; August 30th, 2021 at 06:05 AM.

  4. #4
    Join Date
    Aug 2021
    Posts
    2

    Re: Intersecting number ranges

    Quote Originally Posted by wolle View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured