StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is a way to flatten date range like this</p> <pre><code>Start | End 2009-1-1 06:00 | 2009-1-1 18:00 2009-2-1 20:00 | 2009-2-2 04:00 2009-2-2 06:00 | 2009-2-2 14:00 </code></pre> <p>You have to compare <em>previous</em> <strong>and</strong> <em>next</em> dates in each row and see whether</p> <ul> <li>Current row's <strong>Start</strong> date falls between previous row's date range. </li> <li>Current row's <strong>End</strong> date falls between next row's date range.</li> </ul> <p><img src="https://farm4.static.flickr.com/3501/3468942647_172669e3a6_o.png" alt="alt text"></p> <p>Using above code, implementing UDF is as simple as followed.</p> <pre><code>create function fnThereIsWatchmenBetween(@from datetime, @to datetime) returns bit as begin declare @_Result bit declare @FlattenedDateRange table ( Start datetime, [End] datetime ) insert @FlattenedDateRange(Start, [End]) select distinct Start = case when Pv.Start is null then Curr.Start when Curr.Start between Pv.Start and Pv.[End] then Pv.Start else Curr.Start end, [End] = case when Curr.[End] between Nx.Start and Nx.[End] then Nx.[End] else Curr.[End] end from shift Curr left join shift Pv on Pv.ID = Curr.ID - 1 --; prev left join shift Nx on Nx.ID = Curr.ID + 1 --; next if exists( select 1 from FlattenedDateRange R where @from between R.Start and R.[End] and @to between R.Start and R.[End]) begin set @_Result = 1 --; There is/are watchman/men during specified date range end else begin set @_Result = 0 --; There is NO watchman end return @_Result end </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