January 26, 2011 at 4:13 pm
I am creating queries based on invoicing amounts from a supplier. My problem is that I cannot get the figures in my SQL queries to round as the supplier invoice. My database is on SQL Server 2000.
The scenario: I have a sum of material, water, as 10,952,650 cubic feet and need to convert to Acre Feet. When I apply the agreed upon formula (10,952,650/43560) I come up with 251.438246 Acre Feet for which I need to report to 4 decimal places. The result in SQL is 251.4382 (Round((Acre Feet), 4,0) – the supplier invoice amount is 251.4383 acre feet. How can I get 251.438246 to round up to 251.4383?
Some of my attempts:
•In my query I changed the data type to float using Cast (SUM(cast((C.TotalCF/43560)as float(53))) as AF)for the calc
•Went as far as changing the data type in the table to float for Cubic Feet.
All provide the same result when I round to 4 decimal places.
January 26, 2011 at 4:47 pm
Hi,
I'm not sure if there is a better way but you could try:
SELECT CEILING(251.438246 * 10000) / 10000
All it does is get the 4 decimal places into the int portion.
You can then use CEILING to return the smallest int larger than the decimal and then get the decimal point back into the right place.
Rob.
January 26, 2011 at 5:25 pm
Using ceiling and multiplying and dividng works. Can't believe it was that easy. Thanks.
January 27, 2011 at 2:02 am
No worries, glad it helped 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply