Help needed in SQL coding !!

[Angelina]

Hi,

I have written the query below which selects all
equipment that has been booked within a specific date
range. This part of the query is working fine.
The Start_date and End_date of the rental is specified by
the user at the interface.

SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No
WHERE (dbo.Equipment_Booking.Rental_Start_date BETWEEN
@Start_Date AND @End_Date) OR
(dbo.Equipment_Booking.Rental_End_Date BETWEEN
@Start_Date AND @End_Date) OR
(@Start_Date BETWEEN
dbo.Equipment_Booking.Rental_Start_Date AND
dbo.Equipment_Booking.Rental_End_Date) OR
(@End_Date BETWEEN
dbo.Equipment_Booking.Rental_Start_Date AND
dbo.Equipment_Booking.Rental_End_date)

What i want is a query that displays a list of all
equipment inventory numbers (Equip_Inv_No) that are
available for the specified date range that the user
specifies.
The query above only shows me those that have been booked
for that date range. Can anyone help me or guide me in
writing this new query. I have tried to use a subquery
with a NOT IN statemnet but i keep getting the following
error message:

ADO error: Parameter Information cannot be derived from
SQL statements with sub-select queries. Set parameter
information before preparing command.

Can anyone plz help??

 

[Gary Walter]

Hi,

I have written the query below which selects all
equipment that has been booked within a specific date
range. This part of the query is working fine.
The Start_date and End_date of the rental is specified by
the user at the interface.

SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No
WHERE (dbo.Equipment_Booking.Rental_Start_date BETWEEN
@Start_Date AND @End_Date) OR
(dbo.Equipment_Booking.Rental_End_Date BETWEEN
@Start_Date AND @End_Date) OR
(@Start_Date BETWEEN
dbo.Equipment_Booking.Rental_Start_Date AND
dbo.Equipment_Booking.Rental_End_Date) OR
(@End_Date BETWEEN
dbo.Equipment_Booking.Rental_Start_Date AND
dbo.Equipment_Booking.Rental_End_date)

What i want is a query that displays a list of all
equipment inventory numbers (Equip_Inv_No) that are
available for the specified date range that the user
specifies.
The query above only shows me those that have been booked
for that date range. Can anyone help me or guide me in
writing this new query.

Hi Angelina,

Whenever I have to deal with a date range "overlap"
problem, I always go back to this simple but elegant
answer Michel once gave on this newsgroup:

** quote **
Ranges do NOT overlap if
( I assume aStart, aEnd, bStart and bEnd are the intervals):

aStart > bEnd OR aEnd < bStart

they overlap, in part or in full, on the negation of that statement, ie
(Apply De Morgan's law) :

aStart <= bEnd AND aEnd >= bStart

** end quote **

This has always worked well for me in
my "US dates/Access" world. I am not sure
what the gotchas might be in your situation,
but I might try substituting "Rental" dates
for "a" dates, and "@" dates for "b" dates
in the "do NOT overlap" statement for your
WHERE clause, then go from there.

Please respond back if I have misundertood.

Good luck,

Gary Walter

 

[Angelina]

Hi Gary,

thx for the info. This is a much more efficient way to
code what i wanted to achieve. )

the problem im now having is that i cant get my nested
query to work.

i.e i have managed to select all the pieces of equipment
that are booked between a specifed date range, but this
is not what i want.
I want the pieces of equipment that are available.
I have tried to achieve this by using a NOT IN query but
i cannot get it to work.
this is what i have tried...

SELECT COUNT(dbo.Equipment_Inv.Equip_Inv_No)
As Number_available
FROM dbo.Equipment_Inv
WHERE dbo.Equipment_Inv_No NOT IN
(
SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No
WHERE (dbo.Equipment_Booking.Rental_Start_Date <=
@End_Date)
AND (dbo.Equipment_Booking.Rental_End_Date >=
@Start_Date)

The bottom half of this query works fine, but when i
added the top part i keep getting an error message
stating:

ADO error: PArameter Information cannot be derived from
SQL statements with sub-select queries. Set Parameter
information before preparing command.


Does anyone know how i can resolve this?
Thx for all your help.

 

[Gary Walter]

Sorry Angelina,

I thought you asked:

"What i want is a query that displays a list of all
equipment inventory numbers (Equip_Inv_No) that are
available for the specified date range that the user
specifies."

So assumed you wanted something like:

SELECT dbo.Equipment_Inv.Equip_Inv_No,
COUNT(dbo.Equipment_Inv.Equip_Inv_No)
As Number_available
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No
WHERE
(dbo.Equipment_Booking.Rental_Start_Date >
@End_Date)
AND
(dbo.Equipment_Booking.Rental_End_Date <
@Start_Date)
GROUP BY dbo.Equipment_Inv.Equip_Inv_No;

 

[Gary Walter]

Whoops.. that should be ** OR **

SELECT dbo.Equipment_Inv.Equip_Inv_No,
COUNT(dbo.Equipment_Inv.Equip_Inv_No)
As Number_available
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No
WHERE
(dbo.Equipment_Booking.Rental_Start_Date >
@End_Date)
OR
(dbo.Equipment_Booking.Rental_End_Date <
@Start_Date)
GROUP BY dbo.Equipment_Inv.Equip_Inv_No;

 

[Gary Walter]

Hi Angelina,

So you want all equip in booking
table that is not booked
thru a range of dates
and
all equip in Inv that is not in
booking table at all.


SELECT dbo.Equipment_Inv.Equip_Inv_No,
COUNT(dbo.Equipment_Inv.Equip_Inv_No)
As Number_available
FROM dbo.Equipment_Inv LEFT JOIN
dbo.Equipment_Booking ON
dbo.Equipment_Inv.Equip_Inv_No =
dbo.Equipment_Booking.Equip_Inv_No
WHERE
((dbo.Equipment_Booking.Rental_Start_Date >
@End_Date)
OR
(dbo.Equipment_Booking.Rental_End_Date <
@Start_Date))
OR (dbo.Equipment_Booking.Equip_Inv_No IS NULL)
GROUP BY dbo.Equipment_Inv.Equip_Inv_No;