SQL Filter 日期范围查询

Question

我有一个日程记录列表

员工时间表

其中一些已经被预订了。

预订的时间表

还有一些假期

假期

我只想获取可用的员工时间表

预期时间表 = StaffSchedule - BookedSchedule-Holidays

即，我只需要 StaffSchedule 表中的第 2,6 行

下面是我试过的查询，但是没有显示结果

with NonBookingSlots as
(
select StartdateTime,EndDateTime from Holidays
union all
select StartdateTime,EndDateTime from BookedSchedules
)

SELECT
    StaffId, StartdateTime, EndDateTime
FROM StaffSchedule
WHERE 
not exists (select 1
                from NonBookingSlots h
                where cast(StartdateTime as DATETIME) between 
                cast(h.startdatetime as DATETIME) 
                and cast(h.enddatetime as DATETIME)
           )

SQL FIDDLE DEMO

Answer 1

对于所有示例，我假设开始和结束时间在 BookedSchedules 中将与 StaffSchedules 完全匹配开始和结束时间。

有了CTE，类似问题:

我不建议使用此查询，但它可能会有所帮助，因为它与问题中的查询类似。它不是很可读。

with NonBookingSlots as
(
  select null as StaffId,StartdateTime,EndDateTime from Holidays
  union all
  select StaffId,StartdateTime,EndDateTime from BookedSchedules
)

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      NonBookingSlots
    where
      StaffSchedule.StaffId = isnull(NonBookingSlots.StaffId,StaffSchedule.StaffId)
      and (
        (
          StaffSchedule.StartDateTime =  NonBookingSlots.StartDateTime
          and StaffSchedule.EndDateTime = NonBookingSlots.EndDateTime
        ) or (
          StaffSchedule.StartDateTime <  NonBookingSlots.EndDateTime
          and StaffSchedule.EndDateTime > NonBookingSlots.StartDateTime
        )
     )
  ) 

SQL fiddle :http://sqlfiddle.com/#!3/9cbf4/14

没有 CTE:

我认为这个版本更具可读性。

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.StaffId = BookedSchedules.StaffId
      and StaffSchedule.StartDateTime =  BookedSchedules.StartDateTime
      and StaffSchedule.EndDateTime = BookedSchedules.EndDateTime
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <  Holidays.EndDateTime
      and StaffSchedule.EndDateTime > Holidays.StartDateTime
  )

SQL fiddle :http://sqlfiddle.com/#!3/9cbf4/15

使用外键 - 我推荐:

如果BookedSchedules总是匹配 StaffSchedule你应该对 StaffSchedule 使用外键而不是在 BookedSchedules 中复制开始和结束时间.这会产生更清晰、更高效的查询。

select
  StaffId, StartdateTime, EndDateTime
from 
  StaffSchedule
where
  not exists(
    select
      1
    from 
      BookedSchedules
    where
      StaffSchedule.Id = BookedSchedules.StaffScheduleId
  ) and not exists(
    select
      1
    from 
      Holidays
    where
      StaffSchedule.StartDateTime <=  Holidays.EndDateTime
      and StaffSchedule.EndDateTime >= Holidays.StartDateTime
  )

SQL fiddle :http://sqlfiddle.com/#!3/8a684/3