在SQL中列出日期范围内的所有日期，但忽略银行假日

Question

我正在做一名假期经理。

我有一张表，上面列出了每个假期的开始和结束日期。

[LeaveID]、[EmployeID]、[StartDate]、[EndDate]

我还有一个日历表，上面有2016-2030年的日期，列出了日期格式的常见变化以及工厂关闭的时间，包括银行假日等。

我正在为它做前端工作，现在他们希望我以某种日历格式显示它，所以我需要在每一天标记谁已经预订了休息时间。

我想我需要列出每个日期范围内的每个日期(开始日期到结束日期(，然后检查日历上的每个日期是否出现在该列表中。

所以基本上我需要得到一个日期范围内的日期列表

除此之外。我希望能够将上面的日期列表与日历表进行比较，这样我就可以在计算每个实例的假期时忽略银行假期。

提前感谢！

Answer 1

要获得一个日期范围内的日期列表，您需要从1到n的数字来源。我通常创建这样的表，并将其称为Numbers表。

若要生成某个范围内的日期列表，请使用以下查询。

SELECT
    DATEADD(DAY, Numbers.Number-1, [StartDate]) Date
FROM
    Numbers
WHERE
    DATEADD(DAY, Numbers.Number-1, [StartDate]) <= [EndDate]

要创建这样的表，请参阅此问题。

---

如果您想在Employee表中列出所有日期，只需交叉加入即可。

SELECT
    e.EmployeeID, 
    DATEADD(DAY, n.Number-1, e.[StartDate]) Date
FROM
    Numbers n, Employee e
WHERE
    DATEADD(DAY, n.Number-1, e.[StartDate]) <= e.[EndDate]

Answer 2

由于您已经有了日期表，因此不需要另一个答案中提到的数字表。要完成您所追求的目标，需要从日期表中进行简单的SQL Join。根据您希望如何格式化最终报告，您可以count增加返回的EmployeeID的数量，也可以将它们全部分组到DateValue前端的日历/表格控件中。

在下面的查询中，对于范围内指定的每个日期，您将至少获得一个DateValue(您可以应用自己的筛选，如where Dates.BankHoliday = 0等(，对于多个员工休假的情况，您将获得多个：

-- Build some dummy data to run the query against.
declare @Emp table (LeaveID int, EmployeeID int , StartDate datetime, EndDate datetime);
insert into @Emp values
 (1,1,'20161101','20161105')
,(2,1,'20161121','20161124')
,(3,2,'20161107','20161109')
,(4,3,'20161118','20161122');
declare @Dates table (DateKey int, DateValue datetime, DateLabel nvarchar(50));
declare @s datetime = '20161025';
with cte as
(
select cast(convert(nvarchar(8),@s,112) as int) as DateKey
        ,@s as DateValue
        ,convert(nvarchar(50),@s,103) as DateLabel
union all
select cast(convert(nvarchar(8),DateValue+1,112) as int)
        ,DateValue+1
        ,convert(nvarchar(50),DateValue+1,103)
from cte
where DateValue+1 <= '20161205'
)
insert into @Dates
select * from cte;

-- Actually query the data.
-- Define the start and end of your date range to return.
declare @MinStart datetime = (select min(StartDate) from @Emp);
declare @MaxEnd datetime = (select max(EndDate) from @Emp);
select d.DateValue
    ,e.EmployeeID
from @Dates d
    left join @Emp e
        on(d.DateValue between e.StartDate and e.EndDate)
where d.DateValue between @MinStart and @MaxEnd
order by d.DateValue
        ,e.EmployeeID;