Vehicle availability query

1

Hi everyone, hope you all are doing ok. I am a relatively new to SQL and I wanted some assistance with a simple query to find all vehicles available for future bookings i.e. based on the dates and times the user selects. I have 2 tables, one for user details and the other are just for all vehicles with their updated dates and times from the user table. Any assistance is greatly appreciated.

2

Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

2 Likes
3

Ref my original question, this is the structure of my 2 tables. If my table structure.dat types are incorrect structure, please let me know, thanks

CREATE TABLE [dbo].[fleet] (
[vehicle] VARCHAR (50) NULL,
[bgdate] DATE NULL,
[enddate] DATE NULL,
[strtm] TIME (7) NULL,
[endtm] TIME (7) NULL
);

CREATE TABLE [dbo].[reservations] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[fn] VARCHAR (255) NULL,
[ln] VARCHAR (255) NULL,
[phone] VARCHAR (255) NULL,
[email] VARCHAR (255) NULL,
[vehicle] VARCHAR (50) NULL,
[bgdate] DATE NULL,
[enddate] DATE NULL,
[strtm] TIME (7) NULL,
[endtm] TIME (7) NULL,
[nodays] VARCHAR (50) NULL,
[nodhrs] VARCHAR (50) NULL,
[revstatus] VARCHAR (50) NULL
);

4

DDL is a start. Where is the test data and the expected results for the test data?

1 Like
5

SELECT * FROM fleet WHERE NOT EXISTS(SELECT 1 FROM reservations WHERE fleet.bgdate BETWEEN '" + begdate + "' AND '" + endate + "' AND fleet.strtm BETWEEN '" + begtime + "' AND '" + endtime + "'

6

image
image882×284 65.2 KB

8

Hi

Hope this helps

DECLARE @StartDate DATETIME = '2023-10-01 08:00:00';
DECLARE @EndDate DATETIME = '2023-10-10 20:00:00';

SELECT v.VehicleID, v.VehicleName
FROM Vehicles v
LEFT JOIN Bookings b ON v.VehicleID = b.VehicleID 
    AND b.BookingStart < @EndDate 
    AND b.BookingEnd > @StartDate
WHERE b.VehicleID IS NULL;