gpt4 book ai didi

sql - 获取日期范围之间的可用日期

转载 作者:行者123 更新时间:2023-12-05 04:31:23 25 4
gpt4 key购买 nike

<分区>

我有一个特性租赁网站

所以我想知道某个日期范围内的特性是否可用

首先我将日期范围定义为:

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

现在属性(property)租金如下:

 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 [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 可用。我怎样才能让一行显示可用的真实?问候

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com