Home > IT Answers > Business Intelligence > Crystal Reports – Rounding Nearest 1/8 Mile
Sarahtaj
5 pts.
 Crystal Reports – Rounding Nearest 1/8 Mile
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
ASKED: August 11, 2010  1:59 AM
UPDATED: August 12, 2010  2:13 PM
RELATED QUESTIONS
Answer Wiki:
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
Last Wiki Answer Submitted:  August 12, 2010  1:53 pm  by  Martinjamesward   415 pts.
All Answer Wiki Contributors:  Martinjamesward   415 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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
slateken
 230 pts.