Getting quarters with two input date-parameters

748467

Thanks for your input guys!

748467

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

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

Nice thing, but I need the function that I've created...

Frank Kulash

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

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

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₁,...,qtr_N+1). 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_N1 > 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

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