sql - 存储过程中的日期和班次时间类型过滤器

Question

我正在使用 SQL Server 2016，并且我有一个传感器表，如下所示：

传感器代码	传感器状态	时间戳
PS01A	积极的	2019-11-20 01:38:11.850
PS01B	积极的	2019-11-20 02:30:09.850
PS01C	积极的	2019-11-20 05:32:11.004
PS01D	积极的	2019-11-20 07:38:07.997
PS01E	积极的	2019-11-20 11:38:06.700

如何在存储过程中使用日期和班次类型作为输入参数过滤上表？我可以查询过滤日期而不是班次类型。

对于班次类型，请使用下表作为参考：

移位类型	开始时间	时间结束
天	06:00:00	17:59:59
夜晚	18:00:00	05:59:59

这是我的代码

ALTER PROCEDURE [dbo].[GET_SENSOR_STATE]
@date DATE,
@shifttypeid INT,
@shifttype NVARCHAR(MAX)

AS

BEGIN

SET NOCOUNT ON;

IF (@shifttype = 'DAY')
SET @shifttypeid = 0
ELSE
SET @shifttypeid = 1
    
SELECT [SensorCode]
      ,[SensorStatus]
      ,[Timestamp]

FROM [MyTable].[dbo].[SensorState] SS

WHERE
--@date = CAST(SS.Timestamp AS DATE)
CASE @shifttypeid
WHEN 0 THEN @date = CAST(SS.Timestamp AS DATE) AND DATEPART(HH, SS.Timestamp) BETWEEN 6 AND 18
WHEN 1 THEN @date = CAST(SS.Timestamp AS DATE) AND DATEPART(HH, SS.Timestamp) BETWEEN 18 AND 6
END

END 

标签： sqlsql-serverstored-procedures

Answer 1

你不能使用BETWEEN 18 AND 6它永远不会是真的。同样对于BETWEEN，检查条件是包容性的，这BETWEEN 6 AND 18意味着>= 6 AND <= 18这不是您想要的。最好使用>=和<

WHERE (
          CAST(SS.Timestamp AS DATE) = @date
      OR  (    
               @shifttypeid = 1 
           AND CAST(SS.Timestamp AS DATE) = DATEADD(DAY, 1, @date)
          )
      )
AND   (
          (    -- Day Shift
               @shifttypeid = 0
          AND  DATEPART(HH, SS.Timestamp) >= 6
          AND  DATEPART(HH, SS.Timestamp) < 18
          )
      OR  (    -- Night Shift
               @shifttypeid = 1
          AND  (
                   DATEPART(HH, SS.Timestamp) < 6
               OR  DATEPART(HH, SS.Timestamp) >= 18
               )
          )
      )

添加了处理Night Shift日期的条件

---

编辑：基于相应的@shifttypeid过滤器Timestamp。

WHERE  (    -- Day Shift 06:00 to 18:00
            @shifttypeid = 0
       AND  SS.Timestamp >= DATEADD(HOUR,  6, CAST(@date AS DATETIME))
       AND  SS.Timestamp <  DATEADD(HOUR, 18, CAST(@date AS DATETIME))
       )   
OR     (    -- Night Shfit 18:00 to 06:00
            @shifttypeid = 1
       AND  SS.Timestamp >= DATEADD(HOUR, 18, CAST(@date AS DATETIME))
       AND  SS.Timestamp <  DATEADD(HOUR, 30, CAST(@date AS DATETIME))
       )