SQL技术请求：为RegistrationDate添加当前时段标记列

现有当日RegistrationDate列表 #

原查询语句 #

SELECT 
  DISTINCT AttendanceRegister.RegistrationDate 
FROM 
  AttendanceRegister 
WHERE 
  CAST(AttendanceRegister.RegistrationDate AS DATE) = CAST(GETDATE() AS DATE)

新增时段标记列的SQL实现 #

利用窗口函数LEAD获取每个时段的下一个时间点，结合CASE语句判断当前时间所属时段：

WITH DailyRegistration AS (
    SELECT 
        DISTINCT RegistrationDate
    FROM 
        AttendanceRegister
    WHERE 
        CAST(RegistrationDate AS DATE) = CAST(GETDATE() AS DATE)
),
RegistrationWithNextPeriod AS (
    SELECT 
        RegistrationDate,
        LEAD(RegistrationDate) OVER (ORDER BY RegistrationDate) AS NextRegistrationDate
    FROM 
        DailyRegistration
)
SELECT 
    RegistrationDate,
    CASE 
        WHEN GETDATE() BETWEEN RegistrationDate AND ISNULL(NextRegistrationDate, DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
        THEN 'Current Period'
        ELSE ''
    END AS PeriodStatus
FROM 
    RegistrationWithNextPeriod
ORDER BY 
    RegistrationDate;

逻辑说明 #

1. DailyRegistration：筛选出当日所有唯一的RegistrationDate；
2. RegistrationWithNextPeriod：通过LEAD函数为每个时段匹配下一个时段的时间，最后一行的NextRegistrationDate会返回NULL；
3. 主查询：
   • 普通行判断当前时间是否处于RegistrationDate和NextRegistrationDate之间，符合则标记为Current Period；
   • 最后一行用ISNULL将NextRegistrationDate替换为次日零点，确保当前时间晚于最后一个时段时，最后一行会被标记为当前时段。

内容的提问来源于stack exchange，提问作者S.exe