基于选择的半小时和一小时时段预订-c#和sql [英] HALF-an-hour and One-hour time slot booking based on selection- c# and sql
问题描述
我无法找到特定治疗的半小时和一小时时段的可用时间 - 在选定日期.用户可以在预订结束时选择半小时和一小时示例 = 在选定日期 - 上午 9 点预订 1 小时(上午 9 点至上午 10 点)还有另一个预订 - 上午 11 点半小时(上午 11 点至上午 11:30)那么用户不应该在同一天看到这两个插槽他应该在显示器上看到这个(在选择治疗者和日期之后)
I am having trouble finding the available time for HALF-an-hour and One-hour time slots for a particular Treatment - on a selected date. User has options of selecting HALF-an-hour and One-hour at the booking end example = On a selected date - there is a booking for 9am for 1 hour (9am to 10am) and there is another booking - 11am for HALF-an-hour (11am to 11:30am) then the user should not see these two slots on the same selected day He should get this on the display(after selecting the treater and date)
- 上午 9 点至上午 930 点 ❌(不可用)
- 上午 930 点至上午 10 点 ❌(不可用)
- 上午 10 点至 1030 点 ✅(可用)
- 上午 1030 点至上午 11 点 ✅(有空)
- 上午 11 点至 11:30 ❌(不可用)
11:30 到 1200 pm ✅(可用)等等....................
- 9am to 930 am ❌ (not available)
- 930am to 10am ❌ (not available)
- 10-am -to 1030 ✅ (available)
- 1030 to 11am ✅ (available)
- 11am to 11:30 ❌ (not available)
11:30 to 1200pm ✅ (available) and so on................... .
上午 9 点至上午 10 点 ❌(不可用)
9am to 10am ❌ (not available)
下午 1 点到 2 点 ✅(有售)等等---------------------------
1pm to 2pm ✅ (available) and so on---------------------------
我试着这样做.
我创建了两张表 - 一张用于半小时时段，另一张用于一小时时段.
I created two tables - one for HALF-an-hour slots and - one for ONE-hour slots.
我有另一张桌子，上面有预订的条目.
I have another table that has the booked entries.
我尝试在 SQl 中使用 EXCEPT - 但这似乎给出了错误的结果
I tried with EXCEPT in SQl - but that seems to be giving wrong results
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 分钟的约会时会发生什么?如果安排这些约会的办公室在不同的日子有不同的时间会怎样?
I think you're probably overcomplicating this by having multiple tables for time slots of different lengths. What happens when you want to go to 15-minute intervals rather than 30? What happens when you want to allow 90-minute appointments? What happens if the office scheduling these appointments has different hours on different days?
我在下面提出的解决方案使用一张表来存储约会，仅此而已.显示的其余逻辑可以轻松进入存储过程或当您需要给定日期的可用约会列表时调用的某些内容.希望评论足以解释正在发生的事情.
The solution I propose below uses one table to store appointments, and that's it. The rest of the logic shown could easily go into a stored procedure or something that you call when you want a list of available appointments for a given date. Hopefully the comments are sufficient to explain what's going on.
-- 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 运行上面的代码，这是输出:
Here's the output if I run the above code with @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
这样的事情对你有用吗?
Will something like this work for you?
这篇关于基于选择的半小时和一小时时段预订-c#和sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
