tricky pivot query

From: Rick Ricky <>
Date: Tue, 10 Jun 2008 23:28:30 -0400
Message-ID: <>

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

Received on Tue Jun 10 2008 - 22:28:30 CDT

Original text of this message