SQL Server实现Maximo工单每日状态补全查询需求

核心思路 #

要解决缺失日期补全的问题，关键是先构建连续的日期序列，再为每个工单匹配对应日期及之前的最后一次状态变更记录，以此填充无变更日期的状态。

具体实现步骤 #

1. 生成近2年的连续日期表 #

用递归CTE生成从当前日期往前推2年到当前的所有日期，确保日期无断层：

WITH DateRange AS (
    SELECT CAST(DATEADD(YEAR, -2, GETDATE()) AS DATE) AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE [Date] < CAST(GETDATE() AS DATE)
)
SELECT [Date] INTO #TempDates FROM DateRange OPTION (MAXRECURSION 0);

注：MAXRECURSION 0用于解除递归次数限制，2年约730天，远低于默认限制，但加上更稳妥。

2. 获取所有工单列表 #

从wostatus表中提取唯一工单ID，确保每个工单都能覆盖所有日期：

SELECT DISTINCT workorderid INTO #TempWorkOrders FROM wostatus;

3. 匹配每个工单的每日最新状态 #

通过交叉连接生成所有工单+日期的组合，再关联wostatus表找到每个日期前的最后一次状态变更：

方式一：窗口函数实现

WITH WorkOrderDailyStatus AS (
    SELECT
        t.workorderid,
        d.[Date],
        -- 取当前日期及之前最新的状态ID
        LAST_VALUE(ws.wostatusid) OVER (
            PARTITION BY t.workorderid, d.[Date]
            ORDER BY ws.changedate DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS latest_wostatusid,
        -- 取当前日期及之前最新的状态描述
        LAST_VALUE(ws.statusdesc) OVER (
            PARTITION BY t.workorderid, d.[Date]
            ORDER BY ws.changedate DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS latest_statusdesc
    FROM #TempWorkOrders t
    CROSS JOIN #TempDates d
    LEFT JOIN wostatus ws
        ON t.workorderid = ws.workorderid
        AND CAST(ws.changedate AS DATE) <= d.[Date]
)
-- 去重，每个工单每日仅保留一条状态记录
SELECT DISTINCT workorderid, [Date], latest_wostatusid, latest_statusdesc
FROM WorkOrderDailyStatus
ORDER BY workorderid, [Date];

方式二：关联子查询实现（更直观）

SELECT
    t.workorderid,
    d.[Date],
    -- 子查询取当前日期前最新的状态ID
    (SELECT TOP 1 wostatusid FROM wostatus ws
     WHERE ws.workorderid = t.workorderid AND CAST(ws.changedate AS DATE) <= d.[Date]
     ORDER BY ws.changedate DESC) AS latest_wostatusid,
    -- 子查询取当前日期前最新的状态描述
    (SELECT TOP 1 statusdesc FROM wostatus ws
     WHERE ws.workorderid = t.workorderid AND CAST(ws.changedate AS DATE) <= d.[Date]
     ORDER BY ws.changedate DESC) AS latest_statusdesc
FROM #TempWorkOrders t
CROSS JOIN #TempDates d
ORDER BY t.workorderid, d.[Date];

4. 清理临时表 #

如果不需要保留临时表，执行：

DROP TABLE #TempDates;
DROP TABLE #TempWorkOrders;

关键说明 #

• 连续日期表是补全缺失日期的核心前提，没有它就无法覆盖无状态变更的日期。
• 无论用窗口函数还是子查询，核心逻辑都是找到每个工单在指定日期前的最后一次状态变更记录，确保无变更日期继承最近的状态。
• 若wostatus表数据量较大，建议给workorderid和changedate建立联合索引，可大幅提升查询效率。

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