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

yes, for example of ID =1, the calculation is : 2 * (1-0.2) * (1*0.3) = 1.12 — erezlale, Jul 25 '17 at 08:51
You will need running multiplication approach which is quite complex approach. See my answer here for the ideas https://stackoverflow.com/a/29487107/1651298 — Giorgi Nakeuri, Jul 25 '17 at 08:55
1*0.3 - will give me the amount of discount, multiplying with (1-0.3) - will give me the price after the deduction of the discount amount — erezlale, Jul 25 '17 at 08:56
Giorgi, its not exactly the same as in your example you run with one baseline per raw. in my example - each id has its own baseline you need to multiply many times. — erezlale, Jul 25 '17 at 08:59
Take a look at this as well: https://stackoverflow.com/a/3912258/6305294 — Alex, Jul 25 '17 at 09:02
The link posted by @GiorgiNakeuri is what you need, just remove the OVER part like this: `price * EXP(SUM(LOG( discount )))` — dnoeth, Jul 25 '17 at 09:02

score 11 · Accepted Answer · 2017-07-25T09:17:54.193

11

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.

edited Jul 25 '17 at 09:17

answered Jul 25 '17 at 09:10

Brilliant ! the easiest solution so far, and also the shortest ! thank you very much @PhillipXT – erezlale Jul 25 '17 at 09:20
The running multiplication using EXP(SUM(LOG())) works fine for small precision of decimal expansion. When the required precision of decimal expansion is high , the above method fails. – TharunRaja Jul 25 '17 at 09:29
can you give an example it will fail ? – erezlale Jul 25 '17 at 19:15
Not sure about precision, but here's another post that has some more information about the limits of this method: https://stackoverflow.com/questions/5416169/mutiplication-aggregate-operator-in-sql – Jul 26 '17 at 05:47

Giorgi Nakeuri · Answer 2 · 2017-07-25T09:14:04.457

4

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

edited Jul 25 '17 at 09:14

answered Jul 25 '17 at 09:08

Giorgi Nakeuri

35,155
8
47
75

Nice approach, much less SQL than my answer! – Tanner Jul 25 '17 at 09:09
1

Assuming there could be IDs without discount, OUTER APPLY might be better suited with `COALESCE(ca.discount,1)` in PriceAfterDiscount calculation – Nenad Zivkovic Jul 25 '17 at 09:13
1

@NenadZivkovic, I agree and I am leaving this for erezlale. – Giorgi Nakeuri Jul 25 '17 at 09:15

Tanner · Answer 3 · 2017-07-25T09:18:11.357

I believe this produces the desired results using a CTE to iterate through the discounts. The solution below is re-runnable in isolation.

Edited: to include data that might not have any discounts applied in the output with a left join in the first part of the CTE.

CREATE TABLE #price
(
    id INT,
    price DECIMAL(5, 2)
);

CREATE TABLE #discount
(
    id INT,
    discount DECIMAL(5, 2)
);

INSERT INTO #price
(
    id,
    price
)
VALUES
(1, 2.00),
(2, 2.00),
(3, 2.00),
(4, 3.50); -- no discount on this item

INSERT INTO #discount
(
    id,
    discount
)
VALUES
(1, 0.20),
(1, 0.30),
(2, 0.40),
(3, 0.50),
(3, 0.60);

-- new temporary table to add a row number to discounts so we can iterate through them
SELECT d.id,
       d.discount,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY d.discount) rn
INTO #GroupedDiscount
FROM #discount AS d;

-- note left join in first part of cte to get prices that aren't discounted included
WITH cte
AS (SELECT p.id,
           p.price,
           CASE
               WHEN gd.discount IS NULL THEN
                   p.price
               ELSE
                   CAST(p.price * (1.0 - gd.discount) AS DECIMAL(5, 2))
           END AS discountedPrice,
           gd.rn
    FROM #price AS p
        LEFT JOIN #GroupedDiscount AS gd
            ON gd.id = p.id
               AND gd.rn = 1
    UNION ALL
    SELECT cte.id,
           cte.price,
           CAST(cte.discountedPrice * (1.0 - gd.discount) AS DECIMAL(5, 2)) AS discountedPrice,
           cte.rn + 1 AS rn
    FROM cte
        INNER JOIN #GroupedDiscount AS gd
            ON gd.id = cte.id
               AND gd.rn = cte.rn + 1
   )
SELECT cte.id,
       cte.price,
       MIN(cte.discountedPrice) AS discountedPrice
FROM cte
GROUP BY id,
         cte.price;

DROP TABLE #price;
DROP TABLE #discount;
DROP TABLE #GroupedDiscount;

Results:

id  price   discountedPrice
1   2.00    1.12
2   2.00    1.20
3   2.00    0.40
4   3.50    3.50  -- no discount

score 1 · Answer 4 · answered Jul 25 '17 at 09:18

As others have said, EXP(SUM(LOG())) is the way to do the calculation. Here is basically same approach from yet another angle:

WITH CTE_Discount AS 
(
    SELECT Id, EXP(SUM(LOG(1-Discount))) as TotalDiscount
    FROM TableTwo 
    GROUP BY id
)
SELECT t1.id, CAST(Price * COALESCE(TotalDiscount,1) AS Decimal(18,2)) as FinalPRice
FROM TableOne t1
LEFT JOIN CTE_Discount d ON t1.id = d.id

SQLFIddle Demo

Product Final Price after Many Discount given

4 Answers4