基于出生日期(DOB)计算生日剩余天数并实现分类标记的SQL技术问询

我需要根据用户离生日的剩余天数，为每个用户标记对应的分类编号，具体规则如下：

• 分类0：当天为其生日（例如John、Eric）
• 分类1：生日在未来7天内（本周，例如Ben、Jerry）
• 分类2：生日在未来8-31天内（本月，例如Jules）
• 分类3：其余所有情况（例如Tom）

我尝试用DATEDIFF函数配合格式参数进行计算，但发现DATEDIFF并不支持格式参数；不使用格式参数时，返回结果仅为用户的原始出生日期，完全没法满足需求。我最初尝试的代码如下：

SELECT * FROM (SELECT [id], [fullname] = CONCAT(E.[name], (CASE WHEN LEN(E.[preposition]) > 0 THEN ' ' + E.[preposition] END), ', ', E.[givenname]), [relationnumber], [day] = (CASE WHEN DATEDIFF(day, [birthday], '2021-09-09') < 1 THEN 0 WHEN DATEDIFF(day, [birthday], '2021-09-09') < 8 THEN 1 WHEN DATEDIFF(day, [birthday], '2021-09-09') < 31 THEN 2 ELSE 3 END), [birthday] FROM [info].[member] E WHERE [system_active] = 1) A ORDER BY day ASC

注：代码中的固定日期'2021-09-09'是从URL中获取的。

经过调试，我找到了能满足基础需求的实现方式，核心思路是先计算出用户当年的生日日期（通过DATEADD和DATEDIFF调整年份），再基于这个日期和目标日期计算间隔天数来完成分类：

SELECT * FROM (
 SELECT [id]
 ,[fullname] = CONCAT(E.[name], (CASE WHEN LEN(E.[preposition])>0 THEN ' '+E.[preposition] END), ', ', E.[givenname])
 ,[relationnumber]
 ,hi = DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])
 ,[day] = (
 CASE WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) = 0 THEN 0
 WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) BETWEEN 1 AND 7 THEN 1
 WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) BETWEEN 8 AND 31 THEN 2
 ELSE 3 END
 )
 ,[birthday]
 FROM [info].[member] E
 WHERE [system_active] = 1
 ) A ORDER BY day ASC

如果需要更高效、更健壮的实现方案，可以参考MatBailie的回答，上述方案仅能满足我的基础业务需求。

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