Get available dates between range of dates

Question

I have a property rental site

So I want to know if property is available for a range of dates

First of all I defined the range of dates as:

DECLARE @AvailableRentalStartingDate DATETIME = '2022-04-11'
, @AvailableRentalEndingDate DATETIME = '2022-04-24'

Now the property rent like:

 DECLARE @Rentals AS TABLE
                                (
                                    [PropertyId]   UNIQUEIDENTIFIER,
                                    [StartingDate] DATE,
                                    [EndingDate]   DATE
                                )
            INSERT INTO @Rentals ([PropertyId], [StartingDate], [EndingDate]) 
    VALUES ('A5B2B505-EC6F-EC11-A004-00155E014807','2022-04-11 16:47:20.897', '2022-04-14 16:47:20.897'), 
           ('A5B2B505-EC6F-EC11-A004-00155E014807','2022-04-16 16:47:20.897','2022-04-21 16:47:20.897')

As you can see we have available date 2022-04-15

Dates table

        DECLARE @Dates AS TABLE
                          (
                              DateName DATE
                          )
        DECLARE @TotalDays INT = 365;

        WHILE @TotalDays > 0
            BEGIN
                INSERT INTO @Dates ([DateName])
                SELECT DATEADD(DAY, @TotalDays, '2021-12-31');
                SELECT @TotalDays = @TotalDays - 1;
            END

Then the select

   SELECT [R].[PropertyId], [D].[DateName], CASE WHEN [R].[StartingDate] IS  NULL THEN 1 ELSE 0 END AS [IsAvailable]
    FROM @Dates AS D
             LEFT JOIN @Rentals R ON  [D].[DateName] >= [R].[StartingDate] AND [D].[DateName] <= [R].[EndingDate]
    WHERE [D].[DateName] BETWEEN @AvailableRentalStartingDate AND @AvailableRentalEndingDate
    AND [R].[PropertyId] = 'A5B2B505-EC6F-EC11-A004-00155E014807'
    ORDER BY [D].[DateName]

The problem is it does not identify the null on the available date 2022-04-15, it just return the not available dates.

I just want to know if that propertyId it's available, in this case it should be available because 2022-04-15 is available. How can I get only one row showing available true? Regards

user_1123226 · Answer

your problem is the where clause of propertyid; you should have that as a JOIN condition. WHERE clause and ON conditions can be interchangeably used in INNER JOIN but in OUTER JOIN they impact the meaning.

demo link

  SELECT [R].[PropertyId], [D].[DateName], CASE WHEN [R].[StartingDate] IS  NULL THEN 1 ELSE 0 END AS [IsAvailable]
    FROM @Dates AS D
             LEFT JOIN @Rentals R ON  [D].[DateName] >= [R].[StartingDate] AND [D].[DateName] <= [R].[EndingDate]
AND [R].[PropertyId] = 'A5B2B505-EC6F-EC11-A004-00155E014807'
    WHERE [D].[DateName] BETWEEN @AvailableRentalStartingDate AND @AvailableRentalEndingDate
    
    ORDER BY [D].[DateName]

Get available dates between range of dates

1 Answers