How to calculate the first date and last date in a Period in Oracle SQL as per Gregorian Calendar

Question

I want to build a query to get the first date and last date of a period in Oracle SQL database as per the 4-4-5 calendar to achieve something like below:

Can anyone help me in the same? I tried with the below queries but not able to reach to the point:

alter session set NLS_TERRITORY = 'THE NETHERLANDS';
alter session set NLS_CALENDAR='GREGORIAN';
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual;

Answer 1

The initial date of the first period is either provided externally (I did so, in a WITH clause; more likely it will be a bind variable, or an in parameter to a procedure), or it can be calculated based on rules, in a separate code block.

With the initial date given or calculated, the rest can be done, for example, as shown below.

with
  inputs ( input_date ) as (
    select date '2017-01-02' from dual
  )
select level as period_number,
       input_date + 28 * (level - 1) + 7 * trunc((level-1)/3) as period_start,
       input_date + 28 *  level - 1  + 7 * trunc( level   /3) as period_end,
       case mod(level, 3) when 0 then 35 else 28 end          as days_in_period
from   inputs
connect by level <= 12
;



PERIOD_NUMBER PERIOD_START PERIOD_END   DAYS_IN_PERIOD
------------- ------------ ------------ --------------
            1 2-1-2017     29-1-2017                28
            2 30-1-2017    26-2-2017                28
            3 27-2-2017    2-4-2017                 35
            4 3-4-2017     30-4-2017                28
            5 1-5-2017     28-5-2017                28
            6 29-5-2017    2-7-2017                 35
            7 3-7-2017     30-7-2017                28
            8 31-7-2017    27-8-2017                28
            9 28-8-2017    1-10-2017                35
           10 2-10-2017    29-10-2017               28
           11 30-10-2017   26-11-2017               28
           12 27-11-2017   31-12-2017               35