Complex SQL Date Range Query

[Jkash]

Hi Guys,

I am working on a booking system in Sql Server Express the query is quite complex. Say the date range searched is from 01/12/06 to the 08/12/06.

The query must show loans that start or end within the search range and also loans that extend through that period for example a loan starts on 23/11/06 to 23/12/06.

I can’t figure it out this is what I have so far:


SELECT booking.bookingID, booking.customerID, CONVERT(varchar, booking.startDate, 103) AS startD, CONVERT(varchar, booking.returnDate, 103) AS returnD,
vehicle.registration
FROM booking INNER JOIN
vehicle ON booking.vehicleID = vehicle.vehicleID
WHERE ( (booking.startDate <= @startDate) AND (booking.returnDate <= @returnDate)) OR
((booking.startDate <= @startDate) AND (booking.returnDate <= @returnDate))OR
( (booking.startDate <= @returnDate) AND (booking.returnDate >= @returnDate)) OR
( (booking.startDate <= @startDate) AND (booking.returnDate >= @returnDate))

Thanks in advance for your help.

 

[mrdenny]

If your where looks something like this it should work (this isn't tested). I'm assuming that @StartDate and @returnDate are the two dates you are searching via.

where booking.startDate between @startDate and @returnDate /*Start Date is within the range*/
or booking.returnDate between @startDate and @returnDate /*Return Date is within the range*/
or (booking.startDate >= @StartDate and booking.returnDate <= @returnDate) /*Covers everything that starts before the range and ends after the range.*/

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Jkash]

Thank you Mr Denny that worked a treat

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com