Forum Stats

  • 3,770,488 Users
  • 2,253,125 Discussions
  • 7,875,484 Comments

Discussions

create QUARTERLY LIST

Gor_Mahia
Gor_Mahia Member Posts: 1,110 Bronze Badge
edited Oct 17, 2021 7:33PM in SQL & PL/SQL

All,

iam trying to create a list of quarterly date ranges and a counter as below for the given year ,

DATE RANGE : COUNTER

10/01/2021 to 12/31/2021 : 4

07/01/2021 to 09/30/2021 : 3

04/01/2021 to 06/30/2021 : 2

01/01/2021 to 03/31/2021 : 1


or,


10/01/2020 to 12/31/2020 : 4

07/01/2020 to 09/30/2020 : 3

04/01/2020 to 06/30/2020 : 2

01/01/2020 to 03/31/2020 : 1


my query so far is like ....


  select to_char( add_months( dt, (q-1)*3 ),'MM/DD/RRRR') ||' to '|| to_char( last_day(add_months( dt, (q-1)*3+2 ) ), 'MM/DD/RRRR') D

   from (

  select TRUNC(add_months(:MySysDate,-0),'YEAR')  rownum q

   from all_users

   where rownum <= 4 

      ) 

      order by 1 desc


i get the first column but not the "COUNTER" Column.


any help is appreciated.

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi,

    If you'd want to use CONNECT BY rather than a recursive WITH clause, you can do it this way:

    WITH  quarters (start_date, counter)  AS
    (
      SELECT ADD_MONTHS ( TO_DATE ( '01/01/' || :year_wanted
      	  	    	      , 'MM/DD/YYYY' -- use YYYY, not RRRR
    			      )
      	  	    , 3 * (LEVEL - 1)
      		    )
      ,     LEVEL		   
      FROM  dual
      CONNECT BY LEVEL <= 4
    )
    SELECT   start_date
    ,	 ADD_MONTHS (start_date, 3) - 1 AS end_date
    ,	 counter
    FROM     quarters
    ORDER BY counter DESC
    ;
    


    Gor_Mahia

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @Gor_Mahia

    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. If there is no sample data, and yu just want to generate something from dual (say), then say so clearly. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,110 Bronze Badge
    edited Oct 17, 2021 8:20PM

    Frank Kula

    my stated question doesn't involve any table/view object its fully query-derived .

    thanks

  • mathguy
    mathguy Member Posts: 10,167 Blue Diamond

    i get the first column [...]

    No, you don't. Not from the query you posted. True, you said your query is "like" the one you posted, but perhaps not exactly the one you posted... but then, why aren't you posting the "exact" query you tried, not one "like" it?

    The query you posted has an obvious syntax mistake: missing comma between the two columns in the subquery. After I fix that, there's another error about DT: invalid identifier (perhaps in your real query you named the first column in the subquery as DT?) Etc. Please show us the EXACT query you tried. Also explain what you were trying to achieve by subtracting 0 months from something (same as not subtracting in the first place). And why you expected to get two columns when your SELECT seems to have only one expression in it.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Oct 17, 2021 8:38PM

    Hi,

    If you want to generate four rows (from scratch, not from some table of yours) with the beginning and ending dates of the quarters of that year, like this for the year 2020:

    START_DATE END_DATE   COUNTER
    ---------- ---------- ----------
    01/10/2020 31/12/2020     4
    01/07/2020 30/09/2020     3
    01/04/2020 30/06/2020     2
    01/01/2020 31/03/2020     1
    

    here's one way to do it:

    WITH  quarters (start_date, counter)  AS
    (
      SELECT  TO_DATE ( '01/01/' || :year_wanted
      	  	  , 'MM/DD/YYYY'  -- Use YYYY, not RRRR
    		  )
      ,        1
      FROM     dual
    UNION ALL
      SELECT  ADD_MONTHS (start_date, 3)
      ,	  counter + 1
      FROM    quarters
      WHERE   counter < 4
    )
    SELECT   start_date
    ,	 ADD_MONTHS (start_date, 3) - 1 AS end_date
    ,	 counter
    FROM     quarters
    ORDER BY counter DESC
    ;
    

    :year_wanted is a four-character string.

    If you want the first quarter of the year to start on another month, just change the hard-coded '01/01/'

    If you want a different number of quarters, just change the hard-coded 4.

    Gor_Mahia
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi,

    If you'd want to use CONNECT BY rather than a recursive WITH clause, you can do it this way:

    WITH  quarters (start_date, counter)  AS
    (
      SELECT ADD_MONTHS ( TO_DATE ( '01/01/' || :year_wanted
      	  	    	      , 'MM/DD/YYYY' -- use YYYY, not RRRR
    			      )
      	  	    , 3 * (LEVEL - 1)
      		    )
      ,     LEVEL		   
      FROM  dual
      CONNECT BY LEVEL <= 4
    )
    SELECT   start_date
    ,	 ADD_MONTHS (start_date, 3) - 1 AS end_date
    ,	 counter
    FROM     quarters
    ORDER BY counter DESC
    ;
    


    Gor_Mahia
  • User_H3J7U
    User_H3J7U Member Posts: 693 Silver Trophy
    select
      to_char(dt + interval '3' month *  grouping_id(0,1),          'yyyy-mm-dd" to "') ||
      to_char(dt + interval '3' month * (grouping_id(0,1) + 1) - 1, 'yyyy-mm-dd') qs,
      grouping_id(0,1) + 1 qn
    from (select trunc(sysdate, 'y') dt from dual) group by dt, cube(0,1);
    
    QS                               QN
    ------------------------ ----------
    2021-10-01 to 2021-12-31          4
    2021-07-01 to 2021-09-30          3
    2021-04-01 to 2021-06-30          2
    2021-01-01 to 2021-03-31          1
    


    User_WI23P