0

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) enter image description here

  • 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
)
1
  • 2
    you should post a create table with data, but i am missing wanted enddate, what the use of carid 4 when it is available, but is already booked
    – nbk
    Dec 7, 2020 at 17:58

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.