[thelist] SQL Dates Problem

[Ken Schaefer]

--
www.adOpenStatic.com/cs/blogs/ken/ 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Burhan Khalid
: Subject: Re: [thelist] SQL Dates Problem
: 
: > :    I'm also using a similar rudimentary approach to
: > : finding out of a new event conflicts with any event
: > : in a hall.  
: > So
: >
: > SELECT (whatever)
: > FROM Events
: > WHERE (newEventStartDate) < EventStartDate
: > AND (newEventEndDate) > EventStartDate
: >
: > would return any conflicting, existing events. That query will work in
: > mySQL > (at least something does...) :-)
: 
: I never thought about going about it this way. Atleast its something to
: try.

I've written a few booking systems - it works :-)


: For the other problem of finding available dates, after a few whiteboard
: sessions -- the idea came up to find the difference between the end and
: start dates of an event -- if this difference is equal to one day, then
: there is a possible free date.  If I wanted to find 3 free dates, then I
: could just do some math and mod the resulting difference.  Can anyone
: see any problems with this approach?

This is how I would do it. You may need to change DB engines though :-)

SELECT
	(whatever)
FROM 
	Events AS a
WHERE
	DATEDIFF(
		Day, a.EndDate, 
		SELECT TOP 1 b.StartDate
		FROM Events AS b
		WHERE b.StartDate > a.EndDate
		ORDER by b.StartDate ASC
	) > 1
AND
	a.EndDate > GetDate()   -- only look at events after today
AND
	a.EventHallID = b.EventHallID -- make sure it's the same hall
ORDER BY
	a.EndDate

This isn't tested, but basically you are comparing the EndDate for a given
event with the StartDate of the next event, and see if the difference in
dates is > 1. That would indicate a free intermediate date. Ordering the
result set by a.EndDate would give you the first ending date after which
there's a free date.

The above is untested - you may need to tweak it a bit.


: As if this wasn't causing me enough problems -- there is a new
: requirement that an event can span more than one hall.  So if there is
: an event that is going to be two weeks long and takes place in halls
: 5,6,7 -- I need to find the earliest date where all three halls have 14
: days free.  So now I'm back to square one.

I think this can be added to the above.

You'll need to do the test for all three halls (using an AND) and you'll need
to test to ensure that a.EndDate (end of an event for one hall) and
b.StartDate (start of the next event for the given hall) happens to be within
or greater than the span for the other two tests.

Cheers
Ken