sql-server - 从 SQL Server 2012 中的列表中返回缺少的日期

Question

考虑过滤器:

• 开始:14/09/2015(星期一)
• 结束:2015 年 9 月 20 日(星期日)

我从 14/09 到 20/09 的查询得到了这个结果:

╔════════════╦══════════╦══════════════╦═══════════╗
║    Date    ║ Employee ║ EmployeeType ║   Type    ║
╠════════════╬══════════╬══════════════╬═══════════╣
║ 14/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 14/09/2015 ║ Silva    ║ Permanent    ║ Timesheet ║
║ 16/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 17/09/2015 ║ Airn     ║ Casual       ║ Timesheet ║
╚════════════╩══════════╩══════════════╩═══════════╝

如果员工是“Permanent”并且缺失的日期是“Monday to Friday”，我需要返回所有缺失的日期，如下所示:

╔════════════╦══════════╦══════════════╦═══════════╗
║    Date    ║ Employee ║ EmployeeType ║   Type    ║
╠════════════╬══════════╬══════════════╬═══════════╣
║ 14/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 14/09/2015 ║ Silva    ║ Permanent    ║ Timesheet ║
║ 15/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 15/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 16/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 16/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 17/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 17/09/2015 ║ Airn     ║ Casual       ║ Timesheet ║
║ 17/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 18/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 18/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
╚════════════╩══════════╩══════════════╩═══════════╝

我不太了解SQL Server 2012 中的LEAD 命令。

Answer 1

您可以使用 Tally Table从 @start 和 @end 和 CROSS JOIN 构建日期与您的表以获得缺失的日期。

SQL Fiddle

DECLARE @start  DATE = '20150914',
        @end    DATE = '20150920'

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES
        (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
    )t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(DATEDIFF(DAY, @start, @end))
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
),
CteDates(dt) AS(
    SELECT  DATEADD(DAY, N-1, @start) FROM Tally
),
CteEmployee AS(
    SELECT DISTINCT Employee, EmployeeType 
    FROM yourTable
    WHERE EmployeeType = 'Permanent'
)
SELECT
    Date = d.dt,
    e.Employee,
    e.EmployeeType,
    Type = ISNULL(yt.Type, 'Missing')
FROM CteEmployee e
CROSS JOIN CteDates d
LEFT JOIN yourTable yt
    ON e.Employee = yt.Employee
    AND d.dt = yt.Date
WHERE
    ((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)

UNION ALL

SELECT * FROM yourTable WHERE EmployeeType = 'Casual'
ORDER BY Date, e.Employee

---

WHERE 子句

((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)

不考虑 @@DATEFIRST 的周末。

• Explanation on Tally Table (E1、E2、E4、理货)