我无法在选定的日期找到特定治疗的半小时和一小时时段的可用时间。 用户在预订结束时可以选择半小时和一小时 示例 = 在选定日期 - 上午 9 点预订 1 小时（上午 9 点至上午 10 点） 还有另一次预订 - 上午 11 点半小时（上午 11 点至上午 11:30） 那么用户不应该在同一选定的日期看到这两个时段 他应该在显示屏上看到这个（选择治疗师和日期后）
11:30 至 1200pm ✅（有空） 等等................... 。
上午 9 点至上午 10 点❌（不可用）
下午 1 点至 2 点✅（有空） 等等 - - - - - - - - - - - - - -
我尝试这样做。
我创建了两张表 - 一张用于半小时时段，一张用于一小时时段。
I have another table that has the booked entries.
我尝试在 SQl 中使用 EXCEPT - 但这似乎给出了错误的结果
SELECT T1.timeBegin from ClinicNew.HalfTiming T1
left join ClinicNew.FullTiming T2
On T1.TimeBegin=T2.TimeBegin
EXCEPT
select distinct T1.timeBegin from ClinicNew.HalfTiming T1
inner join ClinicNew.NewTreaterEngagedDTM T2
On T1.timeBegin = T2.timeBegin
where T2.BookedDate = '2014-04-15'
and T2.TreaterID=请帮忙
我认为您可能通过为不同长度的时间段设置多个表而使问题变得过于复杂。如果您想要间隔 15 分钟而不是 30 分钟，会发生什么情况？当您想要允许 90 分钟的约会时会发生什么？如果安排这些预约的办公室在不同日期的工作时间不同，会发生什么情况？
我在下面提出的解决方案使用一张表来存储约会，仅此而已。显示的其余逻辑可以轻松地进入存储过程或当您想要给定日期的可用约会列表时调用的东西。希望这些评论足以解释正在发生的事情。
-- Sample data from the question.
declare @Appointment table
(
[ID] bigint not null identity(1, 1), -- Primary key.
[BookedDate] date not null, -- The date of the appointment.
[Time] time(0) not null, -- The start time of the appointment.
[Duration] int not null -- The length of the appointment in minutes.
);
insert @Appointment
([BookedDate], [Time], [Duration])
values
('2014-04-15', '09:00', 60),
('2014-04-15', '10:00', 30),
('2014-04-15', '17:00', 60),
('2014-04-15', '18:30', 30);
-- @StartTime is the time the office opens on the desired date.
-- @EndTime is the time the office closes on the desired date.
-- @Interval is the number of minutes that separate potential appointment times.
-- @DesiredDate is the date on which an appointment is requested.
-- @DesiredLength is the length of the requested appointment in minutes.
declare @StartTime time(0) = '09:00';
declare @EndTime time(0) = '21:00';
declare @Interval int = 30;
declare @DesiredDate date = '2014-04-15';
declare @DesiredLength int = 30;
-- This CTE enumerates all potential timeslots on the @DesiredDate given the above data.
with [TimeSlotCTE] as
(
-- Base case: the first appointment slot of the day.
select
[From] = @StartTime,
[To] = dateadd(minute, @DesiredLength, @StartTime)
union all
-- Recursive case: create a subsequent appointment slot as long as doing so won't
-- take us past the office's closing time.
select
dateadd(minute, @Interval, [From]),
dateadd(minute, @Interval, [To])
from
[TimeSlotCTE]
where
dateadd(minute, @Interval, [To]) <= @EndTime
)
-- Finally, we simply select every time slot defined above for which there does not
-- yet exist an overlapping appointment on the requested date.
select
[T].[From],
[T].[To],
[Available] =
case when exists
(
select 1 from @Appointment [A]
where
-- Forgot this line the first time around!
[A].[BookedDate] = @DesiredDate and
[A].[Time] < [T].[To] and
dateadd(minute, [A].[Duration], [A].[Time]) > [T].[From]
)
then 'No' else 'Yes' end
from
[TimeSlotCTE] [T];
如果我运行上面的代码，这是输出@DesiredLength = 30:
From To Available
09:00:00 09:30:00 No
09:30:00 10:00:00 No
10:00:00 10:30:00 No
10:30:00 11:00:00 Yes
11:00:00 11:30:00 Yes
11:30:00 12:00:00 Yes
12:00:00 12:30:00 Yes
12:30:00 13:00:00 Yes
13:00:00 13:30:00 Yes
13:30:00 14:00:00 Yes
14:00:00 14:30:00 Yes
14:30:00 15:00:00 Yes
15:00:00 15:30:00 Yes
15:30:00 16:00:00 Yes
16:00:00 16:30:00 Yes
16:30:00 17:00:00 Yes
17:00:00 17:30:00 No
17:30:00 18:00:00 No
18:00:00 18:30:00 Yes
18:30:00 19:00:00 No
19:00:00 19:30:00 Yes
19:30:00 20:00:00 Yes
20:00:00 20:30:00 Yes
20:30:00 21:00:00 Yes
这是与@DesiredLength = 60:
From To Available
09:00:00 10:00:00 No
09:30:00 10:30:00 No
10:00:00 11:00:00 No
10:30:00 11:30:00 Yes
11:00:00 12:00:00 Yes
11:30:00 12:30:00 Yes
12:00:00 13:00:00 Yes
12:30:00 13:30:00 Yes
13:00:00 14:00:00 Yes
13:30:00 14:30:00 Yes
14:00:00 15:00:00 Yes
14:30:00 15:30:00 Yes
15:00:00 16:00:00 Yes
15:30:00 16:30:00 Yes
16:00:00 17:00:00 Yes
16:30:00 17:30:00 No
17:00:00 18:00:00 No
17:30:00 18:30:00 No
18:00:00 19:00:00 No
18:30:00 19:30:00 No
19:00:00 20:00:00 Yes
19:30:00 20:30:00 Yes
20:00:00 21:00:00 Yes
这样的事情对你有用吗？