how to get different date ranges for the last One year as per sysdate

I have a scenario: as per my sysdate i need to capture last 12 months dates.

Example: I will be getting a parameter which is numeric like : 2,3,4,6 ..

If the parameter is 3: then as per sysdate-12 i am expecting 4 records as below

Start_Date  End_Date
20180801    20181101
20181101    20190201
20190201    20190501
20190501    20190827

select TO_CHAR(add_months(trunc(sysdate, 'month'), -12),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -9),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -9),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -6),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -6),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -3),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -3),'YYYYMMDD') Start_Date,TO_CHAR(trunc(sysdate),'YYYYMMDD') End_Date from dual

Between two dates i have difference of 3 months. If the parameter is 2 then difference between Start_Date and End_Date should be 2 months which means i will be getting 6 records.

Can we write a query to read this numeric parameter and create records on the base of the parameter. Without writing multiple queries like above , is there any possibility i can read the parameter and create records

Answer

You can use the following query:

SELECT
    ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
    CASE
        WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
        ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
    END AS END_DATE
FROM
    (
        SELECT
            ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
        FROM
            DUAL
    )
CONNECT BY
    LEVEL <= 12 / ( &&INPUT_NUMBER );

— With Input parameter as 2

SQL> SELECT
  2      ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
  3      CASE
  4          WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
  5          ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
  6      END AS END_DATE
  7  FROM
  8      (
  9          SELECT
 10              ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
 11          FROM
 12              DUAL
 13      )
 14  CONNECT BY
 15      LEVEL <= 12 / ( &&INPUT_NUMBER )
 16  ;


START_DAT END_DATE
--------- ---------
01-AUG-18 01-OCT-18
01-OCT-18 01-DEC-18
01-DEC-18 01-FEB-19
01-FEB-19 01-APR-19
01-APR-19 01-JUN-19
01-JUN-19 27-AUG-19

6 rows selected.

SQL>

— With Input parameter as 3

SQL> SELECT
  2      ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
  3      CASE
  4          WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
  5          ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
  6      END AS END_DATE
  7  FROM
  8      (
  9          SELECT
 10              ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
 11          FROM
 12              DUAL
 13      )
 14  CONNECT BY
 15      LEVEL <= 12 / ( &&INPUT_NUMBER )
 16  ;
Enter value for input_number: 3


START_DAT END_DATE
--------- ---------
01-AUG-18 01-NOV-18
01-NOV-18 01-FEB-19
01-FEB-19 01-MAY-19
01-MAY-19 27-AUG-19

SQL>

Cheers!!