Query to get current column based on date range

Question

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
)

Answer 1

Assuming that tVehicle.BranchId contains Branch where the vehicle is currently at and branchId of branch B is 2 this should do the trick

DECLARE @branchId int= 2;
DECLARE @availableDate date= '2020-12-09';

SELECT tv.*
FROM tVehicle tv
WHERE tv.BranchId=@branch
AND NOT EXISTS(SELECT 1 FROM tBooking tb WHERE tb.VehicleId=tv.VehicleId AND @availableDate BETWEEN tb.StartDate AND tb.EndDate);