我有一个房产租赁网站
所以我想知道这套房子是否在一段时间内可用
首先,我将日期范围定义为:
DECLARE @AvailableRentalStartingDate DATETIME = '2022-04-11'
, @AvailableRentalEndingDate DATETIME = '2022-04-24'
现在房产租金如下:
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')
如你所见,我们有2022-04-15
天的空房
日期表
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
然后 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]
问题是它没有在可用日期2022-04-15
上标识空值,它只是返回不可用的日期.
我只是想知道这propertyId
个是否可用,在这种情况下,它应该是可用的,因为2022-04-15
是可用的.我怎样才能只显示一行可用的true?当做