我被要求将一个大型的Excel日历转换成SQL表结构,以便我们可以在内部应用程序中使用它的功能。为了更容易地解释,我做了一个虚拟示例:
- 因为元旦是国家假日,所以每个人都休息了
- 每个人都在办公室工作
- 詹姆斯·亚当斯不在办公室,正在客户A处咨询
计划日
计划日
是最重要的一个。它代表着一些特别的事情:比如假期或咨询任务。它与一个 员工
相关联,有一个日期值,并通过布尔值(上午为 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。
如果这是可行的话,我可以尝试将此结果旋转到预期的最终结果(请参见上文)。
提前感谢您!