| Author |
 Topic  |
|
|
divan
Posting Yak Master
126 Posts |
 Posted - 08/06/2012 : 10:08:58
|
I have a table where there are a few discount fields such as experience discount, market discount and others. The thing is that these fields are % and not dollar amounts.I have been asked to calculate the final premium in dollar amounts..The first thing I need to do is add two dollar amount fields as my base premium, then I will have to apply the discounts to the base premium in the following manner.
Base Premium + TMB = A
then A * market discount % = X
then X * experience % = Y
then Y * part time discount % = Z and z will be my final premium..
It is important to keep in mind that some of the discount fields are zero and hence we really do not want to multiply a zero as that will cause the remaing fields to be a zero.. |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/06/2012 : 10:15:54
|
Seems to me that if discount really is percentage off the full price then you would need to multiply by 100-discount percentage. If that is the case, you can calculate it like this:SELECT
BasePremium+TMB AS A,
(BasePremium+TMB)
*(100.0-MarketDiscountPercent) AS X,
(BasePremium+TMB)
*(100.0-MarketDiscountPercent)
*(100.0-ExperienceDiscountPercent) AS Y,
(BasePremium+TMB)
*(100.0-MarketDiscountPercent)
*(100.0-ExperienceDiscountPercent)
*(100.0-PartTimeDiscountPercent) AS FinalPremium
FROM
YourTable; |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/06/2012 : 10:20:01
|
so in the case of zero values what should you your final result? looks like what you need to use is case ..when expression
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
divan
Posting Yak Master
126 Posts |
Posted - 08/06/2012 : 10:25:03
|
| If the % is zero then we skip that discount and go to the next... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/06/2012 : 10:30:43
|
then do like
..
(BasePremium+TMB)
*ISNULL(NULLIF((100.0-MarketDiscountPercent),0),1)
*ISNULL(NULLIF((100.0-ExperienceDiscountPercent),0),1)
*ISNULL(NULLIF((100.0-PartTimeDiscountPercent),0),1) AS FinalPremium
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
divan
Posting Yak Master
126 Posts |
Posted - 08/06/2012 : 11:24:16
|
I apologize I have given you the wrong info Please see the following
BASE_PREM + TMB = A
IF GROUP DISCOUNT IS NOT 0 THEN (A * (GROUP_DISCOUNT/100)) - A = B
IF MARKET_DISCONT IS NOT 0 THEN (B * (MARKET_DISCOUNT/100)) - B = C
IF ADD_DISC IS NOT 0 THEN (C * (ADD_DISC/100)) - C = FINAL PREM
hope this helps.. Again sorry for the wrong info |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/06/2012 : 12:00:02
|
Just from the signs of the intermediate results, I still think the formula you have in your latest post is incorrect.
Simply use the formula that Visakh posted and compare that with what you are actually expecting for a few sample cases (by computing those manually). I think you will find that it gives the correct results. It automatically takes into account the case where any of the discounts are zero.
Edit: One change to the expression as shown in red:(BasePremium+TMB)
*ISNULL(NULLIF((100.0-MarketDiscountPercent)/100.0,0),1)
*ISNULL(NULLIF((100.0-ExperienceDiscountPercent)/100.0,0),1)
*ISNULL(NULLIF((100.0-PartTimeDiscountPercent)/100.0,0),1) AS FinalPremium
|
Edited by - sunitabeck on 08/06/2012 12:02:34 |
|
|
|
divan
Posting Yak Master
126 Posts |
Posted - 08/06/2012 : 13:54:22
|
What you are saying is completely correct but I really need to show B as a number c as a number each in separate columns
example
group discount in the table = -4%
market discount in the table = -10%
base Premium Tmb group_discount market_discount final prem
31267 500 -1270.68 -3049.632 27446.688 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/06/2012 : 14:36:29
|
quote:
Originally posted by divan
What you are saying is completely correct but I really need to show B as a number c as a number each in separate columns
example
group discount in the table = -4%
market discount in the table = -10%
base Premium Tmb group_discount market_discount final prem
31267 500 -1270.68 -3049.632 27446.688
then write separate expressions for each
BasePremium,TMB,
(BasePremium+TMB)
*ISNULL(NULLIF((100.0-MarketDiscountPercent)/100.0,0),1) AS market_discount,
.. other discounts similarly,
(BasePremium+TMB)
*ISNULL(NULLIF((100.0-MarketDiscountPercent)/100.0,0),1)
*ISNULL(NULLIF((100.0-ExperienceDiscountPercent)/100.0,0),1)
*ISNULL(NULLIF((100.0-PartTimeDiscountPercent)/100.0,0),1) AS FinalPremium
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/06/2012 : 14:39:08
|
There are more columns than you probably will need in the query below. Remove whatever you don't need:SELECT
BASE_PREM,
TMB,
GROUP_DISCOUNT,
(BASE_PREM+TMB)
*(GROUP_DISCOUNT/100.0) AS GROUP_DISCOUNT_AMOUNT,
(BASE_PREM+TMB)
*(1.0-GROUP_DISCOUNT/100.0) AS B, -- premium after taking off group discount
MARKET_DISCONT,
(BASE_PREM+TMB)
*(1.0-GROUP_DISCOUNT/100.0)
*(MARKET_DISCONT/100.0) AS MARKET_DISCOUNT_AMOUNT,
(BASE_PREM+TMB)
*(1.0-GROUP_DISCOUNT/100.0)
*(1.0-MARKET_DISCONT/100.0) AS C, -- premium after taking off group discount and market discount
ADD_DISC,
(BASE_PREM+TMB)
*(1.0-GROUP_DISCOUNT/100.0)
*(1.0-MARKET_DISCONT/100.0)
*(ADD_DISC/100.0) AS ADD_DISCOUNT_AMOUNT,
(BASE_PREM+TMB)
*(1.0-GROUP_DISCOUNT/100.0)
*(1.0-MARKET_DISCONT/100.0)
*(1.0-ADD_DISC/100.0) AS FINAL_PREM -- premim after taking off all three discounts
FROM
YourTable;
|
|
|
|
divan
Posting Yak Master
126 Posts |
Posted - 08/06/2012 : 14:54:06
|
VISAKH16
YOUR LOGIC IS VERY CLOSE BUT JUST A BIT OFF..
THE SECOND % SHOULD BE MULTIPLIED TO THE RESULT OF THE FIRST
IN OTHER WORDS
base tmb
1) 3126(base)+500(TMB) = 31767
2) 31767 * -4% = -1270.68 >>>>need to display this
3) 31767 + (-1270.68) = 30496.32
4) 30496.32 * -10% = -3049.6 >>>> need to display this
5) 30496.32 + (-3049.6) = 27446.72
6) 27446.72 * 5% = 1372.336 >>> need to display this
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/06/2012 : 15:12:58
|
you can extend the suggestion sunita gave in same way to include what all fields you require for calculation and return the corresponding figures. so if it needs a derived value make sure you include earlier calculation in its expression
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
divan
Posting Yak Master
126 Posts |
Posted - 08/07/2012 : 10:17:18
|
| Thank you all for all the help... It worked... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/07/2012 : 10:19:33
|
cool
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
if then else
