答案1
设置一些测试数据后:
DECLARE @EmployeeUtil TABLE (
Employee_ID INT,
[Date] DATE,
Previous_FTE DECIMAL(3,2),
FTE DECIMAL(3,2)
)
INSERT INTO @EmployeeUtil
SELECT 1, '2016-03-24', 1, 0.67
UNION
SELECT 1, '2016-04-24', 0.67, 0.8
UNION
SELECT 1, '2016-06-01', 0.8, 1
UNION
SELECT 2, '2016-01-01', 1, 0.5
UNION
SELECT 2, '2016-02-03', 0.5, 0.6
UNION
SELECT 2, '2016-03-25', 0.6, 0.8
UNION
SELECT 2, '2016-04-20', 0.8, 0.25
UNION
SELECT 2, '2016-05-01', 0.25, 0.4
UNION
SELECT 2, '2016-06-01', 0.4, 0.8
UNION
SELECT 2, '2016-07-01', 0.8, 1
我将使用窗口函数来实现期望的结果:
SELECT Employee_ID,
[Date] AS [Start_date],
LEAD([Date]) OVER (PARTITION BY Employee_ID ORDER BY [DATE]) AS [End Date],
FTE
FROM @EmployeeUtil
UNION
SELECT e1.Employee_ID,
NULL AS [Start_date],
FIRST_VALUE(e1.[Date]) OVER (PARTITION BY e1.Employee_ID ORDER BY e1.[DATE]) AS [End Date],
FIRST_VALUE(e1.Previous_FTE) OVER (PARTITION BY e1.Employee_ID ORDER BY e1.[DATE]) AS FTE
FROM @EmployeeUtil e1
ORDER BY 1, 2, 3
SELECT
第一部分获取所有UNION
具有适当开始和结束日期的日期范围以及该日期范围的 FTE 值,但每个员工记录的最早日期之前的无限期除外。
SELECT
第二部分处理这个UNION
问题,在结果中为每个员工创建一个额外的行,以显示他们在最早日期之前的 FTE 值。