Payment Schedule 支付周期与例外日期表设计

2026-10-24支付小周

结论

整体思路合理，但不建议在 PaymentSchedule 中无限生成未来周期，也不建议夜间任务只根据“今天是否符合每 14 天一次”来决定是否付款。

更合适的设计是：

PaymentSchedule 保存带版本的周期规则。
ExceptionDate 保存无法处理付款的日期范围。
PaymentCycle 保存已经生成的具体付款周期及其执行状态，供审计、重试和防止重复付款使用。
系统按需计算或滚动生成近期周期，不保存无限期的未来数据。
每个周期保存以下日期：
- 周期开始日期；
- 周期结束日期；
- 原计划付款日期；
- 调整后的实际付款日期。
夜间任务查找“实际付款日期已到，但尚未执行”的周期，而不是只检查当天是否为正常 trigger date。

采用“规则 + 有限实例”的方式，既不会无限生成数据，也能处理节假日、停工期、规则切换、失败重试和历史审计。

先统一日期含义

当前设计最容易出错的地方是 EffectiveDate 的语义。

如果 EffectiveDate = 1 June 表示第一个付款周期的开始日期，周期长度为 14 天，那么：

PeriodStart         = 1 June
PeriodEnd           = 14 June
NominalPaymentDate  = 14 June
NextPeriodStart     = 15 June
NextPeriodEnd       = 28 June

下一个 14 天周期应为 15 June 至 28 June，而不是 29 June。如果周期包含结束日期，15 至 29 实际共有 15 天。

计算公式是：

NominalPaymentDate = PeriodStart + PeriodDays - 1
NextPeriodStart    = PeriodStart + PeriodDays

因此，当 EffectiveDate 表示周期开始日期时，第一个付款日不能写成：

DATEADD(day, 14, EffectiveDate)

应该写成：

DATEADD(day, EffectiveDays - 1, EffectiveDate)

也可以直接将锚点定义为“第一个正常付款日”。这样 modulo 判断会更简单，但周期开始日期需要反向计算。这两种定义都可行，重点是数据库字段必须始终只有一个明确含义。

判断某天是否为正常付款日

假设：

EffectiveDate 是第一个周期的开始日期；
EffectiveDays 是周期长度；
周期结束日也是正常付款日。

可以用下面的 SQL 判断某个日期是否为正常付款日：

DECLARE @Today date = CAST(GETDATE() AS date);

SELECT
    ps.PaymentScheduleId,
    ps.EffectiveDate,
    ps.EffectiveDays
FROM dbo.PaymentSchedule AS ps
CROSS APPLY
(
    SELECT
        DATEADD(day, ps.EffectiveDays - 1, ps.EffectiveDate)
            AS FirstPaymentDate
) AS d
WHERE @Today >= d.FirstPaymentDate
  AND DATEDIFF(day, d.FirstPaymentDate, @Today)
        % ps.EffectiveDays = 0;

核心条件是：

DATEDIFF(day, FirstPaymentDate, @Today) % EffectiveDays = 0

以 1 June 为周期开始日期、周期长度为 14 天为例：

FirstPaymentDate = DATEADD(day, 13, '2024-06-01')
                 = '2024-06-14'

后续正常付款日为：

14 June
28 June
12 July
26 July
...

不过，这个判断只能说明“今天是不是原计划付款日”，不能单独解决提前付款问题。在停工示例中，系统必须在 9 日处理原定于 14 日执行的周期。

不要用“今天的 active schedule”直接决定提前付款

正常付款时，当前日期与付款周期基本一致。提前付款时，两者未必一致。

例如：

PeriodStart          = 1 June
PeriodEnd            = 14 June
NominalPaymentDate   = 14 June
Exception            = 10 June 至 18 June
ActualPaymentDate    = 9 June

任务在 9 日运行时，需要判断的是：

哪些付款周期经过例外日期调整后，应当在今天执行？

正确的处理顺序如下：

确定付款周期。
计算该周期的 NominalPaymentDate。
根据例外日期计算 ActualPaymentDate。
执行 ActualPaymentDate <= Today 且尚未处理的周期。

如果先判断“今天是否符合每 14 天一次”，9 日就不会被识别为 trigger date。

建议的 Schedule 表

可以在现有表中增加版本有效范围：

CREATE TABLE dbo.PaymentSchedule
(
    PaymentScheduleId  int IDENTITY(1, 1) NOT NULL,
    EffectiveFrom      date NOT NULL,
    EffectiveTo        date NULL,
    PeriodDays         smallint NOT NULL,
    Description        varchar(100) NULL,

    CONSTRAINT PK_PaymentSchedule
        PRIMARY KEY (PaymentScheduleId),

    CONSTRAINT CK_PaymentSchedule_PeriodDays
        CHECK (PeriodDays > 0),

    CONSTRAINT CK_PaymentSchedule_DateRange
        CHECK (EffectiveTo IS NULL OR EffectiveTo > EffectiveFrom)
);

