SQL Server：为员工创建复杂的日历视图

Question

我被要求将一个大型的Excel日历转换成SQL表结构，以便我们可以在内部应用程序中使用它的功能。为了更容易地解释，我做了一个虚拟示例：

这仅仅是一周的情况，但是整个年份的所有周都延续到最右边。你可以看出这需要大量手动劳动。这个日历告诉我们：

1. 因为元旦是国家假日，所以每个人都休息了
2. 每个人都在办公室工作
3. 詹姆斯·亚当斯不在办公室，正在客户A处咨询

我们使用颜色和代码使其更易于理解和快速解释。我已经建立了一个结构，以SQL Server为镜像。

计划日

计划日 是最重要的一个。它代表着一些特别的事情：比如假期或咨询任务。它与一个 员工 相关联，有一个日期值，并通过布尔值（上午为 true 或 false）表示上午或下午。一个 计划日 总是与一个 计划日详情 相关联。 需要知道的是，当在表 计划日 中没有找到条目时，应显示 OF（办公室）的默认值，除非这是周末。 只有“例外”才被记录。

计划日详情

包含有关一般 计划日类型（如缺席）以及更具体的名称和缩写，例如假期和 HOL 的信息。

计划日类型

包含可用于使用的一般类型及其相应的颜色。例如，所有缺席类型都标记为紫色，无论是假期还是疾病。

目标是将此结构放入漂亮的 SSRS 报告中。报告将具有开始和结束日期的筛选器（因此这些必须是动态的），并且具有员工的筛选器（以便进行轻松搜索）。

我想创建一个存储过程，可以被报告使用，但我卡在了返回这个概述上。我想返回每个员工的1行，2列（上午和下午），包含与PlanningDay相关联的详细信息的DisplayName（如果为该日期AM / PM注册了一个）。对于从开始日期到结束日期参数之间的每个日期，都要返回基本上与Excel完全相同的内容。

我一直在研究合并常用表达式以呈现所有日期，并进行旋转以显示为列，但还没有真正变得更聪明。我希望你能帮助我。

编辑：

表脚本

CREATE TABLE [Mrd].[Employee]
(
    [Id] INT IDENTITY(1,1) CONSTRAINT [PK_Mrd_Employee_Id] PRIMARY KEY,
    [FirstName] NVARCHAR(100) NOT NULL,
    [LastName] NVARCHAR(100) NULL,
)

CREATE TABLE [Mrd].[PlanningDay]
(
    [Id] INT IDENTITY(1,1) CONSTRAINT [PK_Mrd_PlanningDay_Id] PRIMARY KEY,
    [EmployeeId] INT NOT NULL,
    [Date] DATE NOT NULL,
    [AM] BIT NOT NULL,
    [PlanningDayDealId] INT NOT NULL,

    CONSTRAINT [FK_Mrd_PlanningDay_PlanningDayDealId_Mrd_PlanningDayDetail_Id]
    FOREIGN KEY ([PlanningDayDealId])
    REFERENCES [Mrd].[PlanningDayDetail] ([Id]),

    CONSTRAINT [FK_Mrd_PlanningDay_EmployeeId_Mrd_Employee_Id]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [Mrd].[Employee] ([Id])
)

CREATE TABLE [Mrd].[PlanningDayDetail]
(
    [Id] INT IDENTITY(1,1) CONSTRAINT [PK_Mrd_PlanningDayDetail_Id] PRIMARY KEY,
    [PlanningDayTypeId] INT NOT NULL,
    [Name] NVARCHAR(255) NOT NULL,
    [DisplayName] NVARCHAR(3) NOT NULL,

    CONSTRAINT [FK_Mrd_PlanningDayDetail_PlanningDayTypeId_Mrd_PlanningDayType_Id]
    FOREIGN KEY ([PlanningDayTypeId])
    REFERENCES [Mrd].[PlanningDayType] ([Id])
)

CREATE TABLE [Mrd].[PlanningDayType]
(
    [Id] INT IDENTITY(1,1) CONSTRAINT [PK_Mrd_PlanningDayType_Id] PRIMARY KEY,
    [Name] NVARCHAR(255) NOT NULL,
    [ReportColor] NVARCHAR(10) NOT NULL
)

虚拟数据

INSERT INTO [Mrd].[Employee] ([FirstName],[LastName])
VALUES
('Sandra','Cooper'),
('James','Adams'),
('Martha','Reid');

INSERT INTO [Mrd].[PlanningDayType] ([Name],[ReportColor])
VALUES 
('Absent','#8b4789'),
('Consultancy','#c7c700');

INSERT INTO [Mrd].[PlanningDayDetail] ([PlanningDayTypeId],[Name],[DisplayName])
VALUES 
(1,'New Year','HOL'),
(2,'Customer A','C-A');

