I have a column in my table in string format that contains different types of discounts:
- integers
- decimal numbers
- compound discounts, i.e. whole numbers interspersed with the + symbol (e.g. 10+3, 5+3+2 etc.). In this case the discount should be interpreted as, in the example 10+3, discount of 10% and then further discount of 3%, which equals a discount of 12.7% (1- 0.9*0.97)
In the third case, how can I extract, one at a time, the numbers interspersed with the + so that I get the total discount?
The problem is that the number of + is variable, there could be one, or two, or none.
Thanks for your help!
>Solution :
You are much better off storing the final result rather than (or as well as) the steps to calculate it but if you only have to worry about + and all the values are >= 0 and < 100 then you can use string_split and the trick to simulate a PRODUCT aggregate function.
DECLARE @T table(YourDiscountColumn VARCHAR(100));
INSERT @T VALUES ('10+3'), ('5+3+2')
SELECT *
FROM @T
CROSS APPLY
(SELECT 1 - EXP(SUM(log(1 - value / 100.0))) AS v
FROM string_split(YourDiscountColumn, '+')) X(compound_discount)