字段含义约定如下：

EffectiveFrom：该规则下第一个付款周期的开始日期。
EffectiveTo：排他上界，也就是新规则第一个周期的开始日期。
PeriodDays：周期长度，例如 14。
当前规则通常只有一条记录满足 EffectiveTo IS NULL。

例如：

旧规则：
EffectiveFrom = 2024-06-01
EffectiveTo   = 2024-09-02
PeriodDays    = 14

新规则：
EffectiveFrom = 2024-09-02
EffectiveTo   = NULL
PeriodDays    = 14

新规则的 EffectiveFrom 必须位于明确的周期边界上。不能因为希望付款日改为 Monday，就随意插入一个 Monday。需要先明确旧周期在哪里结束、新周期在哪里开始，以及切换时是否允许缩短或延长过渡周期。

可以这样选择某个周期对应的规则：

SELECT TOP (1) *
FROM dbo.PaymentSchedule
WHERE EffectiveFrom <= @PeriodStart
  AND
  (
      EffectiveTo IS NULL
      OR @PeriodStart < EffectiveTo
  )
ORDER BY EffectiveFrom DESC, PaymentScheduleId DESC;

如果只想查询今天名义上生效的规则，可以将 @PeriodStart 换成 @Today。但在执行提前付款时，应根据周期或名义付款日期确定规则，不能简单地按实际付款日选择规则。

应用层或负责写入的存储过程还必须保证 schedule 版本之间没有重叠。普通 CHECK 约束无法直接阻止不同记录的日期范围相互重叠。

ExceptionDate 表可以保留，但要增加约束

例外日期范围的设计本身没有问题，建议至少增加范围约束：

CREATE TABLE dbo.ExceptionDate
(
    ExceptionDateId int IDENTITY(1, 1) NOT NULL,
    StartDate       date NOT NULL,
    EndDate         date NOT NULL,
    Description     varchar(100) NOT NULL,

    CONSTRAINT PK_ExceptionDate
        PRIMARY KEY (ExceptionDateId),

    CONSTRAINT CK_ExceptionDate_DateRange
        CHECK (EndDate >= StartDate)
);

如果原计划付款日落在例外范围内：

NominalPaymentDate BETWEEN StartDate AND EndDate

最简单的提前付款日期是：

DATEADD(day, -1, StartDate)

示例查询如下：

SELECT
    @ActualPaymentDate =
        CASE
            WHEN e.ExceptionDateId IS NULL
                THEN @NominalPaymentDate
            ELSE DATEADD(day, -1, e.StartDate)
        END
FROM (VALUES (1)) AS source(Dummy)
OUTER APPLY
(
    SELECT TOP (1)
        ExceptionDateId,
        StartDate,
        EndDate
    FROM dbo.ExceptionDate
    WHERE @NominalPaymentDate BETWEEN StartDate AND EndDate
    ORDER BY StartDate
) AS e;

这段逻辑只适用于较简单的情况：例外范围互不重叠，而且 StartDate - 1 一定能够处理付款。

实际系统还要考虑以下情况：

停工期前一天也是 public holiday；
停工期前一天是周末，并且周末不能付款；
多条例外范围重叠或首尾相接；
新增例外日期后，需要重新计算待处理周期。

因此，更可靠的做法是维护一张处理日历。

使用处理日历计算实际付款日

处理日历并不是预先生成所有付款周期。它只记录每天是否可以处理付款，通常按年滚动维护即可。

CREATE TABLE dbo.ProcessingCalendar
(
    CalendarDate     date NOT NULL,
    IsProcessingDay  bit NOT NULL,
    Reason            varchar(100) NULL,

    CONSTRAINT PK_ProcessingCalendar
        PRIMARY KEY (CalendarDate)
);

正常工作日的 IsProcessingDay 设为 1，周末、public holiday 和 shut down period 设为 0。

实际付款日期可以直接取“不晚于原付款日的最后一个可处理日期”：

SELECT TOP (1)
    @ActualPaymentDate = CalendarDate
FROM dbo.ProcessingCalendar
WHERE CalendarDate <= @NominalPaymentDate
  AND IsProcessingDay = 1
ORDER BY CalendarDate DESC;

对于原定 14 日付款、10 日至 18 日停工的情况，14 日无法处理，查询便会向前找到 9 日。

这种方式也能处理连续假期、周末和多个相邻例外，业务代码不必反复执行“减一天再检查”。

如果周末仍允许处理付款，只需将日历中的相应日期设为可处理日。具体设置取决于业务规则，不能因为存在 public holiday，就推断周末一定不能处理付款。

增加具体的 PaymentCycle，而不是空泛的 History 表

现有 PaymentScheduleHistory 只保存 PaymentScheduleId，无法回答这些问题：

这次付款覆盖哪个周期？
原计划在哪天支付？
最终在哪天支付？
是否已经执行？
是否因某个停工期而提前？
任务失败后是否需要重试？
同一个周期是否已被重复创建？

