Crystal Reports – Rounding Nearest 1/8 Mile

5 pts.
Tags:
Crystal Reports
Crystal Reports 8.5
Crystal Reports formulas
SQL Server
SQL Server 2005
I'm trying to create a report that will estimate our cost to a taxi vendor. I have the exact mileage, however I need to round to the nearest 1/8 of a mile. Does anyone have any idea how to do that either is Crystal Reports formula or SQL?

Software/Hardware used:
Crystal Report 8.5 for Windows on SQL Server 2005

Thanks. We'll let you know when a new response is added.

You could create a formula a bit like this…

‘Find the position of the decimal in your mileage field
Local NumberVar DecimalPlace := InStr(Mileage,”.”);

‘Get the decimal remeinder of mileage
local NumberVar DecimalValue := Left(DISTANCEFIELD,DecimalPlace);

‘Work out if in the 1st, 2nd 3rd etc. 8th of a mile, using 0.125 as 1/8th
if DecimalValue = 0 then 0
if DecimalValue > 0 and DecimalValue <= 0.125 then 1
if DecimalValue > 0.125 and DecimalValue <= 0.25 then 2
if DecimallValue > 0.25 and DecimalValue <= 0.375 then 3 etc…

I have not been able to test this, but should point you in the right direction…

———————–

It should be easier than the above solution:

In T-SQL, a formula like this should work:

<pre>ROUND(mileage/0.125,0)*0.125</pre>

A similar formula could be created in Crystal reports if needed.

-CarlosDL

Thanks. We'll let you know when a new response is added.
• Assuming the the MILES field in file MILESFILE is decimal(7,2)... Something like this will give you a whole number representing the eighths... Since you're rounding, obviously you'll need to handle the 8/8ths situation...
```select MILES,  cast(round((dec(substring(digits(MILES),6,2))/100 * 8),0)
as integer) from MILESFILE    ```
Will yield this:
```   85.09              1
85.09              1
67.31              2
162.97              8
129.12              1
162.97              8
129.12              1
67.31              2
94.48              4
67.31              2
162.97              8
```