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

cthtcc · 2022-08-12T18:01:04+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Accepted answers

All comments

GrantSmith

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.

cthtcc

Thanks @GrantSmith I appreciate it!