SQL – Variable formule to calculate prices

I’m in need of the correct code syntax that calculates the price after discount for the following 4 scenarios. Please note that I’m looking for a variable solution as the amount of commas in the discount column can be more tha 3 as well.

I have a table dwh.FactTurnover.

**One of the columns is the SalesPriceExcl (excl discounts)

One of the columns is “discount” which can contain the following:

Sample data:

• Example : 10 (this is a discount of 10%)

• Example 2: 58,12 (this is a discount of 58% followed by an additional discount of 12%)

• Example 3: 50,10,15 (this is a discount of 50% followed by a discount of 10% followed by a discount of 15%)

• Example 4: P –> blocked, no discount possible

This should give the following solutions for the above examples:

• Example 1: 100$ * 0.90 = 90$

• Example 2: 39.5877$ * 0.42 * 0.88 = 14.63$

• Example 3: 100$ * 0.50 * 0.90 * 0.85 = 38.25$

• Example 4: 100$ * P = 100$

Thank you very much for your involvement.

Answer

This works, it uses STRING_SPLIT() to split the discount string into separate rows. It then takes the discount and calculates (100 – Discount) /100. The Log() of these values are taken and aggregated using SUM() and Group By on the basis that a * b is the equivalent EXP(LOG(a) + LOG(b)).

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE FactTurnover
(
  ID INT,
  SalesPriceExcl NUMERIC (9,4),
  Discount VARCHAR(100)
  )

 INSERT INTO FactTurnover
 VALUES 

   (1, 100, '10'),
   (2, 39.5877, '58, 12'),
   (3, 100, '50, 10, 15'),
   (4, 100, 'B')

Query 1:

;WITH CTE AS
(
  SELECT Id, SalesPriceExcl, 
         CASE WHEN value = 'B' THEN 0
         ELSE CAST(value as int) END AS Discount
  From FactTurnover
  CROSS APPLY STRING_SPLIT(Discount, ',')
)
SELECT Id,  
       Min(SalesPriceExcl) AS SalesPriceExcludingDiscount,
       EXP(SUM(LOG((100 - Discount) / 100.0))) As TotalDiscount,
       Cast(EXP(SUM(LOG((100 - Discount) / 100.0))) * 
            MIN(SalesPriceExcl) As Numeric(9,2))
        PriceAfterDiscount
FROM CTE
GROUP BY ID

Results:

| Id | SalesPriceExcludingDiscount |       TotalDiscount | PriceAfterDiscount |
|----|-----------------------------|---------------------|--------------------|
|  1 |                         100 |                 0.9 |                 90 |
|  2 |                     39.5877 | 0.36960000000000004 |              14.63 |
|  3 |                         100 | 0.38250000000000006 |              38.25 |
|  4 |                         100 |                   1 |                100 |