Calculate Week Numbers based on the initial given date to end date

I have below scenario that Business want to calculate Week Number based on Given Start Date to End Date.

For Ex: Start Date = 8/24/2020 End Date = 12/31/2020 ( These Start date & end date are not constant they may change from year to year )

Expected Output below:

[Date 1      Date 2       Week Number
8/24/2020    8/30/2020    week1
8/31/2020    9/6/2020     week2
9/7/2020     9/14/2020    week3
9/15/2020    9/21/2020    week4
9/22/2020    9/28/2020    week5
9/29/2020    10/5/2020    week6
10/6/2020    10/12/2020   week7
10/13/2020   10/19/2020   week8
10/20/2020   10/26/2020   week9
10/27/2020   11/02/2020   week10
11/03/2020   11/09/2020   week11
11/10/2020   11/16/2020   week12
11/17/2020   11/23/2020   week13
11/24/2020   11/30/2020   week14

I need Oracle Query to calculate Week Number(s) like above .. Based on Start date for 7 days then week number will be calcuated.. But remember that crossing months some month have 30 days and some month 31 days etc.. How to calculate ? Appreciate your help!!

Answer

Seems your looking for custom week definition rather that built-ins. But not overly difficult. The first thing is to convert from strings to dates (if columns actually coming off table this conversion is not required), and from there let Oracle do all the calculations as you can apply arithmetic operations to dates, except adding 2 dates. Oracle will automatically handle differing number of days per month correctly. Two methods for this request:

1. Use a recursive CTE (with)

with dates(start_date,end_date) as 
     ( select date '2020-08-24' start_date
            , date '2020-12-31' end_date 
         from dual
     )
   , weeks (wk, wk_start, wk_end, e_date) as
     ( select 1, start_date, start_date+6 ld, end_date from dates
       union all 
       select wk+1, wk_end+1, wk_end+7, e_date
         from weeks 
        where wk_end<e_date
     )
select wk, wk_start, wk_end from weeks;

2. Use Oracle connect by

with dates(start_date,end_date) as 
     ( select date '2020-08-24' start_date
            , date '2020-12-31' end_date 
         from dual
     )
 select level wk
      , start_date+7*(level-1) wk_start
      , start_date+6+7*(level-1)
   from dates
 connect by level <= ceil( (end_date-start_date)/7.0);

Depend on how strict you need to be with the end date specified you may need to adjust the last row returned. Both queries do not make adjust for that. They just ensure no week begins after that date. But the last week contains the full 7 days, which may end after the specified end date.