Re: tricky pivot query

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

Other related posts: