How do i manage year breaks for calendar weeks?

Given a transaction table with columns:

Customer_id, amount, year, calendar_week

How do i efficiently filter for entries of next weeks calendar week?

You have to account for Year breaks.

SELECT 
* 
FROM 
    inputTable i 
where  
    i.year =  if(
        ((weekofyear("@sys_datetime_string@") +  1) / weekofyear('@sys_year@-    12-31')) > 1, 
    CAST(CAST(@sys_year@ AS INT)+1 AS STRING), 
    CAST(@sys_year@ AS STRING)
) 
AND
i.calendar_week = if(((weekofyear("@sys_datetime_string@") + 1) / weekofyear('@sys_year@-12-31')) > 1, 
    1, 
    weekofyear("@sys_datetime_string@") + 1
)

Explanation: Check the CW of the last day in the year, possibly do not increment.

Thank You Flogge.

Documentation

1 Like