SQL Server Forums Profile | Register | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  General SQL Server Forums  New to SQL Server Programming  if then else New Topic  Reply to Topic  Printer Friendly
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 = Athen A * market discount % = Xthen X * experience % = Ythen 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 MVPhttp://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 MVPhttp://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 followingBASE_PREM + TMB = AIF GROUP DISCOUNT IS NOT 0 THEN (A * (GROUP_DISCOUNT/100)) - A = BIF MARKET_DISCONT IS NOT 0 THEN (B * (MARKET_DISCOUNT/100)) - B = CIF ADD_DISC IS NOT 0 THEN (C * (ADD_DISC/100)) - C = FINAL PREMhope 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 columnsexample 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 divanWhat you are saying is completely correct but I really need to show B as a number c as a number each in separate columnsexample 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.688then 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 MVPhttp://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 VISAKH16YOUR LOGIC IS VERY CLOSE BUT JUST A BIT OFF..THE SECOND % SHOULD BE MULTIPLIED TO THE RESULT OF THE FIRST IN OTHER WORDS base tmb1) 3126(base)+500(TMB) = 317672) 31767 * -4% = -1270.68 >>>>need to display this3) 31767 + (-1270.68) = 30496.324) 30496.32 * -10% = -3049.6 >>>> need to display this5) 30496.32 + (-3049.6) = 27446.726) 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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/
Topic
 New Topic  Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC
 This page was generated in 0.11 seconds.