Archives of the TeradataForum

Message Posted: Fri, 20 Sep 2002 @ 13:35:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Think of this as an OLAP problem, and I believe the solution will be much simpler and more generic.

Step 1: Combine the 3 monthly fact tables

A) /* Preferred */
DELETE AnnualTable ALL;
INSERT INTO AnnualTable ( acct, monthnumber, amt ) SELECT acct, 1,
amt_month1 FROM month1
;INSERT INTO AnnualTable ( acct, monthnumber, amt ) SELECT acct, 2,
amt_month2 FROM month2
;INSERT INTO AnnualTable ( acct, monthnumber, amt ) SELECT acct, 3,
amt_month3 FROM month3

or B)
CREATE VIEW AnnualTable AS
SELECT acct, 1, amt_month1 FROM month1
UNION
SELECT acct, 2, amt_month2 FROM month2
UNION
SELECT acct, 3, amt_month3 FROM month3

Step 2:

SELECT acct
        ,monthnumber
        ,SUM( amt ) OVER (
                PARTITION BY acct
                ORDER BY acct, monthnumber
                ROWS UNBOUNDED PRECEDING ) AmtToDate
        ,AmtToDate * 12 / monthnumber AS ProjectedAnnualAmt
FROM    AnnualTable
/* Optional - this piece gives you the row with the highest monthnumber only
*/
QUALIFY Rank(monthnumber) = 1

Of course, there are many variations to the OLAP approach. In particular, I can see where an AnnualTable containing rows for ALL possible accounts and ALL possible months with NULL amounts for non-existing account-months would allow you to also analyze missing data problems, perhaps projecting annual amounts using algorithms for partial or incomplete data.

Donald Maxwell

	<Prev	Next>		<<First	<Prev	Next>	Last>>