如果所有员工都忙碌（分配到预约），请删除时段 [英] 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:

1. 错误的做法！这组子查询可能会导致效率问题...
   
   

1. 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

使用相同的逻辑改进第二个查询！

Use the same logic to improve second query!

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示例 [ ^ ]

当你读完并尝试写出正确的查询并且卡住之后，回到这里并发布另一个问题。但是你应该被警告：你必须解释你做了什么以及为什么你的查询不起作用。

有一个工具，它可以帮助你转换sql查询进入linq。它被称为 Linqer [ ^ ]。你可以下载并使用试用版，但你必须记住我的第一个注释。

Do not expect that someone will do the job for you!
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.

There's one tool, which may help you to convert sql query into linq. It's called Linqer[^]. You can download and use trial version, but you have to keep in mind my first note.

如何将linq查询转换为sql - Stack Overflow [ ^ ]

这篇关于如果所有员工都忙碌（分配到预约），请删除时段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！