如何基于多日期计算交易日期与取消日期的最小日差

现有表格Table A：

需要实现：

• 计算Cancel_Flag = No的记录中，Transaction_Date与最近（日差绝对值最小）的Cancel_Flag = Yes记录的Transaction_Date之间的日差
• Cancel_Flag = Yes的记录日差记为0

期望输出：

以下以SQL Server为例，提供实现代码：

WITH CancelRecords AS (
    SELECT ID, Transaction_Date AS CancelDate
    FROM TableA
    WHERE Cancel_Flag = 'Yes'
)
SELECT
    t.ID,
    t.Transaction_Date,
    t.Cancel_Flag,
    CASE
        WHEN t.Cancel_Flag = 'Yes' THEN 0
        ELSE (
            SELECT TOP 1 DATEDIFF(DAY, cr.CancelDate, t.Transaction_Date)
            FROM CancelRecords cr
            WHERE cr.ID = t.ID
            ORDER BY ABS(DATEDIFF(DAY, cr.CancelDate, t.Transaction_Date)) ASC
        )
    END AS Days_Since_Cancel
FROM TableA t
ORDER BY t.Transaction_Date, t.Cancel_Flag DESC;

代码说明： #

1. 提取取消记录：用CancelRecords临时表存储所有Cancel_Flag = Yes的记录，简化后续关联计算。
2. 日差计算逻辑：
   • 若当前记录是取消记录，直接返回0；
   • 若为非取消记录，关联取消记录表，计算当前日期与每个取消日期的日差，按日差绝对值升序排序后取第一条，即为最小日差。
3. 排序输出：按交易日期和取消标志排序，与期望输出顺序一致。

不同数据库适配： #

• MySQL：将DATEDIFF(DAY, cr.CancelDate, t.Transaction_Date)改为DATEDIFF(t.Transaction_Date, cr.CancelDate)，SELECT TOP 1改为SELECT ... LIMIT 1。
• PostgreSQL：将DATEDIFF(DAY, cr.CancelDate, t.Transaction_Date)改为(t.Transaction_Date - cr.CancelDate)::INTEGER，SELECT TOP 1改为SELECT ... LIMIT 1。

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