按任意月起止日对 Orders table 进行 Billing Cycle 分区

结论

先把 Bill_date 向前平移 25 天，再根据平移后的自然月确定账期。

按这种方式计算：

• 每月 26 日平移后是当月 1 日。
• 次月 25 日平移后仍属于同一个自然月。
• 同一段“26 日至次月 25 日”账期内的记录，平移后都会落在同一个自然月内。

返回订单中实际存在的账期

SELECT DISTINCT
       c.cycle_start AS start_date,
       DATEADD(DAY, -1, DATEADD(MONTH, 1, c.cycle_start)) AS end_date
FROM dbo.orders AS o
CROSS APPLY
(
    VALUES
    (
        DATEADD(
            DAY,
            25,
            DATEADD(
                MONTH,
                DATEDIFF(MONTH, 0, DATEADD(DAY, -25, o.Bill_date)),
                0
            )
        )
    )
) AS c(cycle_start)
WHERE o.Bill_date IS NOT NULL
ORDER BY start_date;

计算结果如下：

下面的表达式用于取得平移后月份的第一天：

DATEADD(
    MONTH,
    DATEDIFF(MONTH, 0, DATEADD(DAY, -25, o.Bill_date)),
    0
)

在这个日期上增加 25 天，即可得到账期开始日期。下一个账期的开始日期减去一天，就是当前账期的结束日期。

将每条订单划分到对应账期

如果要保留订单明细，并为每条记录附上所属账期，可以这样写：

SELECT o.*,
       c.cycle_start AS start_date,
       DATEADD(DAY, -1, DATEADD(MONTH, 1, c.cycle_start)) AS end_date
FROM dbo.orders AS o
CROSS APPLY
(
    VALUES
    (
        DATEADD(
            DAY,
            25,
            DATEADD(
                MONTH,
                DATEDIFF(MONTH, 0, DATEADD(DAY, -25, o.Bill_date)),
                0
            )
        )
    )
) AS c(cycle_start)
WHERE o.Bill_date IS NOT NULL;

按账期汇总

下面的查询按账期统计订单数量、商品数量和客户费用：

SELECT c.cycle_start AS start_date,
       DATEADD(DAY, -1, DATEADD(MONTH, 1, c.cycle_start)) AS end_date,
       COUNT(*) AS order_count,
       SUM(o.Quantity) AS total_quantity,
       SUM(o.Customer_cost) AS total_customer_cost
FROM dbo.orders AS o
CROSS APPLY
(
    VALUES
    (
        DATEADD(
            DAY,
            25,
            DATEADD(
                MONTH,
                DATEDIFF(MONTH, 0, DATEADD(DAY, -25, o.Bill_date)),
                0
            )
        )
    )
) AS c(cycle_start)
WHERE o.Bill_date IS NOT NULL
GROUP BY c.cycle_start
ORDER BY c.cycle_start;

日期时间边界应使用半开区间

Bill_date 是 datetime 类型，而计算出的 end_date 通常是 25 日的 00:00:00。因此，不要直接使用以下条件：

Bill_date BETWEEN @start_date AND @end_date

这种写法会漏掉 25 日零点之后的订单，例如 2020-08-25 14:30:00。

应使用“包含开始时间、不包含下一个账期开始时间”的半开区间：

WHERE Bill_date >= @cycle_start
  AND Bill_date <  DATEADD(MONTH, 1, @cycle_start)

例如，查询 2020-07-26 至 2020-08-25 这一账期：

DECLARE @cycle_start datetime = '20200726';

SELECT *
FROM dbo.orders
WHERE Bill_date >= @cycle_start
  AND Bill_date < DATEADD(MONTH, 1, @cycle_start);

这个条件可以覆盖 25 日全天，也能避免因 datetime 时间精度造成边界遗漏。

如果大表需要频繁按账期分组，可以考虑添加持久化计算列并建立索引。按日期范围查询时，则应直接比较 Bill_date，避免在 WHERE 条件中对该列执行 DATEADD 等函数，让 SQL Server 更容易利用日期索引。

备注：内容仅供参考。