tricky pivot query

Argh!! Why don't people store dates as DATESs??? </rant>

Anyway I think you may be overcomplicating things, the model clause shouldn't be needed. How about something like this (untested):

create or replace function f_results(p_empno in number) return sys_refcursor is

   l_ret sys_refcursor;
begin

   open l_ret for

      select empno
      , max(decode(mth, trunc(sysdate, 'MM'), sales_rollup, null)) as sales_0
      , max(decode(mth, add_months(trunc(sysdate, 'MM'), -1), sales_rollup,
null)) as sales_1
      , max(decode(mth, add_months(trunc(sysdate, 'MM'), -2), sales_rollup,
null)) as sales_2
      , max(decode(mth, add_months(trunc(sysdate, 'MM'), -3), sales_rollup,
null)) as sales_3
      from (
         select empno
         , to_date(to_char(month_id, 'fm000000')||'01', 'YYYYMMDD') as mth
         , sales_rollup
         from monthly_summary
         where to_date(to_char(month_id, 'fm000000')||'01', 'YYYYMMDD') >=
add_months(trunc(sysdate, 'MM'), -3)
         and empno = p_empno
         union all
         select empno
         , trunc(sysdate, 'MM') as mth
         , sum(sales_rollup) as sales_rollup
         from daily summary
         where to_date(to_char(day_id, 'fm00000000'), 'YYYYMMDD') >=
trunc(sysdate, 'MM')
         and empno = p_empno
         group by empno
         , trunc(sysdate, 'MM')
      )
      group by empno;

      return l_ret;

cheers,
Anthony

On Wed Jun 11 11:28 , "Rick Ricky" sent:

>I am pretty sure I need to use the model clause to do this efficient. I am
reading the docs and examples, but I am a bit stumped. I have two tables
>daily_summary ( day_id number,
> empno number, sales_rollup number);
>monthly_summary ( month_id number, empno number, sales_rollup number);
>daily_summary table
>day_id is a number representing a date of the form YYYYMMDD. This is a group by
of the sales for a given daymonthly_summarymonth_id is a number representing a date of the form YYYYMM
>this is a rollup of daily_summary by month.
>I am given an empno. I need to return
>empno, sales_0,sales_1,sales_2,sales_3
>sales_0 current months sales. So if this is june 10th, I need to rollup the
first 10 days of the month from the daily_summary
>sales_1 is last months rollup of sales (I have this in monthly_summary)sales_2
is 2 months ago rollup of salessales_3 is 3 months ago rollup of sales
>I am doing this in the database and passing back a ref cursor. So i need a pivot
query, I need a group by (I think) and I need it to be dynamic.
>
>any suggestions? The examples are all more basic than this. am I going in the
correct direction with the model clause? I really don't want to use alot of pl/sql to build this...

--
http://www.freelists.org/webpage/oracle-l