建议将其改为具体的付款周期表：

CREATE TABLE dbo.PaymentCycle
(
    PaymentCycleId       bigint IDENTITY(1, 1) NOT NULL,
    PaymentScheduleId    int NOT NULL,
    PeriodStart          date NOT NULL,
    PeriodEnd            date NOT NULL,
    NominalPaymentDate   date NOT NULL,
    ActualPaymentDate    date NOT NULL,
    Status               varchar(20) NOT NULL,
    CreatedAtUtc         datetime2(0) NOT NULL,
    ExecutedAtUtc        datetime2(0) NULL,

    CONSTRAINT PK_PaymentCycle
        PRIMARY KEY (PaymentCycleId),

    CONSTRAINT FK_PaymentCycle_PaymentSchedule
        FOREIGN KEY (PaymentScheduleId)
        REFERENCES dbo.PaymentSchedule(PaymentScheduleId),

    CONSTRAINT CK_PaymentCycle_DateRange
        CHECK (PeriodEnd >= PeriodStart),

    CONSTRAINT CK_PaymentCycle_Status
        CHECK (Status IN ('Pending', 'Processing', 'Completed', 'Failed')),

    CONSTRAINT UQ_PaymentCycle_Period
        UNIQUE (PeriodStart, PeriodEnd)
);

虽然 NominalPaymentDate 和 ActualPaymentDate 可以重新计算，但将它们保存在周期记录中更便于审计。例外日期后来发生变化时，已经执行的付款不应被新的规则重新解释。

夜间任务应查询：

DECLARE @Today date = CAST(GETDATE() AS date);

SELECT *
FROM dbo.PaymentCycle
WHERE ActualPaymentDate <= @Today
  AND Status IN ('Pending', 'Failed')
ORDER BY ActualPaymentDate, PaymentCycleId;

这里必须使用 <=，不能使用 =。如果夜间任务某天没有运行，下次启动时仍能补处理遗漏的周期。

是否需要预先生成付款周期

不需要无限预生成。但如果完全不保存周期实例，运行逻辑、审计和重试都会变得复杂。

更实用的做法是滚动生成：

PaymentSchedule 永久保存规则版本；
PaymentCycle 只生成未来一段可控时间内的周期；
定时任务持续补充后续周期；
已完成实际支付的周期永久保留；
生成范围覆盖当前已知的最长停工期和业务提前处理窗口。

具体生成多远由业务配置决定，没有统一答案。可以生成未来数月，也可以生成未来一年。期限不是重点，生成任务必须能够重复执行，并通过唯一约束避免重复创建同一周期。

如果不想预生成，也可以在每次运行时即时计算候选周期，但查询必须覆盖未来日期。任务在 9 日运行时，需要发现原定于 14 日付款的周期。查询窗口至少要覆盖已经登记的未来例外范围，否则仍会漏掉需要提前执行的付款。

Schedule 变更时的处理原则

将付款日从 Wednesday 改为 Monday，不能只增加一条新记录。切换前需要确定：

旧规则最后一个周期的起止日期。
新规则第一个周期从哪天开始。
两个周期之间是否连续。
是否允许出现不满或超过 14 天的过渡周期。
已经生成但尚未支付的周期是否按新规则重建。

建议采用以下规则：

新 schedule 只能从新的周期边界开始；
已完成周期永不修改；
已创建但尚未执行的未来周期可以取消并重新生成；
新旧版本之间不能存在日期重叠或空档；
schedule 版本按周期归属选择，不按提前后的实际付款日选择。

如果业务要求“从某个 Monday 起立即改变付款日”，而该日期并非旧周期边界，就应该明确创建一个特殊过渡周期，不能依靠 modulo 公式隐式推导。

夜间任务的推荐流程

一个完整的夜间任务可以按以下顺序运行：

读取有效的 schedule 版本。
补充生成近期尚不存在的 PaymentCycle。
根据 ProcessingCalendar 计算待处理周期的 ActualPaymentDate。
查询 ActualPaymentDate <= Today 且尚未完成的周期。
在事务中将周期标记为 Processing。
汇总 PeriodStart 至 PeriodEnd 范围内的付款数据。
创建付款并记录唯一业务编号。
成功后标记为 Completed，失败则标记为 Failed。
重试时复用同一周期和业务编号，避免重复付款。

实际支付是高风险操作，数据库中还应设置唯一约束或幂等键。只依靠任务状态检查，无法彻底避免两个任务实例并发处理同一周期。

“今天”也应按明确的业务时区计算，不能默认使用数据库服务器的本地时间或 UTC 日期，否则可能在午夜附近选错付款日。

trigger date 不应只是一个针对“今天”的公式，而应是每个付款周期的属性。先得到正常付款日，再通过处理日历确定实际付款日，最后执行所有已经到期且尚未完成的周期。这样处理后，schedule 变化与 exception dates 才不会相互干扰。