week number from a certain date-CodePudding

I have a following table in oracle

as you see the start date is 04-Nov-20. I need to count 04-Nov-2021 to 10-Nov-2021 as week 1 and 11-nov-2021 to 17-Nov-2021 as Week 2 and so on. How can I achieve this values in oracle.

CodePudding user response：

You can check what the week number is by combining the to_char function with the WW, W, or IW.

select to_char(column,'WW') from table;

WW  Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W   Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW  Week of year (1-52 or 1-53) based on the ISO standard.

CodePudding user response：

By calculating a dense rank over the year and the WW weeknumber.

SELECT g.*
, DENSE_RANK() OVER (PARTITION BY game_id ORDER BY TO_CHAR(start_date, 'YYYYWW')) as num
FROM gamestarts g

GAME_ID | START_DATE | NUM
------: | :--------- | --:
    899 | 2020-11-04 |   1
    899 | 2020-11-05 |   1
    899 | 2020-11-06 |   1
    899 | 2020-11-07 |   1
    899 | 2020-11-08 |   1
    899 | 2020-11-10 |   1
    899 | 2020-11-09 |   1
    899 | 2020-11-11 |   2
    899 | 2020-11-12 |   2

Test on db<>fiddle here