to give you an idea... SQL> create table daily_summary as 2 select day_id, empno, sum(sales) sales_rollup 3 from ( 4 select to_number(to_char(trunc(sysdate, 'yy')+trunc(dbms_random.value(0, 180)), 'yyyymmdd')) day_id, 5 trunc(dbms_random.value(1, 10)) empno, 6 round(dbms_random.value(1, 100), 2) sales 7 from dual 8 connect by level <= 10000 9 ) group by day_id, empno; Table created SQL> SQL> create table monthly_summary as 2 select to_number(to_char(to_date(to_char(day_id), 'yyyymmdd'), 'yyyymm')) month_id, 3 empno, 4 sum(sales_rollup) sales_rollup 5 from daily_summary 6 group by to_number(to_char(to_date(to_char(day_id), 'yyyymmdd'), 'yyyymm')), empno; Table created SQL> SQL> select * from 2 ( 3 select sum(sales_rollup) sales_0 4 from daily_summary 5 where empno=7 6 and day_id>=to_number(to_char(trunc(sysdate, 'mm'), 'yyyymmdd')) 7 ), 8 ( 9 select sum(case month_id when to_number(to_char(add_months(trunc(sysdate, 'mm'), -1), 'yyyymm')) then sales_rollup end) sales_1, 10 sum(case month_id when to_number(to_char(add_months(trunc(sysdate, 'mm'), -2), 'yyyymm')) then sales_rollup end) sales_2, 11 sum(case month_id when to_number(to_char(add_months(trunc(sysdate, 'mm'), -3), 'yyyymm')) then sales_rollup end) sales_3 12 from monthly_summary 13 where empno=7 14 and month_id >= to_number(to_char(add_months(trunc(sysdate, 'mm'), -3), 'yyyymm')) 15 ); SALES_0 SALES_1 SALES_2 SALES_3 ---------- ---------- ---------- ---------- 9127.67 9582.89 7906.03 11425.81 On Tue, Jun 10, 2008 at 11:28 PM, Rick Ricky <ricks12345@xxxxxxxxx> wrote: > 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 day > monthly_summary > month_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 sales > sales_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... > -- Alex Fatkulin, The Pythian Group, http://www.pythian.com/blogs/author/alexf