Re: tricky pivot query

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Wed, 11 Jun 2008 00:54:47 -0400
Message-ID: <49d668000806102154j77dc985co9b9966aa9edda377@mail.gmail.com>

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_at_gmail.com> 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

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

Received on Tue Jun 10 2008 - 23:54:47 CDT