Slight Modification to the Previous Day, Month, and Year Calculations

[JPBTech]

The source is Direct Query SQL Server

I am trying to determine the previous day (month, year) count, but only to the current point in time.

End goal: IsToday = 1, IsPreviousDay = 1, IsCurrentMonth = 1, IsLastMonth = 1
...but only up to this point in time (NOW()).

Example
Previous Day Total is 75
Previous Day Total up to NOW() is33

pDtH stands for "Previous Day to Hour

The code snippet below works, but it returns the entire hour. I need it to stop at the current minute...I hope that makes sense.

pDtH =
IF.EAGER (
    YEAR ( CFS[Date CFS Received] ) = YEAR ( TODAY () )
        && MONTH ( CFS[Date CFS Received] ) = MONTH ( TODAY () )
        && DAY ( CFS[Date CFS Received] )
            <= DAY ( TODAY () ) - 1
        && HOUR ( CFS[Time CFS Received] ) <= HOUR ( NOW () ),
    1,
    0
)

The code below is where I am needing assistance.

pDtH =
IF.EAGER (
    YEAR ( CFS[Date CFS Received] ) = YEAR ( TODAY () )
        && MONTH ( CFS[Date CFS Received] ) = MONTH ( TODAY () )
        && DAY ( CFS[Date CFS Received] )
            <= DAY ( TODAY () ) - 1
        && HOUR ( CFS[Time CFS Received] ) <= HOUR ( NOW () )

        && MINUTE ( CFS[Time CFS Received] ) <= MINUTE( ( NOW() )),    <--- Causes issues

    1,
    0
)

Maybe I have been looking at this too long to see the obvious.  🙂

[Greg_Deckler]

@JPBTech  Can you just do this?

pDtH = IF(CFS[Date CFS Received] <= NOW() - 1,1,0)

[JPBTech · Accepted solution]

For what it might be worth, here is a working version done in Microsft SQL:

FROM [database].[dbo].[table] t
WHERE YEAR([t].[DateTimeReceived]) = YEAR(GETDATE())

AND DATEPART(MONTH, [t].[DateTimeReceived]) = DATEPART(MONTH, GETDATE()) --Gets the Current Month
AND DATEPART(DAY, [t].[DateTimeReceived]) = DATEPART(DAY, GETDATE()) -1 --Gets the Previous Day
AND CONVERT(VARCHAR, [t].[DateTimeReceived], 114) <= CONVERT(VARCHAR, GETDATE(), 114) --Gets the Current Time Stamp

[JPBTech]

HI, @Greg_Deckler, thank you for taking the time to reply.

Implementing that piece of code returns the value of 1 for all dates "below" now.