Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Online Analytical Processing and Oracle
Alternatives To OLAP Data Representation

Listing 5.1 Sophisticated variance analysis with Oracle SQL.

SELECT INTEGER
(((E.NUMBER_OF_SALES - S.NUMBER_OF_SALES) / S.NUMBER_OF_SALES) * 100) ,
E.CUSTOMER_NAME , E.CITY_NAME , E.ZIP , S.NUMBER_OF_SALES , E.NUMBER_OF_SALES

FROM DETAIL S , DETAIL E

SEE CODE DEPOT FOR FULL SCRIPT

ORDER BY E.ZIP ASC , E.CITY_NAME ASC , 1 ;

Note that the variance analysis in Listing 5.1 is done directly in the SQL statement. This case displays California users whose usage has dropped by more than 5 percent (comparing January 1996 to January 1997).
But, what if the user wants to compare one full year with another year? The table is structured for simple comparison of two specific month dates, but the SQL query could be modified slightly to aggregate the data, offering a comparison of two ranges of dates.
The query shown in Listing 5.2 will aggregate all sales for an entire year and compare 1996 with 1997. Here, we meet the request show me all customers in California whose sales have dropped by more than 5 percent between 1996 and 1997.

Listing 5.2 Aggregating sales for an entire year.

SELECT INTEGER
(((E.NUMBER_OF_SALES - S.NUMBER_OF_SALES) / S.NUMBER_OF_SALES) * 100) ,
E.CUSTOMER_NAME , E.CITY_NAME , E.ZIP , S.NUMBER_OF_SALES , E.NUMBER_OF_SALES

FROM DETAIL S , DETAIL E

SEE CODE DEPOT FOR FULL SCRIPT

ORDER BY E.ZIP ASC , E.CITY_NAME ASC , 1 ;

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm