我想使用具有特定時間的昨天日期來選擇一個值。這是我的示例：所以我想用昨天的日期替換昨天的變數，但要在特定時間指定。
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
謝謝
uj5u.com熱心網友回復：
不要投射或轉換你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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374188.html
標籤：sql sql-server 查询语句 sql-server-2008