在where子句中使用具有特定时间的昨天日期

Question

我想使用带有特定时间的昨天日期来选择一个值。下面是我的例子：所以我想用昨天的日期替换昨天的变量，但要指定具体的时间。

select Flow, Sum(Morning) Morning, Sum(PM) PM, Sum(Night) Night, Count(*) Total
from [dbo].[MISSION]
cross apply (values (Iif(QUELLE in ('Réception_14','Réception_21'),'Flow 1',
Iif(QUELLE in ('Réception_17','Réception_16'),'Flow 2','Flow3'))))f(Flow)
cross apply (values ( Convert(time, [START_DATE] )))v(T)
cross apply (
select
case when T >= **YESTERDAYDATE:'06:00:00'** and T < **YESTERDAYDATE:'11:00:00'** then 1 else 0 end Morning,
case when T >=**YESTERDAYDATE:'11:00:00'** and T < **YESTERDAYDATE:'22:00:00'** then 1 else 0 end PM,
case when T >=**YESTERDAYDATE:'22:00:00'** and T < **YESTERDAYDATE:'06:00:00'** then 1 else 0 end Night
)c
group by Flow

感谢

Answer 1

不要将START_DATE转换为时间，否则将无法像T >= **YESTERDAYDATE:'06:00:00'那样进行比较

cross apply 
(
values ( [START_DATE] )
) v (T)

你需要昨天和今天午夜的日期00:00:00

cross apply
(   
values (convert(datetime, convert(date, getdate())),
convert(datetime, convert(date, getdate() - 1)))
) dates (today, yesterday)

通过以上操作，您可以获得各种日期时间，如YESTERDAYDATE:'06:00:00'、YESTERDAYDATE:'22:00:00'

cross apply
(
values (dateadd(hour, 6, yesterday), 
dateadd(hour, 11, yesterday), 
dateadd(hour, 22, yesterday), 
dateadd(hour, 6, today))
) dt (y6, y11, y22, t6)

最后是

cross apply 
(
select  case when T >= y6  and T < y11 then 1 else 0 end Morning,
case when T >= y11 and T < y22 then 1 else 0 end PM,
case when T >= y22 and T < t6  then 1 else 0 end Night
) c

注意：最后一个CASE表达式Night应该是从昨天的22:00到今天的06:00