1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get year start date and end date based on period name?

Discussion in 'Oracle Apps Technical' started by jagadekara, Mar 26, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I need year start date and year end date based on period name. for example if I give input as 'MAR-12' then I need start date as '01-APR-2012' and end date as '31-MAR-13'. It's a fiscal year.

    So let me know possibilities....
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    here one of options...

    Code (SQL):


    SELECT
    MONTH,
    to_char(add_months(to_date (MONTH,'MON-RR'),1),'MON-RR') v_start,
    to_char(last_day (add_months(to_date (MONTH,'MON-RR'),12)),'MON-RR') v_end
    FROM (SELECT '&mon' MONTH FROM dual);

    SQL>

    MONTH  V_START         V_END
    ------ --------------- ---------------
    MAR-12 APR-12          MAR-13

     
     
    jagadekara likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Sergey,
    Thanks for your Reply.

    Your Query giving Periods but I need Dates(Also your query gives the following periods for MAY-13

    JUN-13 MAY-14

    It should give APR-13 MAY-14).

    Any how I got it.

    Here is my workout..

    SELECT year_start_date
    ,last_day(add_months(trunc(year_start_date,'YYYY'), 14)) year_end_date
    FROM gl_periods
    WHERE period_name='MAY-13';

    YEAR_START_DATE YEAR_END_DATE
    -----------------------------------
    01-APR-13 31-MAR-14
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hello, Jagadekara.
    Probably I not absolutely understood that it is necessary for you from a condition : :confused: :)
    " for example if I give input as 'MAR-12' then I need start date as '01-APR-2012' and end date as '31-MAR-13'. It's a fiscal year."

    I wanted to specify: if the list of dates for the period is necessary,
    then here it is possible to use such script(This request will return 395 rows for 'MAY-13'):

    Code (SQL):

    WITH
    params AS
    (SELECT 'MAY-13' period_name FROM dual)
    ,
    gl_periods AS
    (SELECT  
                period_name,
                add_months(to_date(period_name,'MON-RR'),-1) year_start,
                add_months(to_date(period_name,'MON-RR'),12) year_end
     
      FROM params
    )
     
    ,list_date_of_period (period_name,year_start,year_end,curr_date)
    AS(
    SELECT
            period_name,year_start,year_end,year_start curr_date
    FROM gl_periods WHERE period_name='MAY-13'
    UNION ALL
    SELECT
            ld.period_name,ld.year_start,ld.year_end,ld.curr_date+1
    FROM list_date_of_period ld
    WHERE curr_date < ld.year_end -1
    )
    SELECT * FROM list_date_of_period;

     
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    How to execute your query? I am using SQl Developer.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    The sql query which I showed , worked and in SQL Developer
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Sorry I didn't get you..
     
  8. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Jagadekara,

    Try with the below query.

    Code (SQL):

    SELECT MIN(gp1.start_date) , MAX(gp1.end_date)
    FROM   gl_periods gp1
    WHERE  gp1.period_set_name = 'Accounting' -- '<Period set name which is defined in ur organization>'
    AND    gp1.period_year =(        
                                SELECT gp2.period_year
                                FROM gl_periods gp2
                                WHERE gp2.period_name ='May-13'
                                AND  gp2.period_set_name = 'Accounting' -- '<Period set name which is defined in ur organization>'
                            )
     

    Regards
    Sambasiva Reddy.K
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Samba,

    But your query and my query both are returning same.

    My Query:

    SELECT year_start_date
    ,last_day(add_months(trunc(year_start_date,'YYYY') , 14)) year_end_date
    FROM gl_periods
    WHERE period_name=:p_PERIOD;

    When we give 'JAN-11'

    It shows '01-APR-2011' and '31'MAR-2012'

    But I need '01-APR-2010' and '31-MAR-2011'
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    The sql query which I showed , worked and in SQL Developer
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Sergey,

    I got below error while executing your query in SQL Developer.
     

    Attached Files:

    • 1.bmp
      File size:
      233.6 KB
      Views:
      10
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tell,Jagadekara, what is your Oracle version ?
     
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Old sytax :

    Code (SQL):

    WITH
    params AS
    (SELECT 'MAY-13' period_name FROM dual)
    ,
    gl_periods AS
    (SELECT  
                period_name,
                add_months(to_date(period_name,'MON-RR'),-1)   year_start,
                last_day(add_months(trunc(to_date(period_name,'MON-RR'),'YYYY'),14)) year_end
     
      FROM params
    )
     
    SELECT
          p.*,
          year_start + level - 1  curr_date
    FROM gl_periods p
    CONNECT BY level <= (year_end - year_start+1);


    SQL>

    PERIOD_NAME YEAR_START  YEAR_END    CURR_DATE
    ----------- ----------- ----------- -----------
    MAY-13      01.04.2013  31.03.2014  01.04.2013
    MAY-13      01.04.2013  31.03.2014  02.04.2013
    MAY-13      01.04.2013  31.03.2014  03.04.2013
    MAY-13      01.04.2013  31.03.2014  04.04.2013
    MAY-13      01.04.2013  31.03.2014  05.04.2013
    MAY-13      01.04.2013  31.03.2014  06.04.2013
    MAY-13      01.04.2013  31.03.2014  07.04.2013
    MAY-13      01.04.2013  31.03.2014  08.04.2013
    MAY-13      01.04.2013  31.03.2014  09.04.2013
    MAY-13      01.04.2013  31.03.2014  10.04.2013
    MAY-13      01.04.2013  31.03.2014  11.04.2013
    MAY-13      01.04.2013  31.03.2014  12.04.2013
    MAY-13      01.04.2013  31.03.2014  13.04.2013
    MAY-13      01.04.2013  31.03.2014  14.04.2013
    MAY-13      01.04.2013  31.03.2014  15.04.2013
    MAY-13      01.04.2013  31.03.2014  16.04.2013
    MAY-13      01.04.2013  31.03.2014  17.04.2013
    MAY-13      01.04.2013  31.03.2014  18.04.2013
    MAY-13      01.04.2013  31.03.2014  19.04.2013
    MAY-13      01.04.2013  31.03.2014  20.04.2013
    ..............................................................................................

    PERIOD_NAME YEAR_START  YEAR_END    CURR_DATE
    ----------- ----------- ----------- -----------
    MAY-13      01.04.2013  31.03.2014  23.03.2014
    MAY-13      01.04.2013  31.03.2014  24.03.2014
    MAY-13      01.04.2013  31.03.2014  25.03.2014
    MAY-13      01.04.2013  31.03.2014  26.03.2014
    MAY-13      01.04.2013  31.03.2014  27.03.2014
    MAY-13      01.04.2013  31.03.2014  28.03.2014
    MAY-13      01.04.2013  31.03.2014  29.03.2014
    MAY-13      01.04.2013  31.03.2014  30.03.2014
    MAY-13      01.04.2013  31.03.2014  31.03.2014

    365 ROWS selected




     
     
  15. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Still I didn't get you. Your query giving 365 records but I need one record
     
  16. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Code (SQL):


    WITH
    params AS
    (SELECT 'MAY-13' period_name FROM dual)
    ,res AS (
    SELECT  
                period_name,
                add_months(to_date(period_name,'MON-RR'),-1)   year_start,
    last_day(add_months(trunc(to_date(period_name,'MON-RR'),'YYYY'),14)) year_end
               
     
      FROM params
    )
    SELECT
       r.*,
       to_char(r.year_start,'MON-RR') year_start_str,
          to_char(r.year_end,'MON-RR') year_end_str
    FROM res  r;

    SQL>

    PERIOD_NAME YEAR_START  YEAR_END    YEAR_START_STR  YEAR_END_STR
    ----------- ----------- ----------- --------------- ---------------
    MAY-13          01.04.2013   31.03.2014    APR-13                 MAR-14

     
     
  17. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    For MAY-13 it is good.

    But for JUN-13 it is giving like this.

    PERIOD_NAME YEAR_START YEAR_END YEAR_START_STR YEAR_END_STR
    -------------- ----------- ----------- --------------- -----------------------
    JUN-13 01-MAY-13 31-MAR-14 MAY-13 MAR-14

    but I need

    PERIOD_NAME YEAR_START YEAR_END YEAR_START_STR YEAR_END_STR
    -------------- ----------- ----------- --------------- -----------------------
    JUN-13 01-APR-13 31-MAR-14 APR-13 MAR-14

    For JAN-13 I need

    PERIOD_NAME YEAR_START YEAR_END YEAR_START_STR YEAR_END_STR
    -------------- ----------- ----------- --------------- -----------------------
    JAN-13 01-APR-12 31-MAR-13 APR-12 MAR-13
     
  18. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):


    WITH
    params AS
    (SELECT to_date ('JAN-13','MON-RR') dd  FROM dual  UNION ALL
    SELECT to_date ('MAY-13','MON-RR') dd  FROM dual  UNION ALL
    SELECT to_date ('JUN-13','MON-RR') dd  FROM dual  UNION ALL
    SELECT to_date ('NOV-13','MON-RR') dd  FROM dual
    )
    ,res AS (
    SELECT
           dd,      
           add_months(dd,-1*
           CASE
                WHEN   to_char(dd,'Q') = '1' THEN
                    9
                ELSE
                   to_number(to_char(dd,'MM'))-to_char(add_months(trunc(dd,'YYYY'),3),'MM')
                END  
               ) year_start
           ,    
           last_day(add_months(trunc(dd,'YYYY'),
                         CASE
                         WHEN   to_char(dd,'Q') = '1' THEN
                             2
                         ELSE
                             14    
                         END    
                          )
                  ) year_end                                        
    FROM params
    )
    SELECT
         r.*
         ,to_char(r.year_start,'MON-RR') year_start_str
         ,to_char(r.year_end,'MON-RR') year_end_str
    FROM  res r;

    SQL >



    SQL>
     

    DD            YEAR_START  YEAR_END    YEAR_START_STR  YEAR_END_STR
    ----------- -----------   ----------- ---------------       ---------------
    01.01.2013  01.04.2012   31.03.2013   APR-12                  MAR-13
    01.05.2013  01.04.2013   31.03.2014   APR-13                  MAR-14
    01.06.2013  01.04.2013   31.03.2014   APR-13                  MAR-14
    01.11.2013  01.04.2013   31.03.2014   APR-13                  MAR-14
     

     
     
    jagadekara likes this.
  19. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    It's working fine. Once again Thanks A lot...