StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied! <p>This is a classical problem, and it's actually easier if you reverse the logic.</p> <p>Let me give you an example.</p> <p>I'll post one period of time here, and all the different variations of other periods that overlap in some way.</p> <pre class="lang-none prettyprint-override"><code> |-------------------| compare to this one |---------| contained within |----------| contained within, equal start |-----------| contained within, equal end |-------------------| contained within, equal start+end |------------| not fully contained, overlaps start |---------------| not fully contained, overlaps end |-------------------------| overlaps start, bigger |-----------------------| overlaps end, bigger |------------------------------| overlaps entire period </code></pre> <p>on the other hand, let me post all those that doesn't overlap:</p> <pre class="lang-none prettyprint-override"><code> |-------------------| compare to this one |---| ends before |---| starts after </code></pre> <p>So if you simple reduce the comparison to:</p> <pre class="lang-none prettyprint-override"><code>starts after end ends before start </code></pre> <p>then you'll find all those that doesn't overlap, and then you'll find all the non-matching periods.</p> <p>For your final NOT IN LIST example, you can see that it matches those two rules.</p> <p>You will need to decide wether the following periods are IN or OUTSIDE your ranges:</p> <pre class="lang-none prettyprint-override"><code> |-------------| |-------| equal end with start of comparison period |-----| equal start with end of comparison period </code></pre> <p>If your table has columns called range_end and range_start, here's some simple SQL to retrieve all the matching rows:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM periods WHERE NOT (range_start > @check_period_end OR range_end < @check_period_start) </code></pre> <p>Note the <em>NOT</em> in there. Since the two simple rules finds all the <em>non-matching</em> rows, a simple NOT will reverse it to say: <em>if it's not one of the non-matching rows, it has to be one of the matching ones</em>.</p> <p>Applying simple reversal logic here to get rid of the NOT and you'll end up with:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM periods WHERE range_start <= @check_period_end AND range_end >= @check_period_start </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload