SQL Server 2014订单自动锁定逻辑优化：非工作日场景处理

核心规则 #

• 订单类型：Green、Blue、Purple
• 确认日期：首次向客户发送确认的日期
• 订单类型小时偏移：Green=48小时、Blue=24小时、Purple=0小时
• 办公日历：标记日期是否为工作日（ocIN=1为工作日，0为非工作日）

现有问题 #

当前工作日场景下流程运行正常，但以下场景无法自动处理，需人工介入：

• 确认日期+小时偏移后为非工作日（ocIN=0）
• 确认日期本身为非工作日（ocIN=0）

目标是实现全流程自动化，无需人工判定逾期并手动操作。

现有可正常运行的工作日代码 #

IF EXISTS(SELECT oc.ocDate
              FROM dbo.OfficeCalendar oc
              WHERE oc.ocID = 1
                  AND CONVERT(DATE,oc.ocDate) = CONVERT(DATE,GETDATE()))
    BEGIN
    CREATE TABLE #OfficeDays (id INT IDENTITY
                              ,offDate DATETIME
                              ,offIN INT NULL)
    INSERT INTO #OfficeDays 
    SELECT TOP 14 oc.ocDate
                  ,oc.ocIN
    FROM dbo.OfficeCalendar oc
    WHERE oc.ocID = 1
       AND oc.ocDate <= GETDATE()
    ORDER BY oc.ocDate DESC
    
    --UPDATE odr
    --SET odr.status = 3 -- Move order to Locked
    
    FROM (SELECT DISTINCT odr.OrderID
                          ,DATEADD(HOUR,oto.otoHoursOffset,aa.[Conf Date]) AS [Conf Date + Offset]
                          ,ot.typeCode
          FROM dbo.Orders odr
          JOIN dbo.Types AS ot ON ot.otID = odr.otID
          JOIN dbo.OfficeTableOffset AS oto ON oto.otoOrderType = ot.otCode
          JOIN dbo.OrderStatus AS os ON os.osID = odr.osID
          --
          OUTER APPLY(SELECT MIN(h.odrChangeDate) AS [Conf Date]
                      FROM dbo.OrderHistoryLog AS h
                      JOIN dbo.OrderStatus AS os ON os.osID = h.osID
                      WHERE h.odrID = odr.odrID
                      AND h.osID = 2) aa
          --
          WHERE odr.osID = 2) AS result
    JOIN dbo.Orders AS odr1 ON odr1.odrID = CONVERT(DATETIME,result.odrID,120)
    JOIN dbo.Types AS ot1 ON ot1.otID = odr1.otID
    JOIN dbo.OfficeTableOffset AS oto1 ON oto1.otoOrderType = ot1.otCode
    JOIN #OfficeDays AS ocd ON ocd.offDate = CONVERT(DATETIME,LEFT(result.[Conf Date + Offset],11))
    --
    OUTER APPLY(SELECT TOP 1 ISNULL(SUM(oc.ocIN)*24,0) AS [OfficeHours]
                FROM #OfficeDays oc
                WHERE oc.ocDate BETWEEN result.[Conf Date] AND CONVERT(DATETIME,LEFT(GETDATE(),11))) bb
    --
    WHERE CONVERT(DATETIME,LEFT(result.[Conf Date + Offset],11)) <= CONVERT(DATETIME,LEFT(ocd.offDate,11))
             AND DATEPART(hh,result.[Conf Date + Offset]) < DATEPART(hh,GETDATE())
             AND bb.[OfficeHours] >= oto.otoHoursOffset
DROP TABLE #OfficeDays
END

相关表结构 #

OfficeTableOffset表 #

OfficeCalendar表 #

问题场景示例 #

• Green订单（+48小时）于21日确认，48小时覆盖22日和27日
• Green订单（+48小时）于23日确认，48小时覆盖27日和28日
• Blue订单（+24小时）于22日确认，24小时对应27日
• Blue订单（+24小时）于23日确认，24小时对应27日

示例数据 #

*实际锁定日期基于办公日历中ocIN=0的日期调整，**实际锁定日期将工作日（ocIN=1）计为24小时。

解决方案建议 #

核心思路：计算实际有效工作日时长，而非直接偏移小时
现有代码的问题在于直接对确认日期做小时偏移后匹配日历，忽略了非工作日不计入有效时长的规则。需改为累计计算从确认日期开始的有效办公小时数，直到达到订单类型对应的偏移小时数，以此确定实际锁定日期。

具体实现步骤 #

1. 预计算日历的累计有效小时
   为OfficeCalendar表生成累计有效小时字段，或在临时表中计算从最早日期到每个日期的累计办公小时数，减少后续重复计算。

2. 用递归CTE计算每个订单的实际锁定日期
   从确认日期开始，逐天累计有效办公小时，直到累计值达到订单的偏移小时数，对应的日期即为实际锁定日期。示例逻辑：

   WITH LockDateCalculation AS (
       SELECT 
           odr.odrID,
           aa.[Conf Date] AS CurrentDateTime,
           oto.otoHoursOffset AS RemainingHours,
           aa.[Conf Date] AS TempLockDate
       FROM dbo.Orders odr
       JOIN dbo.Types ot ON ot.otID = odr.otID
       JOIN dbo.OfficeTableOffset oto ON oto.otoOrderType = ot.otCode
       OUTER APPLY(SELECT MIN(h.odrChangeDate) AS [Conf Date]
                   FROM dbo.OrderHistoryLog h
                   WHERE h.odrID = odr.odrID AND h.osID = 2) aa
       WHERE odr.osID = 2
   
       UNION ALL
   
       SELECT 
           l.odrID,
           DATEADD(DAY, 1, l.CurrentDateTime),
           l.RemainingHours - (CASE WHEN oc.ocIN = 1 THEN 24 ELSE 0 END),
           CASE 
               WHEN oc.ocIN = 1 AND l.RemainingHours <=24 THEN DATEADD(HOUR, l.RemainingHours, l.CurrentDateTime)
               WHEN oc.ocIN = 1 THEN DATEADD(DAY, 1, l.CurrentDateTime)
               ELSE l.TempLockDate
           END
       FROM LockDateCalculation l
       JOIN dbo.OfficeCalendar oc ON CONVERT(DATE, oc.ocDate) = CONVERT(DATE, DATEADD(DAY, 1, l.CurrentDateTime))
       WHERE l.RemainingHours > 0
   )
   SELECT odrID, MAX(TempLockDate) AS ActualLockDate
   FROM LockDateCalculation
   WHERE RemainingHours <= 0
   GROUP BY odrID
   OPTION (MAXRECURSION 100)
   

3. 调整主逻辑的判断条件
   替换原代码中基于直接偏移日期的判断，改为对比当前日期时间是否大于等于计算出的实际锁定日期，同时确保当前时间超过锁定日期的小时部分。

4. 处理确认日期为非工作日的情况
   如果确认日期是非工作日（ocIN=0），则从确认日期之后的第一个工作日开始累计有效小时数。

优化点 #

• 避免使用LEFT(result.[Conf Date + Offset],11)字符串转换，改用CONVERT(DATE, [Conf Date + Offset])更高效且不易出错。
• 临时表#OfficeDays可扩展为包含累计有效小时数，提升查询效率。
• 为递归CTE设置合理的MAXRECURSION值，避免无限循环。

内容的提问来源于stack exchange，提问作者Gary A MacDonald