StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The CHECK is being executed <strong>after</strong> the row has been inserted, so the range overlaps with itself.</p> <p>You'll need to amend your WHERE to include something like: <code>@MyTableId <> MyTableId</code>.</p> <hr> <p>BTW, your WHERE expression can be simplified.</p> <p>Ranges <em>don't</em> overlap if:</p> <ul> <li>end of the one range is before the start of the other</li> <li><strong>or</strong> start of the one range is after the end of the other.</li> </ul> <p>Which could be written in SQL like:</p> <pre><code>WHERE @DateEnd < DateStart OR DateEnd < @DateStart </code></pre> <p>Negate that to get the ranges that <strong>do</strong> overlap...</p> <pre><code>WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart) </code></pre> <p>...which according to <a href="http://en.wikipedia.org/wiki/De_Morgan%27s_laws" rel="noreferrer">De Morgan's laws</a> is the same as...</p> <pre><code>WHERE (NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)) </code></pre> <p>...which is the same as:</p> <pre><code>WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart </code></pre> <p>So your final WHERE should be:</p> <pre><code>WHERE @MyTableId <> MyTableId AND @DateEnd >= DateStart AND DateEnd >= @DateStart </code></pre> <p><a href="http://sqlfiddle.com/#!3/befe0/1" rel="noreferrer">[SQL Fiddle]</a></p> <p><em>NOTE: to allow ranges to "touch", use <code><=</code> in the starting expression, which would produce '>' in the final expression.</em></p>

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