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.
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 )