SnowFlake/SQL - Filtering for Weeks 50,51 and 52 of 2023 + week 1 of 2024

Question

I am trying to filter for the previous four weeks. But I do get broken weeks with the change of the year from 2023 and 2024

I have tried a lot of queries, but it is only complicating my query more. Maybe we can try and add a row_number to rearrange the weeks so we do not break the year to year change.

This is my query:

QUALIFY MIN(to_decimal(year(date(ETA_PUBLISHED))||weekiso(date(ETA_PUBLISHED)))) OVER (PARTITION BY VOYAGE_REF) 
        BETWEEN to_decimal(year(sysdate())||weekiso(sysdate())) -4 AND
                to_decimal(year(sysdate())||weekiso(sysdate())) -1

This has caused an issue on the shift from 2023-2024, where the condition above is no longer abided.
Example:

20241 (Current Week)

202352(Week Before)

202351(2 Weeks Before)

202350(3 weeks before)

...

My issue lies here, how can I filter for the previous 4 weeks without broken weeks, or without hard coding my query.

I am aware the concatenation is my biggest problem but I cant figure out a better way to think about it

Answer 1

Try this.

QUALIFY MIN(ETA_PUBLISHED) OVER (PARTITION BY VOYAGE_REF) BETWEEN
        trunc(sysdate() - interval '3 week', 'week')             -- 2023-12-11 00:00:00.000
    AND dateadd(second, 24*60*60-1, last_day(sysdate(), 'week')) -- 2024-01-07 23:59:59.000