if then else - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if then else

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-06 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-06 : 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

52326 Posts

Posted - 2012-08-06 : 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

153 Posts

Posted - 2012-08-06 : 10:25:03

If the % is zero then we skip that discount and go to the next...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 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

153 Posts

Posted - 2012-08-06 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-06 : 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

divan
Posting Yak Master

153 Posts

Posted - 2012-08-06 : 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

52326 Posts

Posted - 2012-08-06 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-06 : 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

153 Posts

Posted - 2012-08-06 : 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

52326 Posts

Posted - 2012-08-06 : 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

153 Posts

Posted - 2012-08-07 : 10:17:18

Thank you all for all the help... It worked...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 10:19:33

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources