calculation within SQL 1

[neemi]

SQL SERVER 2005

SQL used within ADO but tested in query ananylser.

I have a calculation with a SQL Statement and the number I am being presented with is incorrect in the sense that I should get the result 8.2 but am being returned with 8.

I need to round the values up so that the actual 8.2 I should get I want as 9.

The calculation is for example

(X-Y+Z)/7

the values I have as an example are:

x=88
Y=60
z=30

therefore
(88-60+30)/7 = 8.29

but the result i am being returned with is

(88-60+30)/7 = 8

WHY IS IT BEING ROUNDED DOWN?
HOW CAN I GET THE ACTUAL VALUE?
SO I CAN ROUND UP?

Please help>
Cheers,
Neemi

 

[bborissov]

Because all values you used are integers the returning result is also integer.
Try:

SELECT 1.0*(X-Y+Z)/7

or

SELECT (X-Y+Z)/7.0

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[neemi]

Thanks. That works like a dream!!!

One more question.... If th number has a decimal... how do I round up?

ie. 8.2 = 9

 

[bborissov]

SELECT CEILING(1.0*(X-Y+Z)/7) AS Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

try

convert(decimal(38,2), (88-60+30)/7.)

 

[neemi]

GOT IT! Used Ceiling!!!! whoo hoo!