13 Replies Latest reply: Jan 3, 2013 5:47 AM by Frank Kulash RSS

    How to get the fiscal yr

    980493
      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
          {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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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.