Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Week Number in a Quarter

Seyed_GAug 2 2019 — edited Aug 2 2019

Hi all,

I am trying to determine week number for each quarter. It is needed for loading data into an existing date table.  I have seen many examples on this forum, none satisfies my requirement. We want to compute week number within each quarter and that is based on ‘Sundays’. Let’s say, we are loading dates for the week of 01/01/2018. Since 01/01/2018 didn’t start on a Sunday; we want to use the week number for previous quarter (14) to be used for loading 1/1/2018 data through 1/6/2018. Then we start at ‘1’ for loading data for 1/7/2018 through 1/13/2018 and then increment the week number after that.  How do we do that? I tried a solution on https://community.oracle.com/message/3717472#3717472,
but I get ‘1’s for 1/1/2018 through 1/6/2018 and ‘2’s after that. It seems to me if the beginning of the quarter does not start on a ‘Sunday’, then my code should continue using the last computed week number (either ‘13’ or ‘14’) until the next day is a ‘Sunday’ at which point, program would start back at ‘1’.

Date             Week Number

12/24/2017         13

12/25/2017         13

12/26/2017         13

12/27/2017         13

12/28/2017         13

12/29/2017         13

12/30/2017         13

12/31/2017         14

1/1/2018              14

1/2/2018              14

1/3/2018              14

1/4/2018              14

1/5/2018              14

1/6/2018              14

1/7/2018              1

1/8/2018              1

1/9/2018              1

Thank you,

Seyed

This post has been answered by mathguy on Aug 2 2019
Jump to Answer
Comments
Post Details
Added on Aug 2 2019
4 comments
1,025 views