Calculate current quarter Fiscal_start date based off 13 week (91 day) rolling quarters

1
votes

I am looking for a SQL solution to work in Oracle Developer SQL that will calculate the current quarters start and end date based off of sysdate and that the Fiscal calendar starts on Feb 1, 2020. Each quarter is a Fixed 13 weeks (91 days) -- NOT 3 months, therefore each year has a slightly different fiscal calendar.
Code will be uploaded to automated reporting and do not want to adjust it each year. Current year fiscal calendar attached as sample explanation. Current Fiscal Calendar

I started to head down this road but got lost when i realized the start date wasnt able to be correct in this format.

End solution would be used for a where clause to determine reporting date range such as ( Where Report_Date between Modified_Quarter_Start and sysdate ) 

select trunc(add_months(add_months(trunc(sysdate,'Y') -1 ,
to_number(to_char(sysdate,'Q')) * 3),-1),'MM') 
start_date,trunc(add_months(add_months(trunc(sysdate,'Y') -1 ,
to_number(to_char(sysdate,'Q')) * 3),+2),'MM')-1 Endd_date,
add_months(trunc(sysdate,'Y') -1 ,to_number(to_char(sysdate,'Q')) * 3) end_date ,
to_char(sysdate,'YYYY - q') qtr from dual

Greatly appreciate any assistance.

1
sqldateoracle-sqldevelopercalculation
So your quarter always starts on a sunday and every year 1 or 2 days earlier? This should return the 1st day of the quarter: date '2020-02-01' + (trunc((current_date - date '2020-02-01') / 91) * 91), add 90 days to get the last_day – dnoeth
Please provide more sample data. For instance, what is the fiscal date of 2021-01-31? Of 2022-01-31? Of 2030-01-31? – Gordon Linoff
appears i was typing as you were, please see below. for 2021-01-31 fiscal date would be Q1 of FY22. it is a very strange way of fiscal calendars for a company that large. – Dustin Gambino
Quarter would actually always start on a Saturday – Dustin Gambino

1 Answers

0
votes

Posting incase someone else runs into the same predicement in the future. After reading a mention of a case statement for fixed days in another thread, i thought of this solution. 

Select Trunc(sysdate - to_date('02-Feb-2019')), 
Case When Trunc(sysdate - to_date('02-Feb-2019')) > 91 Then to_date('02-Feb-2019') + 
Round( to_number(Trunc(sysdate - to_date('02-Feb-2019'))/91),0) * 91 
Else null End Current_Quarter_Start, 
Case When Trunc(sysdate - to_date('02-Feb-2019')) > 91 Then to_date('02-Feb-2019') + 
( (Round( to_number(Trunc(sysdate - to_date('02-Feb-2019'))/91),0) +1 )* 91)-1 Else null End Current_Quarter_End From Dual