如果所有员工都忙碌(分配到预约),请删除时段 [英] Remove time slot if all employees are busy (assigned to a appointment)
问题描述
我有一个日历,客户将用它来选择日期,当选择日期时,如果所有员工在特定时间忙于我希望将其删除,弹出窗口会显示带有工作时间的下拉菜单从下拉菜单中
这是我到目前为止
I have a calendar, which the customers will use to choose a date, when the date is chosen a popup appears with a dropdown menu with the working hours, if all employees are busy at the particular time I want it to be removed from the drop down menu
this is what I have so far
( select WorkingHours from WorkingHours
except
(select StartTime from Assign_Staff,Schedule1
where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))
) ( select distinct StaffID, WorkingHours from Staff,WorkingHours
except
(select StaffID,StartTime from Assign_Staff,Schedule1
where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))
)
< br $> b $ b
我的表:schedule表包含(ID,date,start_time,end_time,Customerid)appointment_Staff( id,ScheduleID,staffID,广告minID)working_Hours(id,start_time,end_time)
我尝试过:
我无法弄清楚
my tables: schedule table contains (ID, date, start_time,end_time,Customerid) appointment_Staff (id, ScheduleID, staffID,AdminID) working_Hours (id,start_time,end_time)
What I have tried:
I can't figure it out
推荐答案
很少有笔记:
Few notes:
- 错误的做法!这组子查询可能会导致效率问题...
- Wrong approach! This set of subqueries may cause efficiency issues...
(select StartTime from Assign_Staff,Schedule1
where Assign_Staff.ScheduleId=Schedule1.ScheduleID and
exists (select ScheduleID,StartTime from Schedule1 where Date='2016-08-16'))
这应该转换为使用 join
的单个查询 where
子句:
This should be converted into single query which uses join
and where
clause:
SELECT StartTime
FROM Assign_Staff As sta INNER JOIN Schedule1 AS sche ON sta.ScheduleId=sche.ScheduleID
WHERE sche.Date='2016-08-16'
var result = from sta in Assign_Staff
join sche in Schedule1.Where(s=>s.Date==new DateTime(2016,8,16)) on sta.ScheduleID equals sche.ScheduleID
select sche.StartTime
var second = (from asta in Assign_Staff
from whr in WorkingHours
select new {asta.StaffID, whr.WorkingHours}).Except(
from sta in Assign_Staff
join sche in Schedule1.Where(s=>s.Date==new DateTime(2016,8,16)) on sta.ScheduleID equals sche.ScheduleID
select new {sta.StaffID, sche.StartTime})
我建议从基础开始:
LINQ to SQL [ ^ ]
LINQ to SQL:关系数据的.NET语言集成查询 [ ^ ]
C#中的101个LINQ示例 [ ^ ]
当你读完并尝试写出正确的查询并且卡住之后,回到这里并发布另一个问题。但是你应该被警告:你必须解释你做了什么以及为什么你的查询不起作用。
I'd suggest to start with basics:
LINQ to SQL[^]
LINQ to SQL: .NET Language-Integrated Query for Relational Data[^]
101 LINQ Samples in C#[^]
When you finish reading and you take a try to write proper queries and you get stuck, come back here and post another question. But you should be warned: you have to explain what you have done and why your query doesn't work.
如何将linq查询转换为sql - Stack Overflow [ ^ ]
这篇关于如果所有员工都忙碌(分配到预约),请删除时段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!