Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Summing 3 tables
 
From:   Maxwell, Donald

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023