RE: tricky pivot query

You can use case statements to get the data you want. I'm not sure I
understand your structure at this hour, but I'll give you an idea and maybe
you can fill in the rest.

 

Select day_id

Sum(Case when to_date(day_id,' YYYYMMDD') > trunc(sysdate,'MM') then
sales_rollup else 0 end)  sales_1,

Sum(Case when to_date(day_id,' YYYYMMDD')  between
add_month(trunc(sysdate,'MM'),-1) and  trunc(sysdate,'MM')-1 then
sales_rollup else 0 end)  sales_2,

Sum(Case when to_date(day_id,' YYYYMMDD')  between
add_month(trunc(sysdate,'MM'),-2) and  trunc(sysdate,'MM')-1 then
sales_rollup else 0 end)  sales_3,

From .

Where ..

Group by day_id;

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rick Ricky
Sent: Tuesday, June 10, 2008 11:29 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: tricky pivot query

 

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

Other related posts: