Product Final Price after Many Discount given

Question

I have two tables. One table of Ids and their prices, and second table of discounts per Id. In the table of discounts an Id can has many Discounts, and I need to know the final price of an Id.

What is the Best way to query it (in one query) ? The query should be generic for many discounts per id (not only 2 as mentioned below in the example)

For example Table one

id  price   
1   2.00   
2   2.00   
3   2.00   

Table two

id  Discount   
1   0.20   
1   0.30   
2   0.40   
3   0.50   
3   0.60   

Final result:

id  OrigPrice   PriceAfterDiscount  
1   2.00        1.12     
2   2.00        1.20      
3   2.00        0.40      

Answer 1

Here's another way to do it:

SELECT T1.ID, T1.Price, T1.Price * EXP(SUM(LOG(1 - T2.Discount)))
FROM T1 INNER JOIN T2 ON T1.ID = T2.ID
GROUP BY T1.ID, T1.Price

The EXP/LOG trick is just another way to do multiplication.

If you have entries in T1 without discounts in T2, you could change the INNER JOIN to a LEFT JOIN. You would end up with the following:

ID   Price   Discount
4    2.00    NULL

Your logic can either account for the null in the discounted price column and take the original price instead, or just add a 0 discount record for those.

Answer 2

Generally it can be done with a trick with LOG/EXP functions but it is complex. Here is a basic example:

declare @p table(id int, price money)
declare @d table(id int, discount money)

insert into @p values
(1, 2),
(2, 2),
(3, 2)

insert into @d values
(1, 0.2),
(1, 0.3),
(2, 0.4),
(3, 0.5),
(3, 0.6)


select p.id, 
       p.price, 
       p.price * ca.discount as PriceAfterDiscount  
from @p p
cross apply (select EXP(SUM(LOG(1 - discount))) as discount FROM @d where id = p.id) ca

For simpler(cursor based approach) you will need a recursive CTE, but in this case you need some unique ordering column in Discounts table to run it correctly. This is shown in @Tanner`s answer.

And finally you can approach this with a regular cursor