如何计算带过期日期的代金券余额？

核心逻辑：每笔使用记录只能抵扣当前日期已添加且未过期的代金券总额，若使用总额超过有效代金券总额，余额为负（即用户欠费）。

以下是修正后的SQL查询：

WITH AllEvents AS (
    -- 合并代金券添加和使用记录，统一事件格式
    SELECT 
        SubscriptionID,
        AddedOn AS EventDate,
        Cost AS Amount,
        'VOUCHER' AS EventType,
        Expiry
    FROM Vouchers
    UNION ALL
    SELECT 
        SubscriptionID,
        Date AS EventDate,
        -Price AS Amount,
        'USAGE' AS EventType,
        NULL AS Expiry
    FROM Usages
),
OrderedEvents AS (
    -- 按订阅ID和事件日期排序，确保同日期先处理代金券添加再处理使用
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY SubscriptionID ORDER BY EventDate, EventType DESC) AS Seq
    FROM AllEvents
),
ValidBalanceCalculations AS (
    SELECT 
        oe.SubscriptionID,
        oe.EventDate,
        oe.Amount,
        oe.EventType,
        -- 计算当前事件日期及之前所有有效代金券的总额
        (SELECT SUM(Cost) 
         FROM Vouchers v 
         WHERE v.SubscriptionID = oe.SubscriptionID 
           AND v.AddedOn <= oe.EventDate 
           AND (v.Expiry IS NULL OR v.Expiry >= oe.EventDate)) AS TotalValidVouchers,
        -- 计算当前事件日期及之前所有使用记录的总金额
        (SELECT SUM(Price) 
         FROM Usages u 
         WHERE u.SubscriptionID = oe.SubscriptionID 
           AND u.Date <= oe.EventDate) AS TotalUsages
    FROM OrderedEvents oe
)
SELECT 
    SubscriptionID,
    EventDate AS Date,
    Amount,
    -- 计算实际余额：有效代金券总额减去使用总额，若使用超额则为负
    CASE 
        WHEN TotalUsages <= TotalValidVouchers THEN TotalValidVouchers - TotalUsages
        ELSE -(TotalUsages - TotalValidVouchers)
    END AS Balance
FROM ValidBalanceCalculations
ORDER BY SubscriptionID, Seq;

查询结果说明 #

针对你的示例数据，执行后会得到以下结果：

可以看到，2000-02-28的30美元使用记录因第二张代金券已过期，无法抵扣该代金券额度，最终余额为-50，符合你的预期。

内容的提问来源于stack exchange，提问作者Yisroel M. Olewski