Forum Stats

  • 3,851,566 Users
  • 2,263,999 Discussions
  • 7,904,781 Comments

Discussions

Getting quarters with two input date-parameters

2»

Answers

  • 748467
    748467 Member Posts: 10
    Thanks for your input guys!
  • 748467
    748467 Member Posts: 10
    Suddenly there are still problems with getting the right quarters... I have skipped the CONNECT BY clauses and used the following function:
    CREATE OR REPLACE function SAI.fnc_get_sales_quarters(p_start_date in date, p_end_date in date)
    return sales_quarters_t
    pipelined
    is
      l_num_quarters number;
      l_start_date   date;
      l_out          number;
    begin
      l_num_quarters := round(months_between(p_end_date,p_start_date)) / 3 + 2;
      for i in 1..l_num_quarters 
      loop
        l_start_date := trunc(add_months(p_start_date, (i -1) * 3),'Q');
        l_out        := to_number(to_char(l_start_date,'YYYY')) || to_number(to_char(l_start_date,'Q'));
        pipe row(l_out);
      end loop;
      return;
    end;
    /
    When I the startdate is: 23 jun 2009 and the enddate is: 23 jul 2009 the function had to returned:

    20092
    20093
    20094

    but the function returned:

    20092
    20093

    Anyone knows what's going wrong?
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like recursive with clause B-)
    I have used PostgreSQL8.4 because I do not have Oracle11gR2 :8}
    with recursive rec(Val) as(
    select date '2009-04-01'
    union all
    select (Val+InterVal '3 month')::date
      from rec
     where Val < date '2009-11-30')
    select to_char(Val,'yyyy-q') from rec;
    
     to_char
    ---------
     2009-2
     2009-3
     2009-4
     2010-1
  • 748467
    748467 Member Posts: 10
    Nice thing, but I need the function that I've created...
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    edited Feb 8, 2010 9:20AM
    Hi,

    The problem is using MONTHS_BETWEEN (y, x) to find the number of quarters.
    If x is February 8, 2010 and y is March 8, 2010, then MONTHS_BETWEEN returns 1, and we're talking about 1 quarter, but
    if x is March 8, 2010 and y is April 8, 2010, then MONTHS_BETWEEN still returns 1, and we're talking about 1 quarters.
    Let's TRUNCate x to the beginning of its quarter, so that we know differences < 3 months will always be in only one quarter.
    Try this:
    CREATE OR REPLACE function SAI.fnc_get_sales_quarters(p_start_date in date, p_end_date in date)
    return sales_quarters_t
    pipelined
    is
      l_num_quarters number;
      l_start_date   date	:= TRUNC (p_start_date, 'Q');
      l_out          number;
    begin
      l_num_quarters := FLOOR ( months_between ( p_end_date
      		    	   		   , l_start_date
    					   )
    			  / 3
    			  ) + 2;
      for i in 1..l_num_quarters 
      loop
        l_out        := to_number (to_char (l_start_date, 'YYYYQ'));
        l_start_date := add_months (l_start_date,  3);
        pipe row(l_out);
      end loop;
      return;
    end;
    / 
  • 748467
    748467 Member Posts: 10
    Thanks Frank...

    I think the following code should also do the trick:
    CREATE OR REPLACE function fnc_get_sales_quarters(p_start_date in date, p_end_date in date)
    return sales_quarters_t
    pipelined
    is
      l_temp_date    date;
      l_out          number;
    begin
       l_temp_date := trunc(p_start_date,'Q');
       while l_temp_date < trunc(p_end_date)
       loop
         l_out := to_char(l_temp_date,'yyyyq');
         pipe row(l_out);
         l_temp_date := add_months(l_temp_date,3);
       end loop;
       l_out := to_char(l_temp_date,'yyyyq');
       pipe row(l_out);
    end;
    /
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,720 Red Diamond
    edited Feb 8, 2010 10:55AM
    Frank Kulash wrote:

    The problem is using MONTHS_BETWEEN (y, x) to find the number of quarters.
    Well, yes. But the real problem is using a counter-intuitive approach to solving sequential items task. We have a function that returns set of start-of-quarter dates for quarters between start_dt and end_dt. Assume function is f(dt)=(qtr<sub>1</sub>,...,qtr<sub>N+1</sub>). So we start with f(start_dt) and keep going with f(start_dt + 3*N) and then stop when f(start_dt + 3*(N+1)) > end_dt. Most of solutions (and not just in this post) use CONNECT BY LEVEL or PL/SQL LOOP. Problem is that instead of ending CONNECT BY LEVEL or PL/SQL LOOP based on condition against task function* (in this particular case it is qtr<sub>N1</sub> > end_date) posters try to use task function arguments+* to precalculate N and use it as stop condition which in many cases, including this, is counter-intuitive. Compare complex logic of converting start_dt, end_dt to N to simple logic of:
    CREATE OR REPLACE function SAI.fnc_get_sales_quarters(p_start_date in date, p_end_date in date)
    return sales_quarters_t
    pipelined
    is
      l_start_date   date	:= TRUNC (p_start_date, 'Q');
      l_out          number;
    begin
      loop
        l_out        := to_number (to_char (l_start_date, 'YYYYQ'));
        pipe row(l_out);
        l_start_date := add_months (l_start_date,  3);
        exit when l_start_date > p_end_date
      end loop;
      return;
    end;
    / 
    Same applies to CONNECT BY solution:
    select  trunc(to_date (:start_date,'DD/MM/YYYY') + (level - 1) * 3,'Q') quarter
      from  dual
      connect by trunc(to_date (:start_date,'DD/MM/YYYY') + (level - 1) * 3,'Q') <= to_date (:end_date,'DD/MM/YYYY')
      order by quarter
    ;
    SY.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,475 Red Diamond
    Hi,
    user9033582 wrote:
    I think the following code should also do the trick:
    CREATE OR REPLACE function fnc_get_sales_quarters(p_start_date in date, p_end_date in date)
    return sales_quarters_t
    pipelined
    is
    l_temp_date    date;
    l_out          number;
    begin
    l_temp_date := trunc(p_start_date,'Q');
    while l_temp_date < trunc(p_end_date) ...
    I think you want <=, not just <, in the WHILE condition. Otherwise, you'll miss one quarter when p_end_date happens to be the beginning of a quarter.
    What is the purpose of TRUNC here?
    I think you want:
       while l_temp_date <= p_end_date
This discussion has been closed.