'Oracle query for Quarter and Yearly Dates for date range

I am looking to get some custom quarter and yearly dates based on a start date and end date .I am able to achieve the result with plain query on quarterly and yearly dates,but there are some cases where some additional logic which i am trying but could not get to it . Below is the query i have used

/* mONTHLY LOGIC */
Select ADD_MONTHS(add_months(to_date(STARTDATE), (level)),-1) START_DATE, add_months(to_date(STARTDATE), (level)) END_DATE
  From dual  connect by level <=           (months_between(to_date(ENDDATE), to_date(STARTDATE)))
 UNION
 /* QUARTERLY LOGIC*/
  SELECT trunc(add_months(to_date(STARTDATE), 3 * level-3),'Q')-1 START_DATE,
          trunc(add_months(to_date(STARTDATE), 3 * level), 'Q')-1  END_DATE
  from dual  
connect by level <= (months_between(to_date(ENDDATE),to_date(STARTDATE)) + 3) / 3
/* YEARLY LOGIC */
UNION
SELECT    trunc(add_months(to_date(STARTDATE), 12*level), 'Y')-1 START_DATE,
          trunc(add_months(to_date(STARTDATE), 12*level+12), 'Y')-1
        END_DATE,IPP.IP_TWR IP_TWR,TIME_PERIOD
  from dual  connect by level <= round((months_between(to_date(ENDDATE), STARTDATE)/12),0)

Below is the requirement i am trying to meet Quarterly Examples:

1.If Begin and End date fall on quarter start and end date - show all quarters as their own row.

Begin Date: 12/31/2017 End Date: 12/31/2018
“12/31/2017 to 3/31/2018”
“3/31/2018 to 6/30/2018”
“6/30/2018 to 9/30/2018”
“9/30/2018 to 12/31/2018”
  1. If End Date does not fall on a quarter end date - show all quarters as their own row including partial period
Begin Date: 12/31/2017 End Date: 7/31/2018
“12/31/2017 to 3/31/2018”
“3/31/2018 to 6/30/2018”
“6/30/2018 to 7/31/2018”
  1. If Begin Date is not a quarter start date - do not show show the first quarter since we do not have a QTD return for 1/31/2018 to 3/31/2018
Begin Date: 1/31/2018 End Date: 12/31/2018
“3/31/2018 to 6/30/2018”
“6/30/2018 to 9/30/2018”
“9/30/2018 to 12/31/2018”

Yearly Examples:

1.If Begin and End date fal1.l on year start and end date - show all years as their own row.

Begin Date: 12/31/2017 End Date: 12/31/2018
“12/31/2017 to 12/31/2018”
  1. If Begin and End date fall on year start and end date (multiple years) - show all years as their own row.
Begin Date: 12/31/2017 End Date: 12/31/2020
“12/31/2017 to 12/31/2018”
“12/31/2018 to 12/31/2019”
“12/31/2019 to 12/31/2020”
  1. If Begin Date is not a year start date - do not show show the first year since we do not have a YTD return for 2/28/2017 to 12/31/2018
Begin Date: 2/28/2017 End Date: 12/31/2020
“12/31/2018 to 12/31/2019”
“12/31/2019 to 12/31/2020”
  1. If End Date is not a year end date - show all year as their own row including partial period
Begin Date: 12/31/2017 End Date: 7/31/2020
“12/31/2017 to 12/31/2018”
“12/31/2018 to 12/31/2019”
“12/31/2019 to 7/31/2020”


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source