February 2, 2015 at 11:15 pm
Hello there,
I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount.
I thought to have something like
DECLARE @Disc float
SET @Disc = 0
SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100
FROM UserDiscounts
WHERE UserID = 123
but, seems, it does not work.
Can somebody think about single query without any loops?
Thanks,
Anton
February 3, 2015 at 5:13 am
Can you please post sample data to play with and what output you are looking for.
I hope group by will do the trick.
February 3, 2015 at 5:54 am
In most cases there are two discounts: 2.5% and 25%. So, we should get total discount 26.875%.
UserID Discount Seq
123 2.5% 1
123 25% 2
100 * 2.5% -> 97.5 (2.5%)
97.5 * 25% -> 73.125 (24.375%)
2.5% + 24.375% = 26.875% - final result
February 3, 2015 at 6:03 am
This is an old favourite, check this thread http://www.sqlservercentral.com/Forums/Topic1435577-392-1.aspx
Phil Parkin's example is the best solution if you just want a single TotalDiscount and is Basically
SELECT (1 - EXP(SUM(LOG(1-Discount/100.0)))) * 100 AS TotalDiscount
FROM UserDiscounts
WHERE UserID = 123
Read Phil's post on the article for an explanation of how it works.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 3, 2015 at 6:30 am
This is great. Thanks a lot!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply