All Forums Database
johnsunnydew 43 posts Joined 09/14
28 Jan 2015
Decimal issue

Hi there, 
I have 2 querys and the result is a little different with the no of digits after decimal. For this field(data_month_1) it is defined as decimal(22,7) in the data type for both the tables.
Basicall we do a load of 90% of data_volume_month_01 from table 1 to table 2 and hence the values should match. Can anybody help whyc it shows different value after the decimal digits?

select (sum(data_volume_month_01)*0.9)

from 

table 1

where sales_force='MUP' and state='DA' and data_month='201405'

 

Result: 1,231.78217697

 

select sum(data_volume_month_01)

from 

table 2

where sales_force='MUP'

 

Result:  1,231.7821777

johnsunnydew 43 posts Joined 09/14
28 Jan 2015

To put it a little more simpler, below is the query.

 

select sum(data_volume_month_01)  from 

table1

where sales_force='MUP' and state='CA' and data_month='201411'  and outlet_code in('91745553')

 

   RESULT :   -3.0303030

 

so 90% of the above result should be   -2.7272727 available in table 2 which am getting .

 

select sum(data_volume_month_01)

from 

TABLE2

where sales_force='MUP' and state='CA' and data_month='201411'  and outlet_code in('91745553')

  RESULT :-2.7272727
Now to show the comparison between both the tables what should be multiplied in query 1 so that i get the same result as that of in Table 2?
 

dnoeth 4628 posts Joined 11/04
31 Jan 2015

When you divide by 0.9 you increase the number of fractional digits to 8.
Simply cast back to 7 digits using

cast(sum(data_volume_month_01)*0.9 as dec(22,7))

 

Dieter

You must sign in to leave a comment.