Re-using aggregate level formulas in SQL - any good tactics?

Posted by Cade Roux on Stack Overflow See other posts from Stack Overflow or by Cade Roux
Published on 2010-04-02T17:49:21Z Indexed on 2010/04/02 17:53 UTC
Read the original article Hit count: 409

Filed under:
|
|
|

Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level:

DECLARE @Profitability AS TABLE
    (
     Cust INT NOT NULL
    ,Category VARCHAR(10) NOT NULL
    ,Income DECIMAL(10, 2) NOT NULL
    ,Expense DECIMAL(10, 2) NOT NULL
    ) ;

INSERT  INTO @Profitability
VALUES  ( 1, 'Software', 100, 50 ) ; 
INSERT  INTO @Profitability
VALUES  ( 2, 'Software', 100, 20 ) ; 
INSERT  INTO @Profitability
VALUES  ( 3, 'Software', 100, 60 ) ; 
INSERT  INTO @Profitability
VALUES  ( 4, 'Software', 500, 400 ) ; 
INSERT  INTO @Profitability
VALUES  ( 5, 'Hardware', 1000, 550 ) ; 
INSERT  INTO @Profitability
VALUES  ( 6, 'Hardware', 1000, 250 ) ; 
INSERT  INTO @Profitability
VALUES  ( 7, 'Hardware', 1000, 700 ) ; 
INSERT  INTO @Profitability
VALUES  ( 8, 'Hardware', 5000, 4500 ) ; 

SELECT  Cust
       ,Profit = SUM(Income - Expense)
       ,Margin = SUM(Income - Expense) / SUM(Income)
FROM    @Profitability
GROUP BY Cust

SELECT  Category
       ,Profit = SUM(Income - Expense)
       ,Margin = SUM(Income - Expense) / SUM(Income)
FROM    @Profitability
GROUP BY Category

SELECT  Profit = SUM(Income - Expense)
       ,Margin = SUM(Income - Expense) / SUM(Income)
FROM    @Profitability

Notice how the same formulae have to be used at the different aggregation levels. This results in code duplication.

I have thought of using UDFs (either scalar or table valued with an OUTER APPLY, since many of the final results may share intermediates which have to be calculated at the aggregate level), but in my experience the scalar and multi-statement table-valued UDFs perform very poorly.

Also thought about using more dynamic SQL and applying the formulas by name, basically.

Any other tricks, techniques or tactics to keeping these kinds of formulae which need to be applied at different levels in sync and/or organized?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server