How to increment weeks by adding number

lucy 0
Viewed 35

I have a table that contains week number in string and number. I want to sum number with week and get the next week.

for example tableA

week    num
2022-1  1
2022-3  3

output

week   num  new_week
2022-1  1  2022-2
2022-3  3  2022-6
2022-52 2  2023-2

As a result, I converted the week into the date, added the week to the date, and finally converted the date back to the week. However, when I try to work date to week, I have issues. The SQL below is what I'm using

CONCAT(YEAR(DATEADD('week', num, date)), WEEK(DATEADD('week', num, date)))

I am not using the calendar year. Due to the fact that my week begins on the first Friday of every year, the calculation is incorrect. Would it be possible to avoid the need to convert week into date and date into week?

snowflake-cloud-data-platform sql
2 Answers

I wrote a small JS UDF to do your "week" math. It seems if December 31 is Thursday, then that year has 53 weeks. Good thing is, you don't need to convert your "year-week" to dates.

create or replace function addweeks( spcweek VARCHAR, num VARCHAR ) returns VARCHAR
  LANGUAGE JAVASCRIPT
AS
$$
    year = parseInt(SPCWEEK.substring( 0, 4 ));
    week = parseInt(SPCWEEK.substring( 5 ));
    week = week + parseInt(NUM);
    weekinyear = (new Date(year, 11, 31).getDay() == 4 ? 53 : 52);
    
    while (week > weekinyear ) {
        week = week - weekinyear;
        weekinyear = (new Date(year, 11, 31).getDay() == 4 ? 53 : 52);
        year ++;
    }
    return year + "-" + week;
$$
;

select myweek, num, addweeks( myweek, num) new_week
from mydata;

+---------+-----+----------+
| MYWEEK  | NUM | NEW_WEEK |
+---------+-----+----------+
| 2022-1  |   1 | 2022-2   |
| 2022-3  |   3 | 2022-6   |
| 2022-52 |   2 | 2023-2   |
| 2020-52 |   2 | 2021-1   |
+---------+-----+----------+

I think you can correct my logic if there is an error in calculating the total weeks of the year.

Gokhan Atil0

With a bit of string fiddling you could do the calulation like this.

SELECT week, num, CONCAT( SUBSTRING(week FROM 1 for 5), num + SUBSTRING(week FROM INSTR(week, '-')+1))
FROM table;
RiggsFolly0
Related