I have a scenario with the following tables. The issue is when a car is booked in table tBooking it is due to following scenarios.
Example
User books a car in branch (branch is a location of company offices where the cars are kept)
User A books a Car at branch A on date range 4-dec-202- to 6-dec-2020 (tBranch.StartBranchId) and wants to return it to branch B (tBranch.EndBranchId)
User B is searching for cars available on the 7-dec-2020 in a specific location branch B
How do I write a query where I get the next available car by date range and the requested location?
Let's say am searching for available cars at branch B, this is what I have tried so far:
SELECT * FROM tVehicle WHERE VehicleID NOT IN (
SELECT VehicleID FROM tBooking
WHERE StartDate >= @requestedDate
AND EndDate <= @requestedDate
)
AND BranchId IN (
SELECT BranchId FROM tBooking WHERE EndBranchId = @branchId
-- I dont know how to write correct logic for the dates
AND StartDate >= @requestedDate
AND EndDate <= @requestedDate
)