Arithmetic overflow error converting expression to data type float.

[sharonc]

I'm hoping you can help me.

I have the following expression:

[Inflation Adjusted Total] = POWER(dbo.v_ProjectTotalCosts.[Total Job Incl IDC]*
CONVERT(DECIMAL(10,5),[contingency])/100+1*CONVERT(DECIMAL(10,5),[Inflation Rate])/100+1,
Case when (CONVERT(DECIMAL(10,5),[In Service Year]) -
CONVERT(DECIMAL(10,5),[Estimate Base Year]))= 0
then 0 ELSE (CONVERT(DECIMAL(10,5),[In Service Year]) - CONVERT(DECIMAL(10,5),[Estimate Base Year]))END),

Total Job Incl Idc(decimal(12,6) = 546789.102568
Contingency(Real) = 10.0
Inflation Rate(Real) = 3.0
In service yr(int) = 2001
Estimate Base Year(int) = 2001

I have tried to modify this expression every which way but I still get the error message:

Arithmetic overflow error converting expression to data type float.
Warning: Null value is eliminated by an aggregate or other SET operation.

Do you have any idea what I'm doing wrong?

 

[grahamm]

I noticed
- Integer division e.g /100 , and
- Possible incorrect association e.g ../100 + 1*CONVERT...

Instead of:

CONVERT(DECIMAL(10,5),[contingency])/100+1*CONVERT(DECIMAL(10,5),[Inflation Rate])/100+1,

try:

( CONVERT(DECIMAL(10,5),[contingency])/100.0 +1) * (CONVERT(DECIMAL(10,5),[Inflation Rate])/100.0 +1),


Graham

 

[KRISF]

Good comment.

If that does not fix it, the SET command can be used to prevent the stored procedure from bombing.

 

[sharonc]

The problem happens when I add the POWER function to the code.

This is the code I have now:

[Inflation Adjusted Total] = Case when ([In Service Year] - [Estimate Base Year])= 0
then 0 ELSE POWER(Cast(dbo.v_ProjectTotalCosts.[Total Job Incl IDC]*
([contingency]/100+1*[Inflation Rate]/100+1) AS Float(25)),
(Cast([In Service Year] - [Estimate Base Year] AS Float(25)))) END,

It acts like the given power has a larger amount of decimal places than the first(expression) has:

(Cast([In Service Year] - [Estimate Base Year] AS Float(25)))) has a larger amount of decimal places than the first(expression)

So I get the error message: Arithmetic overflow error converting expression to data type float.

 

[grahamm]

Ok I looked at this a little closer. I'm not sure what you're trying calculate, but your equation is very suspect. The problem is with the given equation (with ()'s), and your quoted #'s if the number of years > 53 then the power result exceeds the range of a float i.e. 1.79 + 10^309
e.g. try
select power( cast((546789.102568* (10.0/100.0+1) * (3.0/100.0+1))as float(25)),53)

then hanging 53 to 54. This causes overflow. Looking at the eq it looks like you're trying to perform a compound interest calc. but you have:

Fv = (Pv * c * i)^y

I'd thought you'd want:

Fv = Pv * (c+i)^y

although I don't understand "contingency" (c) Is it a yearly adjustment?

Thus:-
Inflation Adjusted Total] = Case when ([In Service Year] - [Estimate Base Year])= 0
then 0 ELSE
dbo.v_ProjectTotalCosts.[Total Job Incl IDC]* POWER(
(([contingency]+[Inflation Rate])/100.0+1)),
[In Service Year] - [Estimate Base Year]) END,


for the example:
select 546789.102568 * power( ((10.0 + 3.0)/100.0+1) ,53)
= 355612775.741236
i.e. a 13% yearly rate over 53 years with a start of 546,789 gives 355,612,775

This seems sane

 

[sharonc]

It may seem sane, but it isn't coming up with the number I need. The final number should be $601468.01. This is a calculation that was written in MS Access by a user and I'm trying to recreate this query as a SQL View.

Thank you for trying to help.

 

[grahamm]

The $601468.01 is a clue. BTY I was using 53 yrs as an example, your # indicate 0 yrs. It looks like "contingency" is not compounded anually. However, you'll need a case where in_service_yr > base_yr to be sure.

Anyway

Fv = Pv * (c+i)^y :

perhaps should be

Fv = Pv * c * i^y

given your #'s from above:

Total Job Incl Idc(decimal(12,6) = 546789.102568
Contingency(Real) = 10.0
Inflation Rate(Real) = 3.0
In service yr(int) = 2001
Estimate Base Year(int) = 2001

Then:
Fv = 546789.102568 * (10.0/100.0 + 1) * (3.0/100.0 +1)^0

As a select:

select 546789.102568 * (10.0/100.0+1) * power( ((3.0)/100.0+1) ,0)

= 601468.012825 the number you want

Thus your select stmt can simply be (no case is needed) :
[Inflation Adjusted Total] =
dbo.v_ProjectTotalCosts.[Total Job Incl IDC]
* ([Contingency]/100.0+1)
* POWER(
([Inflation Rate]/100.0+1),
[In Service Year] - [Estimate Base Year]),