Forum Stats

  • 3,876,221 Users
  • 2,267,082 Discussions
  • 7,912,474 Comments

Discussions

Getting quarters with two input date-parameters

748467
748467 Member Posts: 10
edited Feb 8, 2010 10:55AM in SQL & PL/SQL
I have to find a way to get all the quarters between a startdate and an enddate, like 01-04-2009 (startdate) and 31-11-2009 (enddate). An extra condition is to count an extra quarter above the enddate, so the last quarter is 2010-01 (first quarter of 2010).

The desired result will be:

2009-02
2009-03
2009-04
2010-01

The start- and enddate are inputparameters.

How do I get this result?
«1

Answers

  • 6363
    6363 Member Posts: 6,642
    edited Jan 22, 2010 8:08AM
    Here is an example
    SQL> var start_date varchar2(10)
    SQL> var end_date varchar2(10)
    SQL> exec :start_date := '01/05/2010'; :end_date := '09/15/2010'
    
    PL/SQL procedure successfully completed.
    
    SQL> select
      2    trunc(add_months(to_date(:start_date,'MM/DD/YYYY'),3 * level),'Q')
      3  from
      4    dual
      5  connect by
      6    level <=
      7      (months_between(to_date(:end_date,'MM/DD/YYYY'),to_date(:start_date,'MM/DD/YYYY')) + 3) / 3;
    
    TRUNC(ADD_
    ----------
    04/01/2010
    07/01/2010
    10/01/2010
    
    SQL>
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions218.htm#SQLRF06151
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions009.htm#SQLRF00603
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions100.htm#SQLRF00669
  • 748467
    748467 Member Posts: 10
    edited Jan 22, 2010 8:53AM
    This should almost do the trick, but now I have to count an extra quarter to the end date. The ordening of the result is also not correct:

    select distinct to_char(qdate,'YYYYQ') quarter
    from ( select add_months(to_date(:start_date,'DD/MM/YYYY'),m) qdate
    from ( select level m
    from dual
    connect by level < ceil(months_between(to_date(:end_date,'DD/MM/YYYY'),to_date(:start_date,'DD/MM/YYYY')))
    )
    ) order by quarter

    So the result is:

    20092
    20093
    20094

    Anyone who knows how to add an extra quarter?

    Edited by: user12493601 on 22-jan-2010 14:52
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Jan 22, 2010 8:57AM
    Just add 3 months I guess:
    SQL> SELECT DISTINCT to_char(qdate, 'YYYY') || '-Q' || to_char(qdate, 'Q') quarter
      2    FROM (SELECT add_months(to_date('01/04/2009', 'DD/MM/YYYY'), m) qdate
      3            FROM (SELECT LEVEL m
      4                    FROM dual
      5                  CONNECT BY LEVEL - 3 < ceil(months_between(to_date('30/11/2009', 'DD/MM/YYYY'),
      6                                                         to_date('01/04/2009', 'DD/MM/YYYY')))))
      7  ORDER BY quarter
      8  /
     
    QUARTER
    -------
    2009-Q2
    2009-Q3
    2009-Q4
    2010-Q1
     
    SQL> 
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Jan 22, 2010 8:58AM
    Post duplicated somehow...

    Edited by: fsitja on Jan 22, 2010 11:58 AM
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    edited Jan 22, 2010 9:18AM
    Hi,

    You're getting a quarter for each 3 values of LEVEL.
    To get one more quarter, add 3 to the limit on LEVEL:
    select distinct 
            to_char(qdate,'YYYYQ') quarter
    from 	( select  add_months ( to_date (:start_date, 'DD/MM/YYYY')
    	  	  	     , m
    			     ) qdate
    	  from 	  ( select level m 
    	  	    from   dual
    		    connect by level < 3 +	-- Added
    		    	       	       ceil ( months_between ( to_date (:end_date,  'DD/MM/YYYY')
    		    	       	     	   	  	     , to_date (:start_date,'DD/MM/YYYY')
    					     )		     )
    		  )
    	) 
    order by quarter
    ;
    Edited by: Frank Kulash on Jan 22, 2010 9:09 AM

    By the way, you don't need all those sub-queries.
    SELECT	TO_CHAR ( ADD_MONTHS ( TO_DATE (:start_date, :date_format)
    		  	     , 3 * (LEVEL - 1)
    			     )
    		, 'YYYYQ'
    		)
    FROM	dual
    CONNECT BY	LEVEL <= 1 + CEIL ( MONTHS_BETWEEN ( TO_DATE (:end_date,   :date_format)
    		      	     		      	   , TO_DATE (:start_date, :date_format)
    					      	   )
    			     	   / 3
    			     	   )
    ;
  • 6363
    6363 Member Posts: 6,642
    This maybe
    SQL> var start_date varchar2(10)
    SQL> var end_date varchar2(10)
    SQL> exec :start_date := '01/05/2010'; :end_date := '09/15/2010'
    
    PL/SQL procedure successfully completed.
    
    SQL> select
      2      to_char(quarter_date, 'YYYY')
      3          || to_char( trunc(months_between(quarter_date, trunc(quarter_date,'Y')) / 3) + 1,'00')
      4  from
      5      (
      6      select
      7        trunc(add_months(to_date(:start_date,'MM/DD/YYYY'),3 * level),'Q') quarter_date
      8      from
      9        dual
     10      connect by
     11        level <=
     12          (months_between(to_date(:end_date,'MM/DD/YYYY'),to_date(:start_date,'MM/DD/YYYY')) + 3) / 3
     13      );
    
    TO_CHAR
    -------
    2010 02
    2010 03
    2010 04
    
    SQL> exec :start_date := '04/01/2009'; :end_date := '12/31/2009'
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    
    TO_CHAR
    -------
    2009 03
    2009 04
    2010 01
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    Isn't it 1 level per month in this case? That's the distinct's doing I believe:
    SQL> SELECT DISTINCT to_char(qdate, 'YYYY') || '-Q' || to_char(qdate, 'Q') quarter, m
      2    FROM (SELECT add_months(to_date('01/04/2009', 'DD/MM/YYYY'), m) qdate, m
      3            FROM (SELECT LEVEL m
      4                    FROM dual
      5                  CONNECT BY LEVEL - 3 < ceil(months_between(to_date('30/11/2009', 'DD/MM/YYYY'),
      6                                                         to_date('01/04/2009', 'DD/MM/YYYY')))))
      7  ORDER BY quarter;
     
    QUARTER          M
    ------- ----------
    2009-Q2          2
    2009-Q2          1
    2009-Q3          4
    2009-Q3          3
    2009-Q3          5
    2009-Q4          6
    2009-Q4          8
    2009-Q4          7
    2010-Q1         10
    2010-Q1          9
     
    10 rows selected
     
    SQL> 
  • 659537
    659537 Member Posts: 309
    select distinct to_char(qdate,'YYYYQ') quarter
    from ( select add_months(to_date('1/4/2009','DD/MM/YYYY'),m) qdate
    from ( select level m 
    from dual
    connect by level < ceil(months_between(add_months(to_date('31/12/2009','DD/MM/YYYY'),3),to_date('1/4/2009','DD/MM/YYYY')))
    )
    ) order by quarter
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond
    Hi,
    fsitja wrote:
    Isn't it 1 level per month in this case? That's the distinct's doing I believe: ...
    Right; thanks.
    I corrected my earlier message.

    I find it's confusing to use DISTINCT, and inefficient to generate all the months only to do a DISTINCT later. Sometimes, inefficient execution can be justified by simpler coding, but I don't think that's the case here.
    Repeated from my earlier mesage, here's how I would do this:
    SELECT	TO_CHAR ( ADD_MONTHS ( TO_DATE (:start_date, :date_format)
    		  	     , 3 * (LEVEL - 1)
    			     )
    		, 'YYYYQ'
    		)
    FROM	dual
    CONNECT BY	LEVEL <= 1 + CEIL ( MONTHS_BETWEEN ( TO_DATE (:end_date,   :date_format)
    		      	     		      	   , TO_DATE (:start_date, :date_format)
    					      	   )
    			     	   / 3
    			     	   )
    ;
  • 6363
    6363 Member Posts: 6,642
    Frank Kulash wrote:
    Hi,
    fsitja wrote:
    Isn't it 1 level per month in this case? That's the distinct's doing I believe: ...
    Right; thanks.
    I corrected my earlier message.

    I find it's confusing to use DISTINCT, and inefficient to generate all the months only to do a DISTINCT later.
    Yes, I believe I sort of said that right at the beginning, just had the output format wrong

    4046203

    An on second attempt forgot I could use 'YYYYQ' format which you just reminded me about - so the corrected version should have been just
    SQL> var start_date varchar2(10)
    SQL> var end_date varchar2(10)
    SQL> exec :start_date := '04/01/2009'; :end_date := '12/31/2009'
    
    PL/SQL procedure successfully completed.
    
    SQL> select
      2    to_char(trunc(add_months(to_date(:start_date,'MM/DD/YYYY'),3 * level),'Q'),'YYYYQ') quarter_date
      3  from
      4    dual
      5  connect by
      6    level <=
      7      (months_between(to_date(:end_date,'MM/DD/YYYY'),to_date(:start_date,'MM/DD/YYYY')) + 3) / 3;
    
    QUART
    -----
    20093
    20094
    20101
This discussion has been closed.