How do i manage year breaks for calendar weeks?

ONE DATA Core
1

Given a transaction table with columns:

Customer_id, amount, year, calendar_week

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

2

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
3

Another way to increment calendar weeks is to make use of sparks date add function

 [...]
i.year = year(date_add("@sys_datetime_string@", 7)) as year,/*next CW year*/
i.calendar_week = weekofyear(date_add("@sys_datetime_string@", 7)) as calendar_week  /*next CW week */