none
Formula Issue RRS feed

  • Question

  • Hi

    I am very green with SQL Formulas and am trying to get a formula to return a percentage figure.

    The formula I have so far works to a point but there is obviously something missing somewhere as at times the figure returned is completly wrong and sometimes does not calculate at all.

    Example, if the Total & Costs have 0.00 figures, the formula returns 100 which is obviously wrong

    Also when adding a sub figure which is calculated into the total, the percentage figure dows not change

    The formula I have is this:

    ((Total-Costs)/IsNull(Total, 1))*100

    Can anybody guide me (The Clueless One) as to how I can resolve this

    • Moved by Bob Beauchemin Thursday, November 21, 2013 8:05 PM Moved to the appropriate forum
    Thursday, November 21, 2013 2:08 PM

Answers

  • I say you have to isolate the variables.  Figure out if it is the formula which is wrong or the values passed to your formula.

    Have you tried outputting the values beside your equation.

    for instance:

    select total, cost, CASE WHEN Total IS NOT NULL THEN ((Total-Costs)/Total)*100 ELSE 0 END from ...

    You may be surprised and figure out the values passed to your formula are not the ones your expect.

    As for the formula, to make sure your formula works, you may simply inject the values directly and try it:

    declare @total money = 100.0,
    @costs money = 100.0
    
    select CASE WHEN @Total IS NOT NULL THEN ((@Total-@Costs)/@Total)*100.0 ELSE 0.0 END

    Again, it is very hard to figure what you are doing with only part of the statement.

    Friday, November 22, 2013 2:45 PM
  • Hi

    Many thanks for the assistance with the long and on-going irritating issue, thanks to some of the comments which I have been thinking about and I have managed to work out where the issue lied and this was with the way that my code was calculating the total.

    As a workaround……. Probably more of an experiment…. I put the formula for the Total in the table itself (Not too sure if this is the recommended way but it works) and removed it from the form, I also changed the formula for the “Zero” value issue which you posted and this has done the trick.

    So, after countless hours chasing this one down I have finally got to where I need to be...

    A few more bits to sort out but I think the system will be up and running shortly.

    Many thanks Indeed J

    Saturday, November 23, 2013 6:25 PM

