Query to get current column based on date range 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 &gt;= @requestedDate
    AND EndDate &lt;= @requestedDate
)
AND BranchId IN (
    SELECT BranchId FROM tBooking WHERE EndBranchId = @branchId
    -- I dont know how to write correct logic for the dates
    AND StartDate &gt;= @requestedDate   
    AND EndDate &lt;= @requestedDate
)
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);