Forum Stats

  • 3,837,605 Users
  • 2,262,272 Discussions
  • 7,900,326 Comments

Discussions

Categories

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?
· Share on TwitterShare on Facebook
«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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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>
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 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
			     	   )
;
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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>
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,219 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
			     	   )
;
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
This discussion has been closed.