Dynamically generate decimal points
I have a requirement where I need to compare decimal points based on a column given in a table. I understand it needs to be done dynamically, but is there any other way to achieve this?
create table #temp(Value1 decimal(18,10), Value2 decimal(18,12), DPtoCompare int)
insert into #temp
select 123.45478888, 123.4578888, 3 union
select 23.45478888, 23.4547988, 4 union
select 456.454789, 456.45786, 5 union
select 88.2356789, 88.2356787, 6
If we try below, we get the error
select * from #temp
where convert(decimal(18,DPtoCompare) , Value1) = convert(decimal(18,DPtoCompare) , Value2)
I tried below, it works, but wanted to know if there is any feature or any other possibility in sql server 2008 R2 that will directly select the column value and select decimal points.
declare @sql varchar(max)
select @sql = isnull(@sql,'')+'
select Value1, Value2 from #temp
where DPtoCompare = '+CONVERT(varchar, t.DPtoCompare)
+' and convert(decimal(18,'+CONVERT(varchar,t.DPtoCompare)+'), value1) != '
+'convert(decimal(18,'+CONVERT(varchar,t.DPtoCompare)+'), value2)'
from (select distinct DPtoCompare from #temp) t
exec(@sql)
+3
source to share
1 answer
The round function allows you to pass a field to specify the number of decimal places.
select *
from #temp
where round(Value1, DPtoCompare, 1) = round(Value2, DPtoCompare, 1)
Set the third parameter Round to 1 if you want to truncate to n decimal values ββof your values ββinstead of rounding them.
+5
source to share