INSERT INTO [Mrd].[PlanningDay] ([EmployeeId],[Date],[AM],[PlanningDayDealId])
VALUES 
(1,'2018-01-01',1,1),
(1,'2018-01-01',0,1),
(2,'2018-01-01',1,1),
(2,'2018-01-01',0,1),
(3,'2018-01-01',1,1),
(3,'2018-01-01',0,1),
(2,'2018-01-03',1,2),
(2,'2018-01-03',0,2),
(2,'2018-01-04',1,2),
(2,'2018-01-04',0,2),
(2,'2018-01-05',1,2);

预期结果（如果可以这样）

+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
|   Employee    | 2018-01-01 AM | 2018-01-01 PM | 2018-01-02 AM | 2018-01-02 PM | 2018-01-03 AM | 2018-01-03 PM |
+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| Sandra Cooper | HOL           | HOL           | OF            | OF            | OF            | OF            |
| James Adams   | HOL           | HOL           | OF            | OF            | C-A           | C-A           |
| Martha Reid   | HOL           | HOL           | OF            | OF            | OF            | OF            |
+---------------+---------------+---------------+---------------+---------------+---------------+---------------+

我尝试过的方法

在开始报告之前，我正在尝试创建一个查询/存储过程，每个员工每天返回2条记录，其中一条记录表示该员工当天的上午部分，另一条记录表示下午部分。

我的最新尝试包括使用CTE创建日期范围：

DECLARE @StartDate DATE = '2018-01-01';
DECLARE @EndDate   DATE = '2018-01-31';


WITH AllDays AS
    (SELECT @StartDate AS [Day]
     UNION ALL
     SELECT DATEADD(DAY, 1, [Day])
     FROM AllDays
      WHERE [Day] < @EndDate)

SELECT ad.[Day] AS 'Date', pd.[AM], pdd.[DisplayName], e.[FirstName] + ' ' + e.[LastName] AS 'Employee'
FROM AllDays ad
LEFT JOIN [Mrd].[PlanningDay] pd
ON ad.[Day] = pd.[Date]
LEFT JOIN [Mrd].[PlanningDayDetail] pdd
ON pd.[PlanningDayDealId] = pdd.[Id]
LEFT JOIN [Mrd].[PlanningDayType] pdt
ON pdd.[PlanningDayTypeId] = pdt.[Id]
LEFT JOIN [Mrd].[Employee] e
ON pd.[EmployeeId] = e.[Id]
OPTION (MAXRECURSION 0);

错误的结果

+------------+------+-------------+---------------+
|    Date    |  AM  | DisplayName |   Employee    |
+------------+------+-------------+---------------+
| 2018-01-01 | 1    | HOL         | Sandra Cooper |
| 2018-01-01 | 0    | HOL         | Sandra Cooper |
| 2018-01-01 | 1    | HOL         | James Adams   |
| 2018-01-01 | 0    | HOL         | James Adams   |
| 2018-01-01 | 1    | HOL         | Martha Reid   |
| 2018-01-01 | 0    | HOL         | Martha Reid   |
| 2018-01-02 | NULL | NULL        | NULL          |
| 2018-01-03 | 1    | C-A         | James Adams   |
| 2018-01-03 | 0    | C-A         | James Adams   |
| 2018-01-04 | 1    | C-A         | James Adams   |
| 2018-01-04 | 0    | C-A         | James Adams   |
| 2018-01-05 | 1    | C-A         | James Adams   |
| 2018-01-06 | NULL | NULL        | NULL          |
| 2018-01-07 | NULL | NULL        | NULL          |
+------------+------+-------------+---------------+

这使我得到了1月1日的正确结果，因为那是一个假期，每个员工都注册了“PlanningDays”。我首先要实现的目标是这个（抱歉列表很长）：

所需结果

+------------+----+-------------+---------------+
|    Date    | AM | DisplayName |   Employee    |
+------------+----+-------------+---------------+
| 2018-01-01 |  1 | HOL         | Sandra Cooper |
| 2018-01-01 |  0 | HOL         | Sandra Cooper |
| 2018-01-01 |  1 | HOL         | James Adams   |
| 2018-01-01 |  0 | HOL         | James Adams   |
| 2018-01-01 |  1 | HOL         | Martha Reid   |
| 2018-01-01 |  0 | HOL         | Martha Reid   |
| 2018-01-02 |  1 | OF          | Sandra Cooper |
| 2018-01-02 |  0 | OF          | Sandra Cooper |
| 2018-01-02 |  1 | OF          | James Adams   |
| 2018-01-02 |  0 | OF          | James Adams   |
| 2018-01-02 |  1 | OF          | Martha Reid   |
| 2018-01-02 |  0 | OF          | Martha Reid   |
| 2018-01-03 |  1 | OF          | Sandra Cooper |
| 2018-01-03 |  0 | OF          | Sandra Cooper |
| 2018-01-03 |  1 | C-A         | James Adams   |
| 2018-01-03 |  0 | C-A         | James Adams   |
| 2018-01-03 |  1 | OF          | Martha Reid   |
| 2018-01-03 |  0 | OF          | Martha Reid   |
| 2018-01-04 |  1 | OF          | Sandra Cooper |
| 2018-01-04 |  0 | OF          | Sandra Cooper |
| 2018-01-04 |  1 | C-A         | James Adams   |
| 2018-01-04 |  0 | C-A         | James Adams   |
| 2018-01-04 |  1 | OF          | Martha Reid   |
| 2018-01-04 |  0 | OF          | Martha Reid   |
| 2018-01-05 |  1 | OF          | Sandra Cooper |
| 2018-01-05 |  0 | OF          | Sandra Cooper |
| 2018-01-05 |  1 | C-A         | James Adams   |
| 2018-01-05 |  0 | OF          | James Adams   |
| 2018-01-05 |  1 | OF          | Martha Reid   |
| 2018-01-05 |  0 | OF          | Martha Reid   |
+------------+----+-------------+---------------+