All replies

  • Example, if the Total & Costs have 0.00 figures, the formula returns 100 which is obviously wrong
    ... ((Total-Costs)/IsNull(Total, 1))*100

    Hello,

    Can you please provide the data types of the use table fields and some sample values?

    In your example above you would get a "division by zero" error, not 100.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 21, 2013 2:49 PM
  • Man I love those questions.

    You use isnull to replace null total by 1 in your division.

    If total = null then total - cost will yield null as well.  This will give you null / 1 = null.

    I would therefore assume your isnull statement achieve nothing in this case unless you change it at all places.

    Also If cost = 0 then you would get total / total = 1 * 100 => 100 which is the result you sometime see.

    Hope this helps.


    • Edited by Antoine F Thursday, November 21, 2013 4:36 PM Was erroneous.
    Thursday, November 21, 2013 3:33 PM
  • Hi

    On my database the Total & Costs fields are set as "Money" on my page they are set as decimal

    The Percentage field on the database is set as decimal(13, 2) on the page it is set as decimal

    Where there are zero figures this appear on the page as 0.00

    Where there are values these appear on the page as for instance £100.00

    Thursday, November 21, 2013 3:54 PM
  • Additionally, if you don't know it already, null in sql server means unknown and is different then 0.

    if you take an unknown number and add it, substract it, multiply it or divide it with a known number, you will always get an unknown result.  This is why operations on null always return null in sql server.

    If your isnull statement is to avoid divisions by 0 then it will not achieve the desired result because 0 is not null.

    If there is a possibility you end up with 0 as total, I would recommend using the case statement to avoid division by 0.

    for instance:

    select case total when 0 then null else (total - cost) / total * 100.0 end as [WhateverNameYouWant] from ...

    This way your divisions by 0 would return null (unknown) while all the other divisions would be evaluated.  Again, make sure your total and cost are of proper data type.

    Thursday, November 21, 2013 4:00 PM
  • Hi

    As mentioned I am very green with this stuff, where you say.....

    for instance:

    select case total when 0 then null else (total - cost) / total * 100.0 end as [WhateverNameYouWant] from ...

    Can you indicate what [WhateverNameYouWant] actually is and also where you say from.. again can you indicate what this might be

    I did say I was preyy clueless :-(

    Thursday, November 21, 2013 4:17 PM
  • Interesting, I just learned something today.

    apparently, decimal and floats are rounded down when converted while money is just plain rounded.  Also it appears the data is not automatically converted to lower precision data during calculation but rather as you convert it explicitly or assign it to a variable/column.  I will edit my previous post accordingly.

    So in your particular case, since your destination is money, you will not have rounding happening.

    Try this for instance.

    declare @val decimal(10,2) = 0.9,
    	@val2 int = 1,
    	@val3 int
    select convert(int, @val * @val2) as convertedtoint,
    	@val * @val2 as notconverted
    go
    
    declare @val money = 0.9,
    	@val2 int = 1,
    	@val3 int
    select convert(int, @val * @val2) as convertedtoint,
    	@val * @val2 as notconverted
    go

    The most plausible cause of you 100% appearing would be cases where your cost is 0. (x-0) / x => x/x => 1.  Multiplied by 100.

    If not already done, take a look at my previous post about null vs 0 for your division by 0.

    • Edited by Antoine F Thursday, November 21, 2013 4:39 PM
    Thursday, November 21, 2013 4:34 PM
  • You can give alias to equations.

    For instance

    select 1 / 1 would return a resultset with unnamed an column containing the value 1.

    select 1 / 1 as AValue would returnset a column named AValue containing the value 1.

    It is a bit hard to comment for me since I do not have the full equation/statement.  However, the [WhateverNameYouWant] was ment to be replaced by the name you want to return.  If you assign it to a variable then there is no need for it.  if you return it as part of a select statement then you may want to name your column to be able to recuperate them later.

    Same thing with the from.  In your statement, you use column names.  Therefore, you must have taken this equation from an update a select or some other statement.  Otherwhise, we would see variable names (@cost, @total).

    If I use your example, it would be:

    ...case total when 0 then null else (total - cost) / total * 100.0 end 

    Thursday, November 21, 2013 4:48 PM
  • Well, I have tried as suggested I.E case total when 0 then null else (total - cost) / total * 100.0 end

    This didn't work, although the revised partially working and very similar formula as per the below "sort of" works

    CASE WHEN Total IS NOT NULL THEN ((Total-Costs)/Total)*100 ELSE 0 END

    The issue I am now having is regardless what what figure is calulated in the Total Field, all of the associated fields work correctly.

    However, if I add a Costs figure the returning figure goes haywire........

    For instance, if I have a Total of £100.00 and a Costs of £100.00 the % margin figure returned is 55.55 which is obviously wrong as it should read 0

    Oddly, if I have a Total of £100.00 and costs of £200.00 the % margin figure returned is 11.11 which should of course be a minus figure.

    So, quite what the issue is I'm afraid I have drawn a blank on, there is obviously something not quite right with the revised formula and I now have a sore head :-(

    Any suggestions on what the formula might need to be would be greatly appreciated

    Friday, November 22, 2013 2:05 PM
  • I say you have to isolate the variables.  Figure out if it is the formula which is wrong or the values passed to your formula.

    Have you tried outputting the values beside your equation.

    for instance:

    select total, cost, CASE WHEN Total IS NOT NULL THEN ((Total-Costs)/Total)*100 ELSE 0 END from ...

    You may be surprised and figure out the values passed to your formula are not the ones your expect.

    As for the formula, to make sure your formula works, you may simply inject the values directly and try it:

    declare @total money = 100.0,
    @costs money = 100.0
    
    select CASE WHEN @Total IS NOT NULL THEN ((@Total-@Costs)/@Total)*100.0 ELSE 0.0 END

    Again, it is very hard to figure what you are doing with only part of the statement.

    Friday, November 22, 2013 2:45 PM
  • Hi

    Many thanks for the assistance with the long and on-going irritating issue, thanks to some of the comments which I have been thinking about and I have managed to work out where the issue lied and this was with the way that my code was calculating the total.

    As a workaround……. Probably more of an experiment…. I put the formula for the Total in the table itself (Not too sure if this is the recommended way but it works) and removed it from the form, I also changed the formula for the “Zero” value issue which you posted and this has done the trick.

    So, after countless hours chasing this one down I have finally got to where I need to be...

    A few more bits to sort out but I think the system will be up and running shortly.

    Many thanks Indeed J

    Saturday, November 23, 2013 6:25 PM