SQL Server : Creating A Complex Calendar View For Employees

Question

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



这只是一个星期，但它会在一年中的所有星期中一直持续到最右边。你可以明白为什么这是大量的体力劳动。
这个日历告诉我们:

一月一日国庆放假，大家都放假了

大家每天都在办公室工作

James Adams 不在办公室，为客户 A 提供咨询

我们使用颜色和代码使其更容易和更快地解释。
我已经制作了一个结构来在 SQL Server 中反射(reflect)这一点:



规划日
PlanningDay是最重要的。
它代表了一些特别的东西:比如假期或咨询任务。
它链接到 Employee , 具有日期值并用 bool 值表示 AM 或 PM(AM 为真或假)。一个 PlanningDay总是链接到一个 PlanningDayDetail . 重要的是要知道，当在表 PlanningDay 中找不到条目时，应该显示默认的 OF(办公室)，除非是周末)。 仅注册“异常(exception)”。

PlanningDayDetail

包含一般信息 PlanningDayType (如 Absent)，以及更具体的名称和缩写，如 Holiday 和 HOL。

PlanningDayType

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

目标是将这个结构变成一个很好的 SSRS 报告。该报告将包含开始日期和结束日期的过滤器(因此这些必须是动态的)和员工的过滤器(以便于搜索)。

我想创建一个存储过程，然后可以由报告使用，但我坚持返回此概述。我想为每个 Employee 返回 1 行, 2 列(AM 和 PM)包含 DisplayName链接到 PlanningDay 的详细信息(如果在那个日期 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 条记录，1 条记录显示 AM，另一条记录显示当天的 PM 部分。

我最近的尝试包括使用 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每人注册一次 Employee .我首先要实现的是这个(对不起，列表太长):

所需结果

+------------+----+-------------+---------------+
|    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 有一个特殊的 PlanningDay在客户 A 等处注册咨询...现在我知道我可以使用 COALESCE 将 NULLS 转换为默认值，但在此之前，我需要为每个没有 PlanningDay 的员工提供 NULLS。已被注册。

如果这甚至可以实现，我就可以尝试将此结果转换为预期的最终结果(见上文)。

先感谢您!

Answer 1

我认为你非常接近解决方案。我只是重新调整了初始连接以使用 交叉连接 这将为 Day、AM/PM 和 Employee 的每个组合生成一行。生成后，现有 左连接 s 将插入您的计划日，您可以用您在问题中指定的默认值替换 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]