Search code examples
sqlsql-servert-sqldatetimeinventory-management

SQL query to get free vehicles on basis of their booking date

Here is my Inventory Table,

Inventory Table

And this one is my Engaged Inventory Log Table,

EngagedInventoryLog

and there is also a Main Booking Table with different fields with detailed information of a particular booking.

Once a row created in Main Booking Table , trigger for insertion is there to insert in the EngagedInventoryLog Table with their respective PickupDate, DropDateand the RegistrationNo of the Vehicle Booked from the Main Booking Table.

Now , If am going to do a booking with PickupDate = 15/09/2019 10:00:00 and DropDate = 16/09/2019 16:00:00 then only those vehicles should be available which are free or not booked or in other words not clash with the Records in EngagedInventoryLog, PickupDate and DropDate Column. (i.e RegisNo 2345)

Similarly in case of: 10/09/2019 10:00:00 to 13/09/2019 10:00:00 we have to get both vehicles with RegisNo i.e 1234 , 2345

In Case of 10/09/2019 10:00:00 to 17/09/2019 10:00:00 No Vehicles should be there.

And Here is the query which i have tried but it doesn't works on some cases,

SELECT DISTINCT Id, VehicleName + '#' + ISNULL(RegisNo, '') AS 'Model'
FROM            Inventory
WHERE SupplierName = 'John'
AND
RegisNo NOT IN (
SELECT RegisNo FROM EngagedInventoryLog
WHERE
CONVERT(DATETIME,'15/09/2019 10:00:00',103) <  DropDate
    AND convert(DATETIME,'16/09/2019 16:00:00',103) > PickupDate

)

Solution

  • I would use a correlated subquery with a NOT EXITS condition to pull out the vehicules for which no engagement exists that overlaps the target range:

    SELECT *
FROM Inventory i
WHERE 
    SupplierName = 'John'
    AND NOT EXISTS (
        SELECT 1
        FROM EngagedInventory e
        WHERE 
            e.RegisNo = i.RegisNo
            AND @PickupDate <= e.DropDate 
            AND @DropDate >= e.PickupDate
    )

    You can replace @PickupDate and @DropDate with the date range for which you want to create the new engagement.

    In this demo on DB Fiddle with your sample data, when given pickup date '2019-09-15 10:00:00' and drop date '2019-09-16 10:00:00', the query returns:

    id | SupplierName | VehiculeName | RegisNo
-: | :----------- | :----------- | ------:
 2 | John         | Creta        |    2345