Re: How to use SQL to get forcast records?

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/28
Message-ID: <333d286a.3266537@newshost>#1/1

On Tue, 25 Mar 1997 15:49:44 GMT, limke_at_letterbox.com (kelim) wrote:

>Hello, I'm facing this problem with generating a report that
>generate forecast for different deparment.
>The database is :
>
>Department Date Products SalesFigures
>----------------- -------- -------------- ----------------
>
>
>where the date data is store in a single column.
>I need to compare the date with the system date,
>get the month, add six months, and get the figures
>in a new table with the months as headings:
>
>eg.:
> March +1 +2 ......
>

I think something like:

create view sales_v
as
select dept,
trunc(months_between(trunc(sysdate,'MON'),trunc(fdate,'MON'))) mbtwn,

       sysdate today, fdate,
       sales

from sales
/

select
dept,

sum( decode( mbtwn, 0, sales, 0 )) March,
sum( decode( mbtwn, 1, sales, 0 )) April,
sum( decode( mbtwn, 2, sales, 0 )) May,
sum( decode( mbtwn, 3, sales, 0 )) June,
sum( decode( mbtwn, 4, sales, 0 )) July,
sum( decode( mbtwn, 5, sales, 0 )) August

from sales_v
group by dept
/

on a table that looks like:

SQL> desc sales

 Name                            Null?    Type
 ------------------------------- -------- ----
 DEPT                                     NUMBER
 FDATE                                    DATE
 PROD                                     VARCHAR2(20)
 SALES                                    NUMBER

SQL> The view computes the number of months between the fdate (forecast date) and the current month (the truncs on sysdate and fdate will back the dates to the 1'st of the month). The decode will look at the months between (mbtwn) and either contribute a 'SALES' amount from the table or ZERO if the row doesn't apply to this column. The sum() will 'collapse' the result so you get something that looks like:

DEPT MARCH APRIL MAY JUNE JULY ---------- ---------- ---------- ---------- ---------- ----------

         1     128405      58473      94012      83722      73673
         2      75118      83824      92242      62129      80352
         3      67381      60938      80331     128517      88265
         4      78238      95789      73362      82132      97896
         5      85146      91666      99537     107822     119266
         6      82270      72749      81396      83808     118680
         7      59996     114487      77478     101884      63789
         8      82761     110119      65232     102862      66950
         9     104725      94260      84382     101863     135363
        10     113140      74966      85371     118678      85066

>And the forcast must be the total of all the products
>for each department.
>I realized that this is something like a Excel pivot
>table function, but I need to generate this report
>in SQL urgently.
>Can someone please suggest a solution?
>Also, I'm not familiar with PL./SQL, but is there
>a better solution in using PL/SQL??
>Thanks in advance.
>
>Michael Lim

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 28 1997 - 00:00:00 CST