如何在SQL剩余剂量天数计算中实现单剂量购买后一年过期的规则

• 业务表结构样例：

USER|PURCHASE_DATE|DOSES
1111|2017-07-27|15
2222|2020-07-17|3
3333|2021-02-01|5

• 原有规则：不设置额外过期限制时，每剂可对应90天使用时长，原有SQL计算逻辑为：

SUM(DOSES)*90 - DATEDIFF(DAY, MIN(PURCHASE_DATE), GETDATE())

• 原有计算输出结果：

USER|DAYS_REMAINING
1111|0
2222|6
3333|385

新增过期规则：每剂自购买日起满1年即过期，调整逻辑后的预期计算结果为：

USER|DAYS_REMAINING
1111|-985
2222|6
3333|300

核心计算需要取两个维度的较小值：

1. 按使用速度计算的总剂量可支撑剩余时长（即原有SQL的计算结果）
2. 持有的剂量距离过期节点的剩余天数：由于原计算的时间基准锚定用户所有购买记录里的最早日期，对应的过期节点即为最早购买日加1年——最早购买的批次过期时间最早，决定了所有持有剂量的最长可使用期限

两个维度取较小值后，即可完全匹配预期计算结果，逐例验证：

• 用户1111：原有逻辑计算剩余0天，过期节点为2018-07-27，距离当前已过去985天，剩余-985天，取较小值-985
• 用户2222：原有逻辑计算剩余6天，过期节点为2021-07-17，距离当前还有101天，取较小值6
• 用户3333：原有逻辑计算剩余385天，过期节点为2022-02-01，距离当前还有300天，取较小值300

兼容所有支持MIN双参数的数据库版本的写法：

SELECT
  USER,
  MIN(
    SUM(DOSES) * 90 - DATEDIFF(DAY, MIN(PURCHASE_DATE), GETDATE()),
    DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, 1, MIN(PURCHASE_DATE)))
  ) AS DAYS_REMAINING
FROM 业务表
GROUP BY USER

对于不支持MIN双参数的数据库，可替换为CASE判断实现：

SELECT
  USER,
  CASE
    WHEN SUM(DOSES) * 90 - DATEDIFF(DAY, MIN(PURCHASE_DATE), GETDATE())
         < DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, 1, MIN(PURCHASE_DATE)))
    THEN SUM(DOSES) * 90 - DATEDIFF(DAY, MIN(PURCHASE_DATE), GETDATE())
    ELSE DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, 1, MIN(PURCHASE_DATE)))
  END AS DAYS_REMAINING
FROM 业务表
GROUP BY USER

内容的提问来源于stack exchange，提问作者Mark McGown