Applying multiple percentages to a column

Question

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.

See here for a way to define a multiplication aggregate: http://stackoverflow.com/a/3912258/33791 — Scott Wegner, Nov 14 '13 at 21:20

mb14 · Accepted Answer · 2013-11-14T21:32:23.273

2

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

Should do the trick

edited Nov 14 '13 at 21:32

answered Nov 14 '13 at 21:20

mb14

22,276
7
60
102

That's similar to what I was trying. This won't parse, BasePrice isn't aggregated or grouped by: (T-SQL version) select P.ProdId, EXP(SUM(LOG(ModPercent/100+1)))*BasePrice from #Product P join #Modifiers M ON P.ProdID = M.ProdID group by P.ProdID – Jeffrey Bane Nov 14 '13 at 21:26
It worsk on MySqL, but you can use AVG(BasePrice) – mb14 Nov 14 '13 at 21:31
Ah, I see, you also have to up the precision on SQL Server. The following works: select P.ProdId, EXP(SUM(LOG(ModPercent/100.0000+1.0000)))*AVG(BasePrice) from #Product P join #Modifiers M ON P.ProdID = M.ProdID group by P.ProdID -- Thanks. I swear I tried this, but I must have had a parenthesis out of whack or something. Marked as answer. – Jeffrey Bane Nov 14 '13 at 21:35

score 2 · Answer 2 · answered Nov 14 '13 at 21:27

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)

Applying multiple percentages to a column

2 Answers2