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)"
January 5, 2021 at 6:24 pm
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?
😎
January 5, 2021 at 6:24 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 6, 2021 at 12:15 am
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
January 6, 2021 at 10:18 am
,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
January 6, 2021 at 4:21 pm
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!
January 7, 2021 at 3:42 pm
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!
January 8, 2021 at 4:48 pm
Quit Intresing i appreciate that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply