Re: Matrix Report Headache

A copy of this was sent to Thenardier_at_POBoxes.com (if that email address didn't require changing) On Tue, 03 Nov 1998 12:45:47 GMT, you wrote:

>hi folks,
>
>i'm working on a report which shows the total sales figures of each
>sales person for the last 12 months. i want the report to look like this:
>
> (current mth)
>Name 12/1997 01/1998 02/1998 ... ... 11/1998 Total
>------ ------- ------- ------- ... ... ------- -------
>SalesA 10 10 20 ... ... 1 999
>SalesB 10 10 20 ... ... 1 999
>SalesC 10 10 20 ... ... 1 999
>...
>
>u can see the month range is dynamic.
>
>i know that matrix report can help me to build a report like this.
>my headache is, say, if there is a month in which no sales is made.
>(everything can happen, who know? :) ) the corresponding column
>will disappear! then i'll propably see 02/1998 column followed by
>04/1998. but i need to show the sales figures are 0s in 03/1998!
>
>what should i do?
>

You don't say what your original table looks like but assuming it looks something like:

SQL> create table sales

  2  (     person        varchar2(25),
  3      salesdate    date,
  4      amount        number

And has data such as:

SQL> insert into sales values ( 'sales a', '01-jan-98', 10 );
SQL> insert into sales values ( 'sales a', '11-feb-98', 20 );
SQL> insert into sales values ( 'sales a', '31-mar-98', 40 );
SQL> insert into sales values ( 'sales a', '04-may-98', 40 );
SQL> 
SQL> insert into sales values ( 'sales b', '01-may-98', 10 );
SQL> insert into sales values ( 'sales b', '11-jun-98', 20 );
SQL> insert into sales values ( 'sales b', '25-jul-98', 40 );
SQL> insert into sales values ( 'sales b', '04-sep-98', 40 );

Then a query such as:

SQL> select person,
  2 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-06),'mon'), amount, 0 ))"6m ago",   3 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-05),'mon'), amount, 0 ))"5m ago",   4 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-04),'mon'), amount, 0 ))"4m ago",   5 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-03),'mon'), amount, 0 ))"3m ago",   6 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-02),'mon'), amount, 0 ))"2m ago",   7 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-01),'mon'), amount, 0 ))"1m ago",   8 sum(decode( trunc(salesdate,'mon'), trunc(add_months(sysdate,-00),'mon'), amount, 0 ))"This month"   9 from sales
 10 group by person
 11 /

PERSON 6m ago 5m ago 4m ago 3m ago 2m ago 1m ago This month ---------- -------- -------- -------- -------- -------- -------- ----------

sales a          40        0        0        0        0        0          0
sales b          10       20       40        0       40        0          0

will get what you want and not 'miss' any columns (columns with no data return 0)... This query implements a sliding window based on todays date and goes back 6 months -- just add more columns to go back further in time or make 'sysdate' a bind variable you supply to get the dates to be whatever you want...

>thanx in advance.
>
>thenard
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.