注意，在1月2日，我想为每个员工呈现OF，而不是NULL。在1月3日，我想将OF（Office）作为Sandra和Martha的默认值呈现，但James在客户A咨询中注册了特殊的PlanningDay等等...现在我知道我可以使用COALESCE将NULLS转换为默认值，但在我能做到这一点之前，我需要对没有注册PlanningDay的每个员工都使用NULLS。

如果这是可行的话，我可以尝试将此结果旋转到预期的最终结果（请参见上文）。

提前感谢您！

- J. Michiels

---

这是一个不错的起点。http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ - Sean Lange

@J.M 我相信你是在要求在一个问题中实现一个小项目？ - Sathiya Kumar V M

@SathiyaKumar，这不是一个小项目。这是一个相当大的任务。 - Sean Lange

谢谢链接，肖恩。我已经添加了我的表脚本和相同的虚拟数据来使用。 - J. Michiels

你已经尝试过什么了吗？你需要一个日历表或一个计数表。需要返回每个输出日期的行。 - Sean Lange

我已经添加了我目前正在尝试的内容。请不要误解，我不想让任何人为我创建所有这些内容。我只需要一点指导，告诉我我正在做什么。我对SQL和编程还很陌生，所以如果我在这里尝试实现的东西是无用的，请随时告诉我 :) 我还编辑了我的虚拟数据以修复一些错误。 - J. Michiels

Answer 1

我认为你离解决方案很接近了。我只是重新调整了初始连接，使用CROSS JOIN，这将为每个Day、AM/PM和Employee组合生成一行。一旦生成了这个，现有的LEFT JOIN将插入您的计划天数，您可以用您在问题中指定的默认值替换NULL。

您可以在SQL Fiddle中尝试此操作。

DECLARE @StartDate DATE = '2018-01-01';
DECLARE @EndDate   DATE = '2018-01-31';

WITH AllDays AS (
    SELECT @StartDate AS [Day]

    UNION ALL

    SELECT 
        DATEADD(DAY, 1, [Day])
    FROM
        AllDays
    WHERE 
        [Day] < @EndDate
    )
SELECT
    ad.[Day] AS 'Date'
,   Period.[AM]
,   pdd.[DisplayName]
,   e.[FirstName] + ' ' + e.[LastName] AS 'Employee'
FROM
    AllDays ad
CROSS JOIN
    (SELECT CAST( 0 AS BIT ) AS [AM] UNION ALL SELECT CAST( 1 AS BIT )) AS Period
CROSS JOIN
    [Employee] e
LEFT OUTER JOIN
    [PlanningDay] pd ON (
        pd.[Date] = ad.[Day]
    AND pd.EmployeeId = e.[Id]
    AND pd.[AM] = Period.[AM]
    )
LEFT OUTER JOIN
    [PlanningDayDetail] pdd ON (pdd.[Id] = pd.[PlanningDayDealId])
LEFT OUTER JOIN
[PlanningDayType] pdt ON (pdt.[Id] = pdd.[PlanningDayTypeId])
ORDER BY
    [Date], [Employee], [AM]

- Nik Shenoy

---

非常感谢你，Nik，这解决了我的问题！我之前也读过关于交叉连接的内容，但在我遇到困难时没有想起来 :-) 我现在可以开始使用它来制作样本报告了。你知道如何将这些数据像我问题中的“期望结果”部分那样进行透视吗？或者你认为这有点过度或根本不可能满足上午/下午的要求？非常感谢！ - J. Michiels

您真的想在 SSRS 报表中进行数据透视。您可以在 SQL 中完成此操作，但是需要提前确定日期范围，较为死板。即使使用动态 SQL 完成，报表引擎也难以处理此类内容。如果在 SSRS 矩阵中进行操作，则很容易获得正确的输出结果。 - Nik Shenoy

好的，我会在接下来的几天尝试一下。再次感谢您提供的建设性反馈！今晚我会睡得很好 :-) - J. Michiels