Doing a join that returns dates that do NOT exist in table

Transact-SQL
1

I have a selection of courtesy cars and their bookings. The table schema is like this:
I am using SQL select statements to show the schema:

SELECT TOP (1000) [CourtesyCarBookingId]
,[startDate]
,[endDate]
,[CourtesyCarId_Fk] s
,[BookingId_Fk]
FROM [AATBookings].[dbo].[courtesyCarBookings]

And for courtesy cars:

SELECT TOP (1000) [CourtesyCarId]
,[Vrm]
,[MakeModel]
,[BodyShape]
,[Mpg]
,[DoorNo]
,[Transmission]
FROM [AATBookings].[dbo].[CourtesyCarModels]

A courtesy car booking points to a courtesy car model via ID.

If I provide a start and end date, I need to get a list of the models for which there is NO booking. So essentially the booking table has NO rows or entries for that date.

Is this possible? Do I need a table to store all dates?

2

one way
SELECT CourtesyCarId FROM [AATBookings].[dbo].[CourtesyCarModels]
EXCEPT
SELECT CourtesyCarBookingId FROM [AATBookings].[dbo].[courtesyCarBookings]

another way
SELECT CourtesyCarId FROM [AATBookings].[dbo].[CourtesyCarModels]
WHERE CourtesyCarId NOT IN
( SELECT CourtesyCarBookingId FROM [AATBookings].[dbo].[courtesyCarBookings] )

3

With this, how can I check if the car is free on certain dates?

So for example, I will add a clause to say WHERE courtesy car bookings DOES NOT contain a value of a start and end date.

4

Yes .. !!!

5

Having a calendar table is useful for a lot of things. eg

https://www.sqlshack.com/designing-a-calendar-table/

You could also do this one on the fly with an inline tally/number table (google) and the MIN(startDate) and MAX(endDate) from [AATBookings].[dbo].[courtesyCarBookings] to use dateadd to get all the dates between MIN(startDate) and MAX(endDate).

1 Like
6

A rather complex task. See if this query provides what you need, or almost there. I had no data to test with.

DECLARE @start_date date
DECLARE @end_date date

SET @start_date = '20200101'
SET @end_date = '20200831'

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
),
cte_dates AS (
    SELECT DATEADD(DAY, t.number, @start_date) AS bookDate 
    FROM cte_tally10K t
    WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
)
SELECT d.bookDate, AllModels.MakeModel
FROM cte_dates d
CROSS JOIN (
    SELECT DISTINCT MakeModel
    FROM AATBookings.dbo.courtesyCarBookings
) AS AllModels
LEFT OUTER JOIN (
    SELECT DISTINCT ccb.bookDate, ccm.MakeModel
    FROM AATBookings.dbo.courtesyCarBookings ccb
    INNER JOIN cte_tally10K ON t.number BETWEEN 0 AND DATEDIFF(DAY, startDate, endDate) 
    CROSS APPLY (
        SELECT DATEADD(DAY, t.number, ccb.startDate) AS bookDate
    ) AS calcSpecificBookDate
    INNER JOIN AATBookings.dbo.CourtesyCarModels ccm ON
        ccb.CourtesyCarId_Fk = ccm.CourtesyCarId
    WHERE
        ccb.startDate < @end_date AND ccb.endDate >= @start_date AND 
        bookDate >= @start_date AND bookDate <= @end_date
) AS MakeModelBookDates ON MakeModelBookDates.bookDate = d.bookDate AND MakeModelBookDates.MakeModel = AllModels.MakeModel
WHERE MakeModelBookDates.MakeModel IS NULL
1 Like
7

Maybe one way of doing this is to have a calendar table and then have a join table? each booking can have multiple dates and a date can belong to multiple bookings (as each booking has a unique car).

Would that be a good design?

8

How about posting some consumable test data and the expected results?
There should not be too much data but enough to show any variation you can envisage.