Decimal places

  • Another decimal question re decimal points within a case statement. Trying to get  2 decimal points for each case statement but not getting the results expected. Thanks for any help.

    select

    f.Month

    ,f.Site

    ,ISNULL(f.num,0) as 'NUM'

    ,f.DEN

    ,case when f.HAI ='A' then (isnull(f.NUM,0)/f.DEN) *10000

    when f.HAI ='B' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='C' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='D' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI = 'E' then (f.NUM/f.den) *100

    else 0

    end as 'Rate'

    from Final f

  • try

    ,convert(decimal(38, 2), case when f.HAI ='A' then (isnull(f.NUM,0)/f.DEN) *10000
    when f.HAI ='B' then (isnull(f.NUM,0)/f.den) *1000
    when f.HAI ='C' then (isnull(f.NUM,0)/f.den) *1000
    when f.HAI ='D' then (isnull(f.NUM,0)/f.den) *1000
    when f.HAI = 'E' then (f.NUM/f.den) *100
    else 0
    end
    ) as 'Rate'

    depending on your datatypes of (f.num and f.den) you may need to add a "* 1.00" to allow correct calculations e.g. "/f.den * 1.00)"
  • boehnc wrote:

    Another decimal question re decimal points within a case statement. Trying to get  2 decimal points for each case statement but not getting the results expected. Thanks for any help.

    select

    f.Month

    ,f.Site

    ,ISNULL(f.num,0) as 'NUM'

    ,f.DEN

    ,case when f.HAI ='A' then (isnull(f.NUM,0)/f.DEN) *10000

    when f.HAI ='B' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='C' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='D' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI = 'E' then (f.NUM/f.den) *100

    else 0

    end as 'Rate'

    from Final f

    Could you please post the create table (DDL) script, sample data as an insert statement and the expected results?

    😎

  • cast(

    case f.HAI

    when 'A' then (isnull(f.NUM,0)/f.DEN) *10000.00

    when 'B' then (isnull(f.NUM,0)/f.den) *1000.00

    when 'C' then (isnull(f.NUM,0)/f.den) *1000.00

    when 'D' then (isnull(f.NUM,0)/f.den) *1000.00

    when 'E' then (f.NUM/f.den) *100.00

    else 0.00

    end as decimal(9, 2)) as 'Rate'

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • hi boehnc

    what frederico_fonseca wrote is almost correct.

    but the f.num field is defined as varchar or char, then the value may contain a comma.

    Example: num = 12,123: if it was saved as text.

    If the data was imported from a CSV file, the value can also look like this:

    num = 1.100.293 (one million one hundred thousand and two hundred and ninety-three)

    First you have to import the values into the correct format, i.e. without separating thousands, which may have been set in excel.

    Depending on the country setting, for example Germany, a decimal value with a comma is output 123,1456

    This can be converted with TSQL Replace and converted from the varchar into decimal.

    Hope to have helped you.

    Frank

  • ,case when f.HAI ='A' then (isnull(f.NUM,0)/f.DEN) *10000

    when f.HAI ='B' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='C' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI ='D' then (isnull(f.NUM,0)/f.den) *1000

    when f.HAI = 'E' then (f.NUM/f.den) *100

     

    Story in Hindi Love

    • This reply was modified 6 months, 2 weeks ago by  Shukla182.
    • This reply was modified 6 months, 2 weeks ago by  Shukla182.
  • Just a thought, the OP has not responded but still, there are several assumptions being made by the respondents.  There are lots of details missing yet, i.e. DDL, sample data and the logic such as the meaning of 'A' to 'E'.

    😎

    We need to keep the standards up, suggest that everyone hold their answers until all the facts are there!

  • sorry, been out of office for a few days, but wanted to close the loop, both suggestions of enveloping the case statement with a cast and convert both worked as a solution. I do appreciate the help. Thank you!

  • Quit Intresing i appreciate that.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply