## 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

## Discuss This Question: 1 Reply

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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
• 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
```