嘿，我看你在写SQL查询来标记工单的逾期状态时遇到了问题，咱们一步步来排查和修正：

1. 先修正StatusName的拼写错误 #

你的需求里是要匹配('Open','Hold')这两个状态，但当前CASE语句里写的是'Onhold'——这个拼写差异会导致正确的状态无法被匹配到，这应该是第一个需要调整的地方：

CASE WHEN SD.STATUSNAME IN ('Open','Hold') 

2. 确认时间比较的逻辑合理性 #

你现在直接用原始的时间戳字段wrd.DUEBYTIME < wrd.COMPLETEDTIME做比较，其实是没问题的，因为时间戳数值越大代表时间越晚，和转换后的DateTime比较结果一致。不过如果想让逻辑更直观，也可以用转换后的DateTime来比较，但要注意SQL Server里不能在同级别SELECT的CASE中直接引用别名，所以可以用CTE来重构查询，让代码更清晰：

用CTE优化后的查询结构 #

WITH WorkOrderCTE AS (
    SELECT 
        WRD.WORKORDERID AS REQUEST_ID,
        AU.FIRST_NAME AS TECHNICIAN,
        wrd.TITLE,
        CAT.CATEGORYNAME,
        -- 转换后的时间字段，方便后续引用
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (wrd.CREATEDTIME / 1000), '1970-01-01 00:00:00') AS "Created Time",
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (wrd.COMPLETEDTIME / 1000), '1970-01-01 00:00:00') AS "Completed Time",
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (WRDS.ASSIGNEDTIME / 1000), '1970-01-01 00:00:00') AS "Assigned Time",
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (WRD.DUEBYTIME / 1000), '1970-01-01 00:00:00') AS "Due by",
        AUSR.FIRST_NAME AS Requester_name,
        MD.MODENAME AS MODE_OF_REQUEST,
        DPD.DEPTNAME,
        SD.STATUSNAME,
        -- 保留原始时间戳用于比较（也可以直接用转换后的字段）
        WRD.DUEBYTIME,
        WRD.COMPLETEDTIME
    FROM WorkOrder WRD 
    LEFT JOIN AaaUser AUSR ON AUSR.USER_ID = WRD.REQUESTERID 
    LEFT JOIN ModeDefinition MD ON MD.MODEID = WRD.MODEID 
    LEFT JOIN DepartmentDefinition DPD ON DPD.DEPTID = WRD.DEPTID 
    LEFT JOIN WorkOrderStates WRDS ON WRDS.WORKORDERID = WRD.WORKORDERID 
    LEFT JOIN StatusDefinition SD ON SD.STATUSID = WRDS.STATUSID 
    LEFT JOIN CategoryDefinition CAT ON CAT.CATEGORYID = WRDS.CATEGORYID 
    LEFT JOIN AaaUser AU ON AU.USER_ID = WRDS.OWNERID
)
SELECT 
    CASE 
        WHEN STATUSNAME IN ('Open','Hold') AND "Due by" < "Completed Time" THEN 'Over Due' 
        ELSE 'Not Over Due' 
    END AS OVER_DUE_STATUS,
    REQUEST_ID,
    TECHNICIAN,
    TITLE,
    CATEGORYNAME,
    "Created Time",
    "Completed Time",
    "Assigned Time",
    "Due by",
    Requester_name,
    MODE_OF_REQUEST,
    DEPTNAME,
    STATUSNAME
FROM WorkOrderCTE;

3. 处理NULL值的边界情况 #

还有个需要注意的点：如果工单状态是Open或Hold，但COMPLETEDTIME还没被填充（也就是为NULL），那么wrd.DUEBYTIME < wrd.COMPLETEDTIME会返回UNKNOWN，从而走到ELSE分支标记为'Not Over Due'，这可能不符合你的预期。如果这种情况下你想单独标记（比如'Not Completed'），可以嵌套CASE来处理：

CASE 
    WHEN SD.STATUSNAME IN ('Open','Hold') THEN
        CASE 
            WHEN wrd.COMPLETEDTIME IS NULL THEN 'Not Completed'
            WHEN wrd.DUEBYTIME < wrd.COMPLETEDTIME THEN 'Over Due'
            ELSE 'Not Over Due'
        END
    ELSE 'Not Applicable' -- 非Open/Hold状态可以标记为不适用
END AS OVER_DUE_STATUS

最终修正后的完整查询 #

结合上面的所有调整，这里给出最终的完整查询语句，包含拼写修正和NULL值处理：

SELECT 
    CASE 
        WHEN SD.STATUSNAME IN ('Open','Hold') THEN
            CASE 
                WHEN wrd.COMPLETEDTIME IS NULL THEN 'Not Completed'
                WHEN wrd.DUEBYTIME < wrd.COMPLETEDTIME THEN 'Over Due'
                ELSE 'Not Over Due'
            END
        ELSE 'Not Applicable'
    END AS OVER_DUE_STATUS,
    wrd.WORKORDERID AS REQUEST_ID,
    AU.FIRST_NAME AS TECHNICIAN,
    wrd.TITLE,
    CAT.CATEGORYNAME,
    DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (wrd.CREATEDTIME / 1000), '1970-01-01 00:00:00') AS "Created Time",
    DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (wrd.COMPLETEDTIME / 1000), '1970-01-01 00:00:00') AS "Completed Time",
    DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (WRDS.ASSIGNEDTIME / 1000), '1970-01-01 00:00:00') AS "Assigned Time",
    DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (WRD.DUEBYTIME / 1000), '1970-01-01 00:00:00') AS "Due by",
    AUSR.FIRST_NAME AS Requester_name,
    MD.MODENAME AS MODE_OF_REQUEST,
    DPD.DEPTNAME,
    SD.STATUSNAME
FROM WorkOrder WRD 
LEFT JOIN AaaUser AUSR ON AUSR.USER_ID = WRD.REQUESTERID 
LEFT JOIN ModeDefinition MD ON MD.MODEID = WRD.MODEID 
LEFT JOIN DepartmentDefinition DPD ON DPD.DEPTID = WRD.DEPTID 
LEFT JOIN WorkOrderStates WRDS ON WRDS.WORKORDERID = WRD.WORKORDERID 
LEFT JOIN StatusDefinition SD ON SD.STATUSID = WRDS.STATUSID 
LEFT JOIN CategoryDefinition CAT ON CAT.CATEGORYID = WRDS.CATEGORYID 
LEFT JOIN AaaUser AU ON AU.USER_ID = WRDS.OWNERID;

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