CODEWITHSUNDEEP
sqlsql-servert-sql
Jeffrey Bane
Jeffrey Bane

Reputation: 592

Applying multiple percentages to a column

I know I can use a cursor for this, but I'm trying to write this with ideally a set based solution or perhaps a CTE. I have 2 tables (simplified for post), products - each having a base price, then a table of modifiers which are percentage increases to apply in succession to that price. So if a product has 2 percentages, i.e., 4% and 5%, I can't just increase the base price by 9%, the requirement is to increase the base price by 4% then the result of that is increased by 5%. This can happen 1 to many times. Here is what I have so far:

CREATE TABLE #Product
(ProdID INT,
BasePrice MONEY)

INSERT INTO #Product
VALUES
(1, 10), (2, 20)

CREATE TABLE #Modifiers
(ProdID INT,
ModPercent INT)

INSERT INTO #Modifiers
VALUES
(1, 2), (1,5), (2, 2), (2, 3), (2,5)

The desired output for these 2 products is:

Prod 1 ((10 * 1.02) * 1.05) = 10.71 Prod 2 (((20 * 1.02) * 1.03) * 1.05) = 22.0626

I tried messing around with EXP(SUM(LOG())) in a straight query, but it seems I'm always summing the percentages. I also tried a CTE, but I can't seem to get it from infinitely recursing:

WITH ProductOutput (ProdID, SumPrice) AS 
(
    SELECT ProdID, BasePrice
    FROM #Product 

    UNION ALL
    SELECT P.ProdID, CAST(O.SumPrice * (1 + (M.ModPercent / 100.00)) AS MONEY)
    FROM #Product P
    INNER JOIN #Modifiers M ON 
    P.ProdID = M.ProdID
        INNER JOIN ProductOutput AS O
        ON P.ProdID = O.ProdID 

)
SELECT ProdID, SUM(SumPrice)
FROM ProductOutput
GROUP BY ProdID

I appreciate any insights that could be offered. I would imagine this has been done before, but my searches didn't yield any hits.

Upvotes: 0

Views: 108

Answers (2)

mb14
mb14

Reputation: 22596

select ProdId, EXP(SUM(LOG(ModPercent/100+1)))*AVG(BasePrice)
from Product
join Modifiers using(ProdId) 
group by ProdId

Should do the trick

Upvotes: 2

Gary Walker
Gary Walker

Reputation: 9134

SQL 2005 added Outer Apply -- makes lots of complex SQL clearer to me -- clearly not necessary as the Group By is providing the key insight here -- but worth learning when you add conditions to the "join logic" it becomes invaluable

select P.ProdID
  , ML.logmarkup
  , P.BasePrice 
  , P.BasePrice * exp(ML.logmarkup) as NewPrice
from #Product P
outer apply 
( 
  select sum(log(1.0+M.ModPercent/100.0)) as logmarkup
  from #Modifiers M where (M.ProdID = P.ProdID)
  group by M.ProdID
) ML

ProdID      logmarkup              BasePrice             NewPrice
----------- ---------------------- --------------------- ----------------------
1           0.0685927914656118     10.00                 10.71
2           0.0981515937071562     20.00                 22.0626

(2 row(s) affected)

Upvotes: 2

Related Questions