SQL Server技术求助：如何生成无记录日期的缺失记录

要实现“某日期无对应记录时自动创建缺失行”的需求，核心是先生成所有需要覆盖的日期范围，再结合用户/部门的维度组合得到所有“应该存在”的记录，最后通过左连接原视图来补全缺失的行。下面是具体的实现步骤和代码：

1. 生成完整的日期范围 #

首先用递归CTE生成你需要覆盖的所有日期（这里默认从视图中最早的日期到最晚的日期，也可以换成固定起止日期）：

WITH DateRange AS (
    -- 从VW_HORARIOS中获取最早的日期作为起始
    SELECT (SELECT MIN(Fecha) FROM VW_HORARIOS) AS Fecha
    UNION ALL
    -- 逐天递增加1
    SELECT DATEADD(DAY, 1, Fecha)
    FROM DateRange
    -- 到VW_HORARIOS中最晚的日期结束
    WHERE Fecha < (SELECT MAX(Fecha) FROM VW_HORARIOS)
),

2. 获取唯一的用户/部门维度组合 #

从原视图中提取所有不重复的用户+部门相关字段，确保每个组合在每个日期都能生成一条记录：

UserDepartments AS (
    SELECT DISTINCT 
        IdDepartment, 
        IdParent, 
        Localidad, 
        Codigo, 
        Nombre, 
        Departamento
    FROM VW_HORARIOS
)

3. 交叉连接+左连接补全缺失记录 #

把日期范围和用户/部门维度交叉连接，得到所有应存在的记录，再左连接原视图，最后处理字段值和筛选条件：

SELECT 
    ud.IdDepartment,
    ud.IdParent,
    ud.Localidad,
    ud.Codigo,
    ud.Nombre,
    ud.Departamento,
    dr.Fecha,
    -- 缺失记录时设为相等的默认值（比如空字符串），满足原查询的筛选条件
    ISNULL(vh.[Registro Entrada], '') AS [Registro Entrada],
    ISNULL(vh.[Registro Salida], '') AS [Registro Salida],
    -- 处理Novedades字段：有匹配的Exception记录则取描述，否则显示'Ausente'
    CASE
        WHEN vh.Codigo IS NOT NULL AND EXISTS (
            SELECT 1 FROM Exception 
            WHERE IdUser = vh.Codigo 
              AND vh.Fecha BETWEEN BeginingDate AND EndingDate
        ) THEN (
            SELECT Description FROM Exception 
            WHERE IdUser = vh.Codigo 
              AND vh.Fecha BETWEEN BeginingDate AND EndingDate
        )
        ELSE 'Ausente'
    END AS Novedades
FROM DateRange dr
-- 交叉连接得到每个日期+每个用户/部门的组合
CROSS JOIN UserDepartments ud
-- 左连接原视图，匹配已存在的记录
LEFT JOIN VW_HORARIOS vh
    ON ud.IdDepartment = vh.IdDepartment
    AND ud.IdParent = vh.IdParent
    AND ud.Localidad = vh.Localidad
    AND ud.Codigo = vh.Codigo
    AND ud.Nombre = vh.Nombre
    AND ud.Departamento = vh.Departamento
    AND dr.Fecha = vh.Fecha
-- 保留原查询的筛选逻辑：只显示Registro Entrada和Salida相等的行（包括我们补的默认值）
WHERE ISNULL(vh.[Registro Entrada], '') = ISNULL(vh.[Registro Salida], '')
ORDER BY dr.Fecha, ud.Departamento, ud.Codigo
-- 如果日期范围超过100天，必须加这个选项解除递归限制
OPTION (MAXRECURSION 0);

关键注意事项 #

• 如果你的日期跨度超过100天，一定要加上OPTION (MAXRECURSION 0)，否则SQL Server会因为默认递归次数限制报错。
• 如果你需要只包含当前活跃的用户/部门，而不是所有在视图中出现过的组合，可以把UserDepartments的数据源换成专门的用户表或部门表，而不是从VW_HORARIOS取。
• 可以根据实际需求调整日期范围的起止，比如换成固定的日期（如CAST('2024-01-01' AS DATE)）来覆盖特定时间段。

内容的提问来源于stack exchange，提问作者Frankenstainero