sql - 排除另一个表中存在日期的行

Question

我有两张表，一张是工作模式，一张是缺勤。

1)工作模式

ID | Shift Start | Shift End
123| 01-03-2017  | 02-03-2017

2) 缺勤

ID| Absence Start | Absence End
123| 01-03-2017   | 04-03-2017

从工作模式中选择行时，排除缺勤表中日期标记为缺勤的任何行的最佳方法是什么？

例如，我有一个报告，它使用工作模式表来计算员工每周工作的天数，但我不希望它包含在缺勤表上标记为缺勤的天数这有道理吗？也不希望它包含缺勤开始日期和缺勤结束日期之间的任何天数？

Answer 1

如果缺席的范围应始终包含要排除的类次，您可以使用not contains():

select *
from WorkPatterns w
where not exists (
  select 1
  from Absences a
  where a.Id = w.Id
    and a.AbsenceStart <= w.ShiftStart
    and a.AbsenceEnd   >= w.ShiftEnd
  )

rextester 演示:http://rextester.com/DCODC76816

返回:

+-----+------------+------------+
| id  | ShiftStart |  ShiftEnd  |
+-----+------------+------------+
| 123 | 2017-02-27 | 2017-02-28 |
| 123 | 2017-03-05 | 2017-03-06 |
+-----+------------+------------+

给出这个测试设置:

create table WorkPatterns ([id] int, [ShiftStart] datetime, [ShiftEnd] datetime) ; 
insert into WorkPatterns ([id], [ShiftStart], [ShiftEnd]) values 
 (123, '20170227', '20170228')
,(123, '20170301', '20170302')
,(123, '20170303', '20170304')
,(123, '20170305', '20170306')
;
create table Absences ([id] int, [AbsenceStart] datetime, [AbsenceEnd] datetime) ; 
insert into Absences ([id], [AbsenceStart], [AbsenceEnd]) values 
(123, '20170301', '20170304');