Unanswered: Search a date range using a date range in sql server 2005
I have a query where i need to search certains dates to see if specific equipment is available for those dates. Problem is that i need to do this as a date range (both for the dates to find & the dates to find against).
EG. I have a table which hold equipment booking & has a field called 'required Date' & 'Estimated return Date'. This is what the user specifies in order to book out a piece of equipment.
If i had laptop1booked out with a required date of 1/1/2008 & Estimated return date of 5/1/2008 then this laptop should not be available for any new bookins between the 1/1/2008 to 5/1/2008.
So my equipment booking system needs to search to see if any new Rrequired date - new Estimated return date range falls into the existing date range & exclude this equipment.
EG We have stated that Laptop1 is booked between 1/1/2008 & 5/1/2008 so then if i entered a new booking record with the Required date as 6/1/2008 & estimated return date as 7/1/2008 then Laptop1 should be available from my list
BUT if i enetered a new booking record with the Required Date as 3/1/2008 & estimated date as 9/1/2008 then Laptop1 should NOT be avaialble from the equipment list as it is booked out between 1/1/2008 & 5/1/2008.
How do i represent this as a SQL query? (Using a date range to filter a stored date range?)
All dates in the example are UK date format (dd/mm/yyyy)
Please let me know if you have any thoughts on how to do this, as i am stumped!