Storing operators with operands in table in SQL Server

Question

I work at a company that sells many versions of a product to several different resellers, and each reseller adds parameters that change the resale price of the product.

For example, we sell a vehicle service contract where, for a certain vehicle, the reserve price of the contract is $36. The dealer marks up every reserve by 30% (to $47), adds a premium of $33 to the reserve price (now $80), and adds a set of fee--like commissions and administrative costs--to bring the contract total to $235.

The reserve price is the same for every dealer on this program, but they all use different increases that are either flat or a percentage. There are of course dozens of parameters for each contract.

My question is this: can I store a table of parameters like "x*1.3" or "y+33" that are indexed to a unique ID, and then join or cross apply that table to one full of values like the reserve price mentioned above?

I looked at the SQL Server "table valued parameters," but I don't see from the MSDN examples if they apply to my case.

Thanks so much for your kind replies.

EDIT: As I feared, my example seems to be a little too esoteric (my fault). So consider this: Twinings recommends different temperatures for brewing various kinds of tea. Depending on your elevation, your boiling point might be different. So there must be a way to store a table of values that looks like this--

_{(source: twinings.co.uk)}

A user enters a ZIP code that has a corresponding elevation, and SQL Server calculates and returns the correct brew temperature for you. Is that any better an example?

Again, thanks to those who have already contributed.

Answer 1

I don't know if I like this solution, but it does seem to at least work. The only real way to iteratively construct totals is to use some form of "loop", and the most set-based way of doing that these days is with a recursive CTE:

declare @actions table (ID int identity(1,1) not null, ApplicationOrder int not null, 
                        Multiply decimal(12,4), AddValue decimal(12,4))
insert into @actions (ApplicationOrder,Multiply,AddValue) values
(1,1.3,null),
(2,null,33),
(3,null,155)

declare @todo table (ID int not null, Reserve decimal(12,4))
insert into @todo(ID,Reserve) values (1,36)

;With Applied as (
    select
        t.ID, Reserve  as Computed, 0 as ApplicationOrder
    from
        @todo t
    union all
    select a.ID,
      CONVERT(decimal(12,4),
        ((a.Computed * COALESCE(Multiply,1)) + COALESCE(AddValue,0))),
      act.ApplicationOrder
    from
        Applied a
            inner join
        @actions act
            on
                a.ApplicationOrder  = act.ApplicationOrder - 1
), IdentifyFinal as (
    select
        *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ApplicationOrder desc) as rn
    from Applied
)
select
    *
from
    IdentifyFinal
where
    rn = 1

Here I've got a simple single set of actions to apply to each price (in @actions) and a set of prices to apply them to (in @todo). I then use the recursive CTE to apply each action in turn.

My result:

ID          Computed                                ApplicationOrder rn
----------- --------------------------------------- ---------------- --------------------
1           234.8000                                3                1

Which isn't far off your $235 :-)

I appreciate that you may have different actions to apply to each particular price, and so my @actions may instead, for you, be something that works out which rules to apply in each case. That may be one of more CTEs before mine that do that work, possibly using another ROW_NUMBER() expression to work out the correct ApplicationOrder values. You may also need more columns and join conditions in the CTE to satisfy this.

Note that I've modelled the actions so that each can apply a multiplication and/or an add at each stage. You may want to play around with that sort of idea (or e.g. add a "rounding" flag of some kind as well so that we might well end up with the $235 value).

Applied ends up containing the initial values and each intermediate value as well. The IdentifyFinal CTE gets us just the final results, but you may want to select from Applied instead just to see how it worked.

Answer 2

You can use a very simple structure to store costs:

DECLARE @costs TABLE (
    ID INT,
    Perc DECIMAL(18, 6),
    Flat DECIMAL(18, 6)
);

The Perc column represents percentage of base price. It is possible to store complex calculations in this structure but it gets ugly. For example if we have:

1. Base Price: $100
2. Flat Fee: $20
3. Tax: 11.5%
4. Processing Fee: 3%

Then it will be stored as:

INSERT INTO @costs VALUES
-- op example
(1, 0.0,  NULL),
(1, 0.3,  NULL),
(1, NULL, 33.0),
(1, NULL, 155.0),
-- above example
(2, 0.0,          NULL),
(2, NULL,         20.0),
(2, 0.115,        NULL),
(2, NULL,         20.0 * 0.115),
(2, 0.03,         NULL),
(2, NULL,         20.0 * 0.03),
(2, 0.115 * 0.03, NULL),
(2, NULL,         20 * 0.115 * 0.03);

And queried as:

DECLARE @tests TABLE (
    ID INT,
    BasePrice DECIMAL(18, 2)
);
INSERT INTO @tests VALUES
(1, 36.0),
(2, 100.0);

SELECT t.ID, SUM(
    BasePrice * COALESCE(Perc, 0) + 
    COALESCE(Flat, 0)
) AS TotalPrice
FROM @tests t
INNER JOIN @costs c ON t.ID = c.ID
GROUP BY t.ID

ID | TotalPrice
---+-------------
1  | 234.80000000
2  | 137.81400000

The other, better, solution is to use a structure such as follows:

DECLARE @costs TABLE (
    ID INT,
    CalcOrder INT,
    PercOfBase DECIMAL(18, 6),
    PercOfPrev DECIMAL(18, 6),
    FlatAmount DECIMAL(18, 6)
);

Where CalcOrder represents the order in which calculation is done (e.g. tax before processing fee). PercOfBase and PercOfPrev specify whether base price or running total is multiplied. This allows you to handle situations where, for example, a commission is added on base price but it must not be included in tax and vice-versa. This approach requires recursive or iterative query.