'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”
- 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”
- 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”
- 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”
- 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”
- 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 |
|---|
