Re: tricky pivot query
- From: "Alexander Fatkulin" <afatkulin@xxxxxxxxx>
- To: ricks12345@xxxxxxxxx
- Date: Wed, 11 Jun 2008 00:54:47 -0400
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:
