SQL Comparing user supplied date ranges to a table with date ranges

I have a table with date ranges representing reservations with pickup date and dropp-off date.
Users need to supply a desired start and end date and get a list of available entities that are not already reserved - whats the most efficient way to do this in a stored procedure - to compare date range overlap based on some input params.  

If I was using VB ide make load the ranges into an array and loop through to build a list of available entities that A) have no reservations whatsoever and are thus available and  B) have reservations that have no overlap with the date range supplied by the user and are thus also available.

Ive seen the BETWEEN statement but cant figure out how to implement this in a stored procedure
bcombeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bobaran98Commented:
I've not generally used BETWEEN-- not sure if it's more efficient.  I would simply do something like the below code.
Then you would just execute your code in VB.NET using a SQL string such as "EXEC MyRsvpChecker '5/21/2010', '5/30/2010' "
Is that what you're looking for?

CREATE PROCEDURE [dbo].[MyRsvpChecker]

@StartDate datetime,
@StopDate datetime

AS 

SET NOCOUNT ON

SELECT *
FROM RSVPs
WHERE @StopDate < RSVPs.PickupDate
	OR @StartDate > RSVPs.DropoffDate

Open in new window

0
Bobaran98Commented:
Wait... clarification question:  Do you actually have two db tables?  One a list of entities to be reserved, and one a list of reservations?  With the possibility of having multiple (or no) entries in the second table for each entity in the first table?  That would make sense, since obviously you could (and probably would) be booking multiple reservations for each entity.
It might help if you could show us the profile of these tables.  
0
bchoorCommented:
@Bobaran - it's missing if there are multiple reservations for an entity

E1 '4/12/2010' '4/15/2010'
E1 '4/16/2010' '4/20/2010'
E2 '4/12/2010' '4'13/2010'
E2 '4/18/2010' '4/20/2010'

So
'4/14/2010' to '4/17/2010' should give only
E2

You query will give:
E1
E2
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

bchoorCommented:
It soundsl like its really 3 queries

1. No reservations
2. 1 reservation
3. 2+ reservations

For 1 and 2 would be:
      SELECT
            E.[ENTITY_ID]
      FROM
            [ENTITY] E
      WHERE
            E.[ENTITY_ID] NOT IN (SELECT DISTINCT [ENTITY_ID] FROM [ENTITY_RESERVATIONS])

        UNION ALL

      SELECT
            ER.[ENTITY_ID]
      FROM [ENTITY_RESERVATIONS] ER
      WHERE
            (ER.[PICKUP_DATE] > @END_DATE OR
            ER.[DROPOFF_DATE] < @START_DATE) AND
            (SELECT COUNT(*) FROM [ENTITY_RESERVATIONS] ER2 WHERE ER2.[ENTITY_ID] = ER.[ENTITY_ID]) = 1


0
Bobaran98Commented:
@bchoor -- Good call.  I made some simplistic assumptions with my first answer.
That said, I think we're making it a bit too complicated here.  If we want this to be as efficient as possible, I think the best way would be as follows:
  1. Get a list of all entities that ARE reserved during that date range.
  2. Return a list of all entities that don't show up in that first list.
Just one nested query required, as shown below (I'm using bchoor's naming scheme to prevent confusion).  We could use a DISTINCT in that nested query, but since we're looking for entities NOT IN that list, I'm thinking DISTINCT would just add unnecessary processing.
@bchoor-- do you know if adding a DISTINCT to a query will increase processing time (because it's doing extra filtering) or decrease (because it's returning fewer records)?  Or does it depend on the query?  I've always wondered...

SELECT [ENTITY_ID]
FROM [ENTITY]
WHERE [ENTITY_ID] NOT IN (
	SELECT [ENTITY_ID]
	FROM [ENTITY_RESERVATIONS]
	WHERE (@START_DATE >= [PICKUP_DATE] AND @START_DATE <= [DROPOFF_DATE])
		OR (@END_DATE >= [PICKUP_DATE] AND @END_DATE <= [DROPOFF_DATE])
)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bchoorCommented:
@Bobaran98 - nice and simple. didn't occur to me.

about the DISTINCT, it's usually query dependent, at least in my experience. no reason to have it in this case, unless we have more info on the datasets on which this query will be run.



0
bcombeAuthor Commented:
To answer  Bobaran98: 04/27/10 11:22 AM, ID: 32283463
 Do you actually have two db tables?   One a list of entities to be reserved, and one a list of reservations?

       - - Yes, the entities, BTW are indeed vehicles - so theres an 'vehicle inventory' table and it has both a 'vehicle instance status' table which is to reflect true current vehicle disposition.  As well there is a 'vehicle reservation' table that has all the reservations with DateStart and DateEnd fields.   For completeness a reservation is a projection about vehicle future status. When that date arrives - if acted upon - the reservation will be converted into rental agreement.  At this moment the vehicles status will be modified, by trigger, to reflect that it is checked out and off the lot.  

  With the possibility of having multiple (or no) entries in the second table for each entity in the first table?  
       - - Yes, Not every vehicle is reserved and there will be some available at any given time.  The idea  is that the user supplies some dates and gets a list of vehicles available to reserve.


0
Bobaran98Commented:
Have you tried the solutions we've posted?  Is there any other assistance we can provide?  Just let us know...
0
bcombeAuthor Commented:
Hi fellas, thanks so much -  I believe I have it working -
Bobaran98 - your code worked great if I supplied:
res request:  '4/14/2010' to '4/17/2010'
and in the system we already (only) had reservations :

E2 '4/12/2010' '4'13/2010'
E1 '4/16/2010' '4/20/2010'

We get E2 as available. But to accomodate for the for:
res request:  '4/14/2010' to '4/21/2010'
We get E2 and E1 - to fix this I added one more OR part:
OR (@DateStart <=  TrxReservation_PickupDate  AND @DateEnd >= TrxReservation_ReturnDate)

Heres the whole part I used in the sproc - testing so far *seems* to cover it - Im a tester may come up with something.

Thanks again !!


SELECT     dbo.Vehicle.VehicleID_PK, dbo.Vehicle.VehicleNumber, dbo.Vehicle.VehicleBBClass, dbo.VehicleClass.VehicleClassBase, dbo.VehicleClass.VehicleClass, 
                      dbo.VehicleClass.VehicleClassGroup
FROM         dbo.Vehicle INNER JOIN
                      dbo.VehicleClass ON dbo.Vehicle.VehicleClassID_FK = dbo.VehicleClass.VehicleClassID_PK
WHERE VehicleID_PK 

NOT IN 
(
	SELECT TrxReservation_VehicleID_FK
	FROM dbo.Trx_ReservationSchedule
	WHERE 
	(@DateStart >= [TrxReservation_PickupDate] AND @DateStart <=  [TrxReservation_ReturnDate])
	 
	 OR (@DateEnd >=  TrxReservation_PickupDate  AND @DateEnd <= TrxReservation_ReturnDate)
	 
	 OR (@DateStart <=  TrxReservation_PickupDate  AND @DateEnd >= TrxReservation_ReturnDate)
	 
)

Open in new window

0
bcombeAuthor Commented:
I meant to say "I'm sure a tester may come up with something."
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.