March 26, 2013 at 9:30 am
I have a scenario whereby I need to aggregate n number of discounts to get a total discount %. Each discount must be applied net of the previous discount.
For example: I buy a hat for £100 and I have two valid vouchers. The first gets me 15% off, 100-(100*(15/100)) = £85. And then we have a second voucher worth 5%, 85-(85*5/100) = £80.75.
So our total discount is 19.25%.
Sequence is important, so a further field records the order in which the discounts are applied.
So in our table we might have:
SaleID | Discount | Sequence
1 15% 1
1 5% 2
2 5% 1
3 5% 1
3 10% 2
3 25% 3
Is anyone able to put together an elegant query that will give the total discount for each sale using the above table? (I can think of some less elegant ways to do it, but there must be a cleaner way...?). As in the example above, I expect 19.25% for SaleID 1.
Many Thanks.
March 26, 2013 at 9:50 am
This should do what you need, and serve as a starting point
CREATE Table #disc
(
SaleId tinyint
,Discount Decimal(5,2)
,Seq tinyint
)
Insert into #disc
Values (1,0.15,1)
Insert into #disc
Values (1,0.05,2)
Insert into #disc
Values (2,0.05,1)
Insert into #disc
Values (3,0.05,1)
Insert into #disc
Values (3,0.10,2)
Insert into #disc
Values (3,0.25,3)
Select * from #disc;
WITH CTE_CalcTotalDisc
AS
(
Select SaleId,Discount,Seq, convert(decimal(6,5),1-(1*Discount)) T_Disc
From #disc
where Seq=1
UNION ALL
Select d.SaleId,d.Discount,d.Seq, convert(decimal(6,5),(1-(1*d.Discount))*T_Disc) T_Disc
From #disc d
JOIN CTE_CalcTotalDisc ad on d.SaleId=ad.SaleId
AND d.Seq=ad.Seq+1
),
CTE_FinalPrice
AS
(
Select SaleId, T_Disc,100-((1-T_Disc)*100) totalPrice
,Row_Number() OVER (PARTITION BY SaleId order by Seq Desc) rn
from CTE_CalcTotalDisc
)
Select * FROM CTE_FinalPrice where rn=1
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 26, 2013 at 9:57 am
Another version:
declare @t table
(
SaleId int
,Discount int
,Sequence int
)
insert @t (SaleId, Discount, Sequence)
select 1,15,1
union all
select 1,5,2
union all
select 2,5,1
union all
select 3,5,1
union all
select 3,10,2
union all
select 3,25,3
select SaleId, (1 - EXP(SUM(LOG(1-Discount/100.0)))) * 100 Discount
from @t t
group by SaleId
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 26, 2013 at 10:16 am
Great, thanks guys.
Very interested to see the recursive CTE solution as that is what I've been trying to get going most of this morning! I wasn't far off actually I just needed to limit the "Anchor member" to Seq 1. Bah!
Solution 2 looks interesting, to be honest I don't see how that works immediately but am going to work through it...
Thanks again.
March 26, 2013 at 10:21 am
Phils is the best one of the two and it should run a lot quicker.
If you want to see the totals after each discount then you may have to look at a triangular join/running total type solution.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 26, 2013 at 10:25 am
jezza101 (3/26/2013)
Great, thanks guys.Very interested to see the recursive CTE solution as that is what I've been trying to get going most of this morning! I wasn't far off actually I just needed to limit the "Anchor member" to Seq 1. Bah!
Solution 2 looks interesting, to be honest I don't see how that works immediately but am going to work through it...
Thanks again.
How sol 2 works is just a bit of maths.
When calculating total discount for a sale, order is not important. To calculate the final price of an item after multiple discounts, just do something like this:
Final Price = (Orig Price)(1 - d1)(1 - d2)(1 - d3) etc etc where d1, d2 are discount percentages as decimals (eg, 0.15 for 15%).
So calculating the total discount for a sale becomes a sort of aggregation - but instead off summing, we are multiplying across rows. But T-SQL has no 'MULT' aggregation function, so we need to sum the logs & then power up the result - for which there are T-SQL functions.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 26, 2013 at 1:53 pm
Here's another solution. Entirely set based and no rounding approximations. Returns the current, previous, and cumulative discount for each row in the source table.
IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
DROP TABLE #TestData
;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,0.15,1),
(1,0.05,2),
(2,0.05,1),
(3,0.05,1),
(3,0.10,2),
(3,0.25,3)
) DATA (SaleID,Discount,Seq))
SELECT
SaleID,Discount,Seq
INTO #TestData
FROM sampledata
SELECT
CurrSaleID AS SaleID
,CurrSeq AS Seq
,CurrDiscount
,PrevDiscount
,(CASE
WHEN PrevDiscount = 0 THEN ((CurrDiscount*100))/100
ELSE 1-(((100-(PrevDiscount*100))-((100-(PrevDiscount*100))*CurrDiscount))/100)
END) AS CumulativeDiscount
FROM
(
SELECT DISTINCT
d.SaleID
,d.CurrSeq
,d.CurrSaleID
,d.NextSaleID
,d.Discount AS CurrDiscount
,(CASE
WHEN d.CurrSeq = 1 THEN 0
WHEN d.CurrSeq > 1
AND d.CurrSaleID = d.NextSaleID-1
THEN (SELECT Discount
FROM #TestData
WHERE
SaleID = d.CurrSaleID
AND Seq = d.CurrSeq-1)
ELSE 0
END) AS PrevDiscount
FROM
(
SELECT
s1.SaleID
,s1.Discount
,s1.Seq AS CurrSeq
,s1.SaleID AS CurrSaleID
,s1.SaleID+1 AS NextSaleID
FROM
#TestData s1
) d
) d1
ORDER BY
CurrSaleID
,CurrSeq
March 26, 2013 at 5:24 pm
Steven Willis (3/26/2013)
... no rounding approximations.
What a misconception.
Try add this to the final SELECT in your query:
, SQL_VARIANT_PROPERTY(CurrDiscount, 'basetype'), SQL_VARIANT_PROPERTY(CurrDiscount, 'scale'), SQL_VARIANT_PROPERTY(CurrDiscount, 'precision')
, SQL_VARIANT_PROPERTY(CurrDiscount*100, 'basetype'), SQL_VARIANT_PROPERTY(CurrDiscount*100, 'scale'), SQL_VARIANT_PROPERTY(CurrDiscount*100, 'precision')
, SQL_VARIANT_PROPERTY((100-(PrevDiscount*100))*CurrDiscount, 'basetype'), SQL_VARIANT_PROPERTY((100-(PrevDiscount*100))*CurrDiscount, 'scale'), SQL_VARIANT_PROPERTY((100-(PrevDiscount*100))*CurrDiscount, 'precision')
, SQL_VARIANT_PROPERTY(1-(((100-(PrevDiscount*100))-((100-(PrevDiscount*100))*CurrDiscount))/100), 'basetype')
, SQL_VARIANT_PROPERTY(1-(((100-(PrevDiscount*100))-((100-(PrevDiscount*100))*CurrDiscount))/100), 'scale'), SQL_VARIANT_PROPERTY(1-(((100-(PrevDiscount*100))-((100-(PrevDiscount*100))*CurrDiscount))/100), 'precision')
Every step of the way the data type of the monetary values is changed.
And considering that SQL Server uses floating point data types for multiplications/divisions - there are more implicit conversions and roundings than you can see.
Sorry, you cannot avoid rounding approximations totally.
But you can minimise them by using FLOAT data type for such things as "Discount" and converting only final result of calculations to an appropriate decimal data type.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply