SQL筛选日期范围查询

Question

我有一个日程记录列表

员工日程表

其中一些已经被预订了。

已预订的日程表

此外还有一些假期

假期表

我想要得到仅有的可用员工日程

预计日程= 员工日程表 - 已预订的日程表 - 假期表

即，我只需要员工日程表中的第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演示

- Billa

---

+1 用于一个完整的问题 - Kermit

员工的日程安排可以部分预定，还是只能完全预定（就像您所有的示例数据一样）？ - Brian DeMilia

@ShWiVeL，已经满员了。 - Billa

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

- Robert Harris

---

我们不能只用一个where吗？就像我尝试使用CTE一样？ - Billa

@Billa 你是指只需要一个EXISTS子查询吗？ - Brian DeMilia

我不明白在这种情况下使用with子句来减少EXISTS子查询的数量（从2个到1个）的意义所在。您不会多次引用它（使用with子句的一个优点），并且您并没有简化代码（使用with子句的另一个优点）。您是否有特别的原因想要使用一个exists子查询，以及一个with子句，而不是没有with子句和2个exists子查询？ - Brian DeMilia

@ShWiVeL 我同意，这只会让它变得更加复杂。我以这种方式展示它是为了保持 @Billa 提出问题的方式。我建议重构并在 BookedSchedules 中使用 StaffSchedule 的外键。 - Robert Harris

@Billa 我更新了答案，包括了几个替代方案。它们都能得到你想要的结果；然而，使用外键是我的建议。 - Robert Harris

Answer 2

select *
  from dbo.StaffSchedule x
 where not exists (select 'y'
          from dbo.BookedSchedules y
         where y.staffid = x.staffid
           and y.startdatetime = x.startdatetime
           and y.enddatetime = x.enddatetime)
   and not exists (select 'z'
          from dbo.Holidays z
         where cast(z.startdatetime as date) =
               cast(x.startdatetime as date))

这里有一个 SQL Fiddle 示例：http://sqlfiddle.com/#!3/07698/19/0

编辑 - 处理跨越多天的假期：

select *
  from dbo.StaffSchedule x
 where not exists (select 'y'
          from dbo.BookedSchedules y
         where y.staffid = x.staffid
           and y.startdatetime = x.startdatetime
           and y.enddatetime = x.enddatetime)
   and not exists (select 'z'
          from dbo.Holidays z
         where cast(z.startdatetime as date) <=
               cast(x.startdatetime as date)
           and cast(z.enddatetime as date) >=
               cast(x.enddatetime as date))

- Brian DeMilia

---

假期可以是一个范围。例如3月5日至3月7日。 - Billa

@Billa 添加了一个持续2天以上的假期逻辑。 - Brian DeMilia