Use yesterday date on where clause with specific time-CodePudding

I want to select a value using yesterdays date with a specific time. here is my example: So I want to replace the yesterday variable with the date of yesterday but specify in specific time.

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

Thanks

CodePudding user response：

Don't cast or convert your START_DATE to time or you will not be able to make comparison like T >= **YESTERDAYDATE:'06:00:00'

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

You need yesterday and today's date at midnight 00:00:00

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

And with the above, you can obtain the various datetime like 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)

and then finally

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

Note : The last CASE expression Night should be from yesterday 22:00 till today 06:00