如何使用SQL CASE语句按HH:MM格式将datetime值分组到跨天班次

问题根因 #

你当前的写法失效的核心原因是跨零点的班次用HH:MM字符串直接比较会逻辑错误：比如班次2的时间范围是22:30~04:59，字符串比较逻辑下'22:30' > '04:59'，所以[HH:MM] BETWEEN '22:30' AND '04:59'永远返回空值，无法匹配到凌晨的时间。

解决方案 #

把HH:MM格式的时间统一转换为当天的分钟数（0~1439）再做判断，跨零点的时间段拆分两种判断逻辑即可，修改后的查询语句如下：

WITH CTE1 AS(
SELECT
    [item_id]
    ,[event_time]
    -- 把时间转换为当天的总分钟数，方便数值比较
    ,DATEPART(HOUR, [event_time])*60 + DATEPART(MINUTE, [event_time]) AS time_minute
    ,[location_id]
    -- 班次的起止时间也统一转成分钟数
    ,CAST(LEFT([start_time_shift_1],2) AS INT)*60 + CAST(RIGHT([start_time_shift_1],2) AS INT) AS shift1_start
    ,CAST(LEFT([stop_time_shift_1],2) AS INT)*60 + CAST(RIGHT([stop_time_shift_1],2) AS INT) AS shift1_end
    ,CAST(LEFT([start_time_shift_2],2) AS INT)*60 + CAST(RIGHT([start_time_shift_2],2) AS INT) AS shift2_start
    ,CAST(LEFT([stop_time_shift_2],2) AS INT)*60 + CAST(RIGHT([stop_time_shift_2],2) AS INT) AS shift2_end
    ,CAST(LEFT([start_time_shift_3],2) AS INT)*60 + CAST(RIGHT([start_time_shift_3],2) AS INT) AS shift3_start
    ,CAST(LEFT([stop_time_shift_3],2) AS INT)*60 + CAST(RIGHT([stop_time_shift_3],2) AS INT) AS shift3_end
FROM [dbo].[testData]
)
SELECT
    [item_id]
    ,[event_time]
    ,CONVERT(varchar(5), [event_time], 108) AS [HH:MM]
    ,[location_id]
    ,CASE 
        WHEN [location_id] = '100' THEN(
            CASE 
                -- 班次1不跨零点，直接判断范围
                WHEN time_minute BETWEEN shift1_start AND shift1_end THEN 'Shift_1'
                -- 班次2跨零点，判断大于等于开始 或者 小于等于结束
                WHEN time_minute >= shift2_start OR time_minute <= shift2_end THEN 'Shift_2'
                -- 班次3不跨零点，直接判断范围
                WHEN time_minute BETWEEN shift3_start AND shift3_end THEN 'Shift_3' 
            END)      
        WHEN [location_id] = '200' THEN(
            CASE 
                WHEN time_minute BETWEEN shift1_start AND shift1_end THEN 'Shift_1'
                WHEN time_minute >= shift2_start OR time_minute <= shift2_end THEN 'Shift_2' 
            END)
        WHEN [location_id] = '300' THEN(
            CASE 
                WHEN time_minute BETWEEN shift1_start AND shift1_end THEN 'Shift_1'
                WHEN time_minute >= shift2_start OR time_minute <= shift2_end THEN 'Shift_2' 
            END)
    END AS [pass]
FROM CTE1
-- 可以去掉where条件测试所有location的数据，班次匹配全部正确
-- WHERE [location_id] = '100'

验证结果 #

针对你提供的测试数据，以上语句可以正确匹配所有班次：

• 100站点的03:24、00:38会正确分到Shift_2
• 100站点的05:42、06:10、08:26会正确分到Shift_3
• 其他站点的班次也全部符合预设规则

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