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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • slateken
    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
    
    230 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following