scheduling query 1

[MartDawg]

I'm writing a web page to do some scheduling, I have a table
Table1
id
user
beg_date
end_date

The user specifies a begin date and end date to schedule, I do two queries like the following for each date specified....

SELECT * Table1
WHERE (CONVERT(datetime, userspecified date) BETWEEN beg_date AND end_date)

To make sure the date doesn't clash with one all ready scheduled, if it doesn't, I inser the record....

This works great except for one thing.

If March 16-18 is scheduled, and a user specifies March 13-20, it will not flag it as allready scheduled, and schedule over it.

I need to tweak my query. Is their someway to do it so it checks it by range?

 

[evaleah]

For range checking I usually do it in one query something like the following:

SELECT * FROM Table1
WHERE (CONVERT(datetime, userspecified_begin_date) BETWEEN beg_date AND end_date) OR (CONVERT(datetime, userspecified_end_date) BETWEEN beg_date AND end_date)

This way you are checking both dates being inside a date range.

HTH

Eva

 

[tlbroadbent]

Here is one method of determining availability. Note that you have to check if the user begin or end date falss in an existing date range and then check if existing begin or end date falls in the user specified range. This can be done in one query.

I've created a stored procedure that returns 'Y' or 'N' when two dates are submitted.

Create Procedure spCheckDatesAvailability
 @userbegdate char(10),
 @userenddate char(10),
 @available char(1) Output
As

Set @available = 'Y'

If Exists
 (SELECT * 
  FROM YourTableName
  WHERE @userbegdate BETWEEN Beg_Date AND End_Date
     OR @userenddate BETWEEN Beg_Date AND End_Date
     OR Beg_Date BETWEEN @userbegdate AND @userenddate
     OR End_Date BETWEEN @userbegdate AND @userenddate)

  Set @available = 'N'
go

--Test the procedure
Declare @available char(1)

Exec spCheckDatesAvailability
 @userbegdate = '2004-03-13',
 @userenddate = '2004-03-20',
 @available=@available Output

Print @available

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[MartDawg]

Thanks, this is great!