如何获取SQL中可用小时数的列表

Question

以下是用例：客户端可以在房间中注册从一个日期到另一个日期的时间表。我正在尝试实现一个日历，在这个日历中，客户可以点击一天，并获得安排房间的可用时间。这是我迄今为止尝试过的，只有当一天有一个单独的时间表时，它才有效：

SELECT {Hours}.*
FROM {Hours}
WHERE {Hours}.[Id] BETWEEN (
SELECT
CASE
WHEN @DateInput = {Schedule}.[StartDate] THEN 0
WHEN @DateInput > {Schedule}.[StartDate] AND @DateInput < {Schedule}.[EndDate] THEN 23
WHEN @DateInput = {Schedule}.[EndDate] THEN {Schedule}.[EndHour]
END
FROM {Schedule}
WHERE {Schedule}.[RoomId] = @RoomInput
) AND (
SELECT
CASE
WHEN @DateInput = {Schedule}.[StartDate] THEN {Schedule}.[StartHour] - 1
WHEN @DateInput > {Schedule}.[StartDate] AND @DateInput < {Schedule}.[EndDate] THEN 0
WHEN @DateInput = {Schedule}.[EndDate] THEN 23
END
FROM {Schedule}
WHERE {Schedule}.[RoomId] = @RoomInput
)

Answer 1

我不熟悉像{Hours}这样的花括号表示法，但我认为这是一个外部系统的东西，并使用它运行。

我认为您不需要在两个值之间选择小时，而是需要构建查询结构，以选择不属于任何现有计划条目的小时。这可以通过NOT EXISTS((条件来完成。类似于：

SELECT H.*
FROM {Hours} H
WHERE NOT EXISTS (
SELECT *
FROM {Schedule} S
WHERE S.RoomId = @RoomInput
AND (S.StartDate < @DateInput
OR (S.StartDate = @DateInput AND S.StartHour <= H.Id))
AND (S.EndDate > @DateInput
OR (S.EndDate = @DateInput AND S.EndHour >= H.Id))
)

如果将日期和时间结合起来，这样的比较通常更容易写。

WHERE S.RoomId = @RoomInput
AND DATEADD(hour, H.Id, @DateInput)
BETWEEN DATEADD(hour, S.StartHour, S.StartDate)
AND DATEADD(hour, S.EndHour, S.EndDate)

您可能需要根据EndDate是包含还是独占来调整结束比较。(EndHour=10的时间表条目是在10:00还是11:00结束？(

有关工作演示，请参阅

• 这个数据库<gt；使用包含日期的小提琴
• 这个db<gt；使用独家结束日期(上午9点开始的1小时会议的结束时间为10(
• 这个db<gt；使用组合日期/时间和独占结束日期/时间的fiddle