SQL Server按当前班次筛选数据：WHERE子句CASE逻辑修正

需要实现SQL Server查询，根据当前时段筛选对应班次的数据：日班标记为1（对应7-18点），夜班标记为0（对应0-6点、19-23点）。已通过CTE构建名为DATA的表，其中WhichShift字段标记各记录所属班次，但WHERE子句中的CASE逻辑存在问题，导致无论当前处于哪个班次，所有行都会被返回。请修正该逻辑，实现仅显示当前班次的行。

现有查询代码 #

WITH DATA as(
    select vwDateShift.*
        ,DateHour.DateTime
        ,DateHour.DateTimeEnd
        ,DateHour.Hour
        ,DateHour.HH
        ,DateHour.[HH:MM]
        ,DATEDIFF(Hour,DateTime,GETDATE()) as HoursAgo
        ,CASE
            WHEN hour <= 6 THEN 0
            WHEN hour >= 7 And hour <=18 THEN 1
            ELSE 0
         END AS WhichShift
        ,Case
            WHEN hour <= 6 Then ShiftNightPrev
            WHEN hour >= 19 Then ShiftNight
            ELSE ShiftDay
        end AS ShiftHour
    from PublicUtilities..vwDateShift
    Left Join PublicUtilities..DateHour on DateHour.Date = vwDateShift.Date
    )
    SELECT * 
    FROM DATA
    WHERE WhichShift = CASE
        WHEN DATEPART(HOUR, GETDATE()) = 1 THEN WhichShift
        WHEN DATEPART(HOUR, GETDATE()) = 2 THEN WhichShift
        WHEN DATEPART(HOUR, GETDATE()) = 3 THEN WhichShift
        ...
        END

数据示例 #

Date Time                  HH  WhichShift
-----------------------     --  -----------
2000-01-06 04:00:00.000     04  0
2000-01-06 05:00:00.000     05  0
2000-01-06 06:00:00.000     06  0
2000-01-06 07:00:00.000     07  1
2000-01-06 08:00:00.000     08  1
2000-01-06 09:00:00.000     09  1

问题分析与修正 #

原WHERE子句的CASE逻辑错误：无论当前时段是什么，CASE的返回值都是WhichShift本身，导致WhichShift = WhichShift永远成立，所以会返回所有行。

正确的逻辑应该是先根据当前时间计算出对应的班次标记（1或0），再筛选DATA表中WhichShift等于该标记的行。修正后的完整查询如下：

WITH DATA as(
    select vwDateShift.*
        ,DateHour.DateTime
        ,DateHour.DateTimeEnd
        ,DateHour.Hour
        ,DateHour.HH
        ,DateHour.[HH:MM]
        ,DATEDIFF(Hour,DateTime,GETDATE()) as HoursAgo
        ,CASE
            WHEN hour <= 6 THEN 0
            WHEN hour >= 7 And hour <=18 THEN 1
            ELSE 0
         END AS WhichShift
        ,Case
            WHEN hour <= 6 Then ShiftNightPrev
            WHEN hour >= 19 Then ShiftNight
            ELSE ShiftDay
        end AS ShiftHour
    from PublicUtilities..vwDateShift
    Left Join PublicUtilities..DateHour on DateHour.Date = vwDateShift.Date
    )
    SELECT * 
    FROM DATA
    WHERE WhichShift = 
        CASE 
            WHEN DATEPART(HOUR, GETDATE()) BETWEEN 7 AND 18 THEN 1
            ELSE 0
        END

逻辑说明 #

• 当当前时间的小时数在7到18之间时，CASE返回1，筛选出WhichShift=1的日班数据
• 其他时段（0-6点、19-23点）CASE返回0，筛选出WhichShift=0的夜班数据

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