I have this stored procedure:
create procedure [dbo].[GetCarsByDates]
(@startDate date, @returnDate date)
as
SELECT
ManufacturerName, ModelName,
CreationYear,
Gear, CurrentKM,
Picture,
DATEDIFF(D, @startDate, @returnDate) * PricePerDay AS [Totalprice],
PricePerDay, PricePerDayDelayed,
InventoryCars.LicensePlate
FROM
Models
JOIN
Manufacturers ON Models.ManufacturerID = Manufacturers.ManufacturerID
JOIN
InventoryCars ON InventoryCars.ModelID = Models.ModelID
JOIN
CarsForRent ON CarsForRent.LicensePlate = InventoryCars.LicensePlate
WHERE
CarsForRent.RentalStartDate < @startDate
AND CarsForRent.RentalReturnDate < @returnDate
AND CarsForRent.RentalReturnDate < @startDate
ORDER BY
ManufacturerName, ModelName
I want to be able to select the properties by start and return dates. User inputs start date must be greater than return date and that is exactly what I did, but it still not working properly.
The problem is that I get rows results of items that are not available.
What is the problem with my where clause?
I feel your query should be written like below. I assumed that you need to query all cars available from startDate to returnDate and need to check based on CarsForRent table's columns CarsForRent.RentalStartDate and CarsForRent.RentalReturnDate
create procedure [dbo].[GetCarsByDates]@startDate date, @returnDate date
as
BEGIN
select DISTINCT ManufacturerName, ModelName,
CreationYear,Gear, CurrentKM,
Picture,
DATEDIFF(D, @startDate, @returnDate)*PricePerDay as[Totalprice],
PricePerDay,PricePerDayDelayed, InventoryCars.LicensePlate
from Models
join Manufacturers on Models.ManufacturerID=Manufacturers.ManufacturerID
join InventoryCars on InventoryCars.ModelID=Models.ModelID
join CarsForRent on CarsForRent.LicensePlate=InventoryCars.LicensePlate
where
@startDate > CarsForRent.RentalReturnDate AND
CarsForRent.RentalReturnDate >CarsForRent.RentalStartDate
AND @startDate<=@returnDate
order by ManufacturerName, ModelName
END
if you do not need to check that returndate is >startdate remove this line from where clause :
AND @startDate<=@returnDate
I've created a sample fiddle Please add values to it in left side and play with the use cases http://sqlfiddle.com/#!6/00236/1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments