Round date to fiscal year

Question

The following database view rounds the date back to the closest fiscal year (April 1st):

CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
SELECT
  CASE
    WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN    
      to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy')
    ELSE
      to_date('1-APR-'||to_char(SYSDATE, 'YYYY'), 'dd-MON-yyyy')
  END AS fiscal_year
FROM
  dual;

This allows us to calculate the current fiscal year based on today's date.

How can this calculation be simplified or optimized?

Answer 1

Perhaps this...

SELECT to_date('01/04/' ||
to_char(extract(YEAR FROM SYSDATE)
- CASE WHEN extract(MONTH FROM SYSDATE) BETWEEN 1 AND 4 THEN 1 ELSE 0 END),
'DD/MM/YYYY') FROM dual;

I guess this is another option...

SELECT add_months(trunc(SYSDATE) - extract(DAY FROM SYSDATE) + 1,
- (extract(MONTH FROM SYSDATE) + CASE
WHEN extract(MONTH FROM SYSDATE) <= 4 THEN 12 ELSE 0 END) + 4)
FROM dual;

Other options are rewriting as a function that returns a date, or the logic could be simplified if you could just return the year number for the current fiscal year since you'd only need the logic within the to_char.

Answer 2

SELECT ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-3),'YYYY'),3)
FROM DUAL;

Answer 3

TRUNC() can be usefully applied to dates, with different format masks. Most pertinently, trunc(sysdate, 'yyyy') gives us the first day of the year. So this will give us the 01-APR of the current year ...

add_months(trunc(sysdate, 'yyyy'), 3)

and this that date for the previous year ...

add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3)

So:

CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
WITH cte as 
    ( select add_months(trunc(sysdate, 'yyyy'), 3) as this_year
             , add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3) as last_year
      from dual )
SELECT
  CASE
    WHEN SYSDATE >= cte.this_year THEN    
      cte.this_year
    ELSE
      cte.last_year
  END AS fiscal_year
FROM
  cte;

caveat: I haven't had the chance to test this code yet so it might contain typos. I will test it later and correct it if necessary.

Answer 4

I find the TO_CHAR(date, 'Q') feature of oracle very useful for calculating fiscal calendars. The query below uses the 'with' clause to build two things

1. Sample Data - test_dates table.
2. fiscal_map - a simple mapping of the calendar quarters to your fiscal calendar. In this example the 4th calendar quarter is the 1st fiscal quarter (October 1st).

Example:

with test_dates as (
 select sysdate + level * 80 test_date from dual connect by level < 11
),
fiscal_map as (
 select 1 cal, 2 fiscal from dual
 union
 select 2 cal, 3 fiscal from dual
 union
 select 3 cal, 4 fiscal from dual
 union
 select 4 cal, 1 fiscal from dual
)
select 
 test_date, 
 TO_CHAR(test_date, 'Q') cal_quarter, 
 fiscal_map.fiscal,
 (case when CAL < fiscal then 
     TO_CHAR(test_date, 'yyyy') + 0
     else TO_CHAR(test_date, 'yyyy') + 1
 end) FISCAL_YEAR
from test_dates, fiscal_map
where fiscal_map.cal = TO_CHAR(test_date, 'Q')
order by test_date

Output:

TEST_DT CAL_Q   FISCAL Q    FISCAL_YR
22-Jul-10   3   4   2010
10-Oct-10   4   1   2011
29-Dec-10   4   1   2011
19-Mar-11   1   2   2011
07-Jun-11   2   3   2011
26-Aug-11   3   4   2011
14-Nov-11   4   1   2012
02-Feb-12   1   2   2012
22-Apr-12   2   3   2012
11-Jul-12   3   4   2012