13 Replies Latest reply on Jan 3, 2013 11:47 AM by Frank Kulash

# How to get the fiscal yr

Hi Experts,

My below query is getting the quarter start, end and previous quarter start and end dates according to the calendar yr..Now I've to get the values accordingly fiscal year..

Thanks in advance for the support...
``````SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)"PREV_QTR_ST_DT", ADD_MONTHS(MAX (LAST_DAY (t)),-3) "PREV_QTR_END_DT" INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_RPRT_DATE)), 'YYYY'), LEVEL - 1)  t,
TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE(V_RPRT_DATE)), 'YYYY'), LEVEL - 1), 'Q')QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD  = V_QUARTER_NUMBER;``````
• ###### 1. Re: How to get the fiscal yr
{message:id=9360002}

Please provide some example input data and what output you expect from it.
Just saying you want the fiscal year doesn't explain what the output should be.
• ###### 2. Re: How to get the fiscal yr
``````SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)
FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jul-2012')), 'YYYY'), LEVEL - 1)  t,
TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jul-2012')), 'YYYY'), LEVEL - 1), 'Q')QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD  = 3``````
If I fire this query the output is below:
``````CURNT_QTR_ST_DT CURNT_QTR_END_DT   PREV_QTR_ST_DT  PREV_QTR_END_DTE
---------------------------   -------------------------------- -------------------------   ------------------------------
01-JUL-12               30-SEP-12                   01-APR-12              30-JUN-12``````
But the values are coming as calendar year, instead of this i need the output like fiscal yr..
``````1st qtr--01-apr-yyyy to 30-jun-yyyy
2nd qtr---01-jul-yyyy to 30-sep-yyyy
3rd qtr---01-oct-yyyy to 31-dec-yyyy
4th qtr---01-jan-yyyy to 31-mar-yyyy``````
Furthermore the instead of getting the calender yr start date and end date from the below qry, i need fiscal yr start and end date: ie--01-apr-yyyy to 31-mar-yyyy
``````SELECT TRUNC(TO_DATE(V_RPRT_DATE),'YEAR')INTO V_CURRYTD_STRT FROM DUAL;

SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_CURRYTD_STRT), 11)) INTO V_CURRYTD_END FROM DUAL;``````
``````output of the above qry
01/01/2012
12/31/2012``````
• ###### 3. Re: How to get the fiscal yr
Hi.
977490 wrote:
``````SELECT   MIN (t)"CURNT_QTR_ST_DT", MAX (LAST_DAY (t))"CURNT_QTR_END_DT", ADD_MONTHS(MIN (t),-3)
FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jul-2012')), 'YYYY'), LEVEL - 1)  t,
TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jul-2012')), 'YYYY'), LEVEL - 1), 'Q')QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD  = 3``````
If I fire this query the output is below:
``````CURNT_QTR_ST_DT CURNT_QTR_END_DT   PREV_QTR_ST_DT  PREV_QTR_END_DTE
---------------------------   -------------------------------- -------------------------   ------------------------------
01-JUL-12               30-SEP-12                   01-APR-12              30-JUN-12``````
But the values are coming as calendar year, instead of this i need the output like fiscal yr..
``````1st qtr--01-apr-yyyy to 30-jun-yyyy
2nd qtr---01-jul-yyyy to 30-sep-yyyy
3rd qtr---01-oct-yyyy to 31-dec-yyyy
4th qtr---01-jan-yyyy to 31-mar-yyyy``````
Sorry, it's still unclear what you want. You still haven't posted any sample data (CREATE TABLE and INSERT statements). Seriously, see the forum FAQ {message:id=9360002}; it can help you a lot.
Your quarters begin and end in exaclty the same places that the regular quarters do, so
TRUNC (dt, 'Q')     is still the beginning of the quarter that contains DATE dt, and
ADD_MONTHS (TRUNC (dt, 'Q'), 3)     is still the beginning of the next quarter.
What you call the quarters is different, however. To get your fiscal quarter "number" (actually a string), you can use
``````TO_CHAR ( ADD_MONTHS (dt, -3)
, 'Q'
)``````
This will return '4' when TO_CHAR alone would return '1'.
Furthermore the instead of getting the calender yr start date and end date from the below qry, i need fiscal yr start and end date: ie--01-apr-yyyy to 31-mar-yyyy
``````SELECT TRUNC(TO_DATE(V_RPRT_DATE),'YEAR')INTO V_CURRYTD_STRT FROM DUAL;

SELECT LAST_DAY (ADD_MONTHS (TO_DATE (V_CURRYTD_STRT), 11)) INTO V_CURRYTD_END FROM DUAL;``````
``````output of the above qry
01/01/2012
12/31/2012``````
Since your fiscal year begins 3 months after the calendar year, you can find the beginng of the fiscal year that includes dt like this
``````v_currytd_strt := ADD_MONTHS ( TRUNC ( ADD_MONTHS (dt, -3)
, 'YYYY'
)
, 3
);
v_currytd_end := ADD_MONTHS (v_currytd, strt, 12)
- 1;``````
where dt is any DATE. (You don't have to use the dual table much in PL/SQL.)
• ###### 4. Re: How to get the fiscal yr
Thnx for ur prompt reply....and help
``````SELECT   ADD_MONTHS(MIN (t),+3)"CURNT_QTR_ST_DT", ADD_MONTHS(MAX(LAST_DAY (t)),+3)"CURNT_QTR_END_DT", MIN (t)"PREV_QTR_ST_DT", MAX (LAST_DAY (t)) "PREV_QTR_END_DT" --INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-aug-2012')), 'YYYY'), LEVEL - 1)  t,
TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-aug-2012')), 'YYYY'), LEVEL - 1), 'Q')QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD  = 2``````
``````output of above qry

CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-JUL-12 30-SEP-12 01-APR-12 30-JUN-12``````
but when M passing the date starting from 01-jan-YYYY and 31-Mar-YYYY
i's giving the absur value like below:
``````CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-JAN-13 31-MAR-13 01-OCT-12 31-DEC-12``````
As according to the fiscal year Jan-Mar comes under qtr no 4..
``SELECT DECODE(TO_NUMBER(TO_CHAR(TO_DATE('20-aug-2012'), 'Q')-1),'0',4,TO_NUMBER(TO_CHAR(TO_DATE('20-aug-2012'), 'Q')-1)) QTR_NUM FROM DUAL;``
I m getting the qtr num from the avove code....
For Qtr no 4 ranging from Jan to Mar...
The output must be 01-jan-2012 to 31-mar-2012 for currrent qtr and for previous qtr it sholud be 01-oct-2011 to 31-dec-2011

Kindly help....
Help Appreciated

• ###### 5. Re: How to get the fiscal yr
You have an absurd query, so why don't you expect absurd results :)
TO_DATE(TO_DATE can't be correct

Does your parameter V_RPRT_DATE alone determines your expected results or do you need the parameter for (fiscal or calendar) quarter too?
If you need both, are they related?
For instance, can you have these values, and what are your expected outcomes
01-jan-2012 1
01-jan-2012 4
20-aug-2012 2
20-aug-2012 4
• ###### 6. Re: How to get the fiscal yr
HI Frank,

Thnx for the help I've implemented ur suggestion on qtr no. i.e-
``````TO_CHAR ( ADD_MONTHS (dt, -3)
, 'Q'
)``````
It's working great---but still I've problem from the below qry:
``````SELECT   ADD_MONTHS(MIN (t),+3)"CURNT_QTR_ST_DT", ADD_MONTHS(MAX(LAST_DAY (t)),+3)"CURNT_QTR_END_DT", MIN (t)"PREV_QTR_ST_DT", MAX (LAST_DAY (t)) "PREV_QTR_END_DT" --INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
FROM (SELECT ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jan-2012')), 'YYYY'), LEVEL - 1)  t,
TO_CHAR (ADD_MONTHS (TRUNC(TO_DATE(TO_DATE('20-jan-2012')), 'YYYY'), LEVEL - 1), 'Q')QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD  = 4;``````
it's still giving the output like below:
``````CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-JAN-13 31-MAR-13 01-OCT-12 31-DEC-12``````
Instead of this the values must be 01-jan-2012 to 31-mar-2012 for current qtr start and end date and for previous qtr start and end date it should be 01-oct-2011 and 31-dec-2011.

Kindly help as m new to oracle...
• ###### 7. Re: How to get the fiscal yr
Check this if it helps.
``````SELECT ADD_MONTHS (MIN (t), -9) "CURNT_QTR_ST_DT",
ADD_MONTHS (MAX (LAST_DAY (t)), -9) "CURNT_QTR_END_DT",
ADD_MONTHS (MAX (LAST_DAY (t)), -12) "PREV_QTR_END_DT" --INTO V_CURRQTD_STRT ,V_CURRQTD_END,V_PRVQTD_STRT, V_PRVQTD_END
FROM (    SELECT ADD_MONTHS (TRUNC (TO_DATE ('20-jan-2012'), 'YYYY'),
LEVEL - 1)
t,
TO_CHAR (
TRUNC (TO_DATE ('20-jan-2012'), 'YYYY'),
LEVEL - 1),
'Q')
QTD
FROM DUAL
CONNECT BY LEVEL <= 12) A
WHERE A.QTD = 4;``````
output:
``````CURNT_QTR_ST_DT     CURNT_QTR_END_DT     PREV_QTR_ST_DT     PREV_QTR_END_DT
1/1/2012              3/31/2012        10/1/2011       12/31/2011``````
Cheers,
Manik.

Edited : removed to_date(to_date function
• ###### 8. Re: How to get the fiscal yr
``````select trunc( v_rprt_date, 'Q' ) CURNT_QTR_ST_DT
, add_months( trunc( v_rprt_date, 'Q' ), 3 ) - 1 CURNT_QTR_END_DT
, add_months( trunc( v_rprt_date, 'Q' ), -3 ) PREV_QTR_ST_DT
, trunc( v_rprt_date, 'Q' ) - 1 PREV_QTR_END_DT
, to_char( add_months( trunc( v_rprt_date, 'Q' ), -3 ), 'YYYY' ) fiscal_year
, to_char( add_months( trunc( v_rprt_date, 'Q' ), -3 ), 'Q' ) fiscal_quarter
from ( select to_date('20-jan-2012', 'dd-mon-yyyy' ) V_RPRT_DATE from dual )``````
Edited by: ascheffer on Jan 3, 2013 11:08 AM
• ###### 9. Re: How to get the fiscal yr
Hi,

Let me make sure I know what you want.
You want to pass a parameter (such as January 20, 2012) and have a query show the beginning and ending of the quarter that includes that date, and also the beginning and ending of the previous quarter. Is that right?

If so:
``````WITH     param          AS
(
SELECT     TO_DATE ( '20-Jan-2012'
, 'DD-Mon-YYYY'
)     AS t
FROM     dual
)
SELECT     TRUNC (t, 'Q')               AS curnt_qtr_st_dt
,     ADD_MONTHS ( TRUNC (t, 'Q')
, 3
) - 1          AS curnt_qtr_end_dt
,     ADD_MONTHS ( TRUNC (t, 'Q')
, -3
)               AS prev_qtr_st_dt
,     TRUNC (t, 'Q') - 1          AS prev_qtr_end_dt
FROM     param
;``````
Output:
``````CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-JAN-12 31-MAR-12 01-OCT-11 31-DEC-11``````
Calling TO_DATE with just 1 argument is legal, but is almost always a mistake. (I've worked with Oracle for over 20 years, and I don't remember ever needing to call TO_DATE with only 1 argument.) Call TO_DATE with 2 arguments:
(1) a string, such as '01-Jan-2012' containing the date information, and
(2) another string, such as 'DD-Mon-YYYY', that shows how the first string is formatted.
• ###### 10. Re: How to get the fiscal yr
Ur query will help only if the dates are between 01-jan-yyyy and 31-mar-yyyy other than that it's not working...
``````CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-APR-11 30-JUN-11 01-JAN-11 31-MAR-11``````
I've passed 20-apr-2012 as the report date.
• ###### 11. Re: How to get the fiscal yr
Thnx a lot.....Frank

Indeed, and it's working gr8... but as i m new m unable to implement the same into the function---I've to pass the output in four variable:

Output:
``````CURNT_QTR CURNT_QTR PREV_QTR_ PREV_QTR_
--------- --------- --------- ---------
01-JAN-12 31-MAR-12 01-OCT-11 31-DEC-11

AS
curnt_qtr_st_dt into V_CURRQTD_STRT
curnt_qtr_end_dt into  V_CURRQTD_END
prv_qtr_st_dt into V_PRVQTD_STRT
curnt_qtr_end_dt into V_PRVQTD_END``````
I've edited like below:
``````WITH     param          AS
(
SELECT     TO_DATE ( '20-mar-2012'
, 'DD-Mon-YYYY'
)     AS t
FROM     dual
)
SELECT     TRUNC (t, 'Q')               AS curnt_qtr_st_dt INTO V_CURRQTD_STRT
,     ADD_MONTHS ( TRUNC (t, 'Q')
, 3
) - 1          AS curnt_qtr_end_dt INTO V_CURRQTD_END
,     ADD_MONTHS ( TRUNC (t, 'Q')
, -3
)               AS prev_qtr_st_dt INTO V_PRVQTD_STRT
,     TRUNC (t, 'Q') - 1          AS prev_qtr_end_dt INTO V_PRVQTD_END
FROM     param
;``````
but its throwing error...
Kindly help...

Edited by: 977490 on Jan 3, 2013 3:17 AM
• ###### 12. Re: How to get the fiscal yr
``````declare
V_RPRT_DATE date;
V_CURRQTD_STRT date;
V_CURRQTD_END date;
V_PRVQTD_STRT date
V_PRVQTD_END date;
begin
V_RPRT_DATE := to_date( '20-jan-2012', 'dd-mon-yyyy' );
V_CURRQTD_STRT := trunc( v_rprt_date, 'Q' );
V_CURRQTD_END  := add_months( V_CURRQTD_STRT, 3 ) - 1;
V_PRVQTD_STRT  := add_months( V_CURRQTD_STRT, -3 );
V_PRVQTD_END   := V_CURRQTD_STRT - 1;
end;``````
• ###### 13. Re: How to get the fiscal yr
Hi,

You don't need the dual table much in PL/SQL.

Another thing that makes PL/SQL programming different from SQL is that, in SQL, you can assign an alias to a column, but you can only reference that alias in a super-query (or in an ORDER BY clause). In PL/SQL, you can reference a variable immediately after it is set.

In PL/SQL, once you have a target DATE in t, you can set the 4 variables like this:
``````v_currqtd_strt := TRUNC (t, 'Q');
v_currqtd_end  := ADD_MONTHS (v_currqtd_start, 3) - 1;