Negative Numbers from Calculation

I am trying to get this calculation to work, but it keeps coming back with a negative number.

I think its something to do with making the

Column 1 & 2 are numeric figures then i calcating the datediff with another numeric figure. but i keep getting a negative answer? Any ideas?

Sum((isnull([Column1],0)) * ((isnull([Column2],0)) - cast(DATEDIFF(d,(isnull([date1],0)),(isnull([date2],0)))as float)/365.00)) as New Column

Here You can try this ...

Isnull(Sum([Column1]),0) * isnull([Column2],0)
- Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00)) as New Column

You need not to apply Isnull inside the SUM function, by default the null values will be eliminated on aggergation.. But you can apply the ISNULL on the result of the SUM function..

Try to execute the following statement to get the 3 part of your expression which may help you where you missed your expression..

select

A = Isnull(Sum([Column1]),0) ,

B = isnull([Column2],0)),

C= Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00))

as per your earlier expression the result = A * B - C => (A*B) - C is it correct?

or you want to achive A * (B-C) ... not clear buddy... try to find the result of the 3 expression and debug it..

|||

Can't seem to get this to work. Keep getting syntax error. Incorrect syntax near ')'.

|||

Try the following expressions...

Sum(isnull([Column1],0) * (isnull([Column2],0)
- Isnull(DATEDIFF(d,date1,date2),0)/365.00))

A= isnull([Column1],0) ,
B=isnull([Column2],0),
C=Isnull(DATEDIFF(d,date1,date2),0)/365.00

A= isnull([Column1],0) ,
isnull([Column2],0) - Isnull(DATEDIFF(d,date1,date2),0)/365.00

|||Spot on mate. Thanks. Worked a treat