This discussion is archived
13 Replies Latest reply: Jan 3, 2013 3:32 AM by ascheffer RSS

How to get the fiscal yr

980493 Newbie
Currently Being Moderated
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
    BluShadow Guru Moderator
    Currently Being Moderated
    {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
    980493 Newbie
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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
    980493 Newbie
    Currently Being Moderated
    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

    Thnx in advance..
  • 5. Re: How to get the fiscal yr
    ascheffer Expert
    Currently Being Moderated
    You have an absurd query, so why don't you expect absurd results :)
    TO_DATE(TO_DATE can't be correct

    Did you read the faq about asking questions?
    Did you understand that you need to clear about your expectations?
    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
    980493 Newbie
    Currently Being Moderated
    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
    Manik Expert
    Currently Being Moderated
    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 (MIN (t), -12) "PREV_QTR_ST_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 (
                          ADD_MONTHS (
                             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
    ascheffer Expert
    Currently Being Moderated
    How about this
    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
    Added fiscal_year and quarter
  • 9. Re: How to get the fiscal yr
    Frank Kulash Guru
    Currently Being Moderated
    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
    980493 Newbie
    Currently Being Moderated
    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
    980493 Newbie
    Currently Being Moderated
    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
    ascheffer Expert
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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;
    v_prvqtd_strt  := ADD_MONTHS (v_currqtd_start, -3);
    v_prvqtd_end   := v_currqtd_start - 1;
    Use SELECT ... INTO in PL/SQL when data is in a table, and you need to copy that data into variables. (It looked like you forgot the INTO clause in your last message.)
    However, in this case, there is no data in any table that you need to copy, so there is no need for a SELECT statement.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points