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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy