Identifying "Last Week' when current week is 0 or 1

cthtcc
cthtcc Member
in Magic ETL

I'm using the case statement below to identify dates from "Last Week". It works fine but I will run into a problem in January as we move to week 0 or 1 for current week, and last week could be 52 or 53 (see screen grab).

Any suggestions for approaching this?

week number.png

CASE 

WHEN WEEK(`Date`) = (WEEK(CURRENT_DATE())-1) THEN 'Last Week'

ELSE 'Not Last Week'

END

Answers

  • GrantSmith
    GrantSmith Coach
    edited August 2022 
    CASE WHEN `Date` + INTERVAL (7-DAYOFWEEK(`Date`)) DAY = CURRENT_DATE() - INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY THEN
'Last Week' ELSE 'Not Last Week' END

    To add some additional context I'm adding a specific number of days to the date field to get to saturday (7) of that day's week then comparing it to last saturday (current_date - whatever day of the week it is to get back to 7). DAYOFWEEK returns 1-7 depending on which day the date actually is.

    So for example since today is Friday DAYOFWEEK is 6. 6 days ago would be the saturday of last week.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • cthtcc
    cthtcc Member

    Thanks @GrantSmith I appreciate it!

Categories