StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h2><strong>EDIT:</strong> See here for a MySQL working SQL Fiddle: <a href="http://sqlfiddle.com/#!2/2b3c2/47" rel="nofollow">SQLFiddle</a>, that actually works properly this time :-)</h2> <p>Try these. The bottom line is that if <code>Shortfall > 0</code> then you can't book the rental.</p> <p><strong>MSSQL</strong> - this is how I worked it out</p> <pre><code>DECLARE @start DATETIME = '2013-04-29' -- this will depend on your dateformat DECLARE @end DATETIME = '2013-05-10' DECLARE @days INT = DATEDIFF(D,@start, @end) -- this is how many days we actually want to stay DECLARE @unusedDays INT = 0 -- this will be the number of unused days from the rental periods in which our start and end dates fall SELECT @UnusedDays = DATEDIFF(D,@end,end_at) FROM PERIODS WHERE (@end > start_at AND @end <= end_at) -- how many spare days are there in the final period? SELECT @UnusedDays = @UnusedDays + DATEDIFF(D,start_at, @start) FROM PERIODS WHERE (@start >= start_at AND @start < end_at) -- how many spare days are there in the start period? SELECT @days + @UnusedDays - SUM(DATEDIFF(D,start_at,end_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent SUM(DATEDIFF(D,start_at,end_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period @days AS DesiredDays, -- number of days we want to rent @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period) FROM PERIODS WHERE (@start >= start_at AND @start < end_at) -- period in which our selected rental starts OR (end_at < @end AND start_at > @start) -- period completely within our selected rental OR (@end > start_at AND @end <= end_at) -- period in which our selected rental ends </code></pre> <p>This provides output like this:</p> <pre><code>-- if you have @start = '2013-05-05' -- and @end = '2013-05-13' -- then you get Shortfall AvailableDays DesiredDays WastedDays 0---------10------------8-----------2--------- -- if you have @start = '2013-04-29' -- and @end = '2013-05-10' -- then you get Shortfall AvailableDays DesiredDays WastedDays 5---------6-------------11----------0--------- </code></pre> <p><strong>MySQL</strong> - this is what you actually want</p> <pre><code>SET @start = '2013-04-29'; SET @end = '2013-05-10'; SET @days = DATEDIFF(@end, @start); -- this is how many days we actually want to stay SET @UnusedDays = 0; -- this will be the number of unused days from the rental periods in which our start and end dates fall SELECT @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at); -- how many spare days are there in the final period? SELECT 'hello'; SELECT @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at); -- how many spare days are there in the start period? SELECT 'hello'; SELECT @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall, -- total shortfall in days. Zero if we are okay to rent SUM(DATEDIFF(end_at, start_at)) AS AvailableDays, -- total number of days available in all periods covering our chosen rental period @days AS DesiredDays, -- number of days we want to rent @UnusedDays AS WastedDays -- number of wasted days (if we start or end our rental mid-period) FROM PERIODS WHERE (@start >= start_at AND @start < end_at) -- period in which our selected rental starts OR (end_at < @end AND start_at > @start) -- period completely within our selected rental OR (@end > start_at AND @end <= end_at); -- period in which our selected rental ends </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