 # Rounding a Calculated Field in Select Query

I am trying to calculate and round the output value in a select query (below). I need to first calculate the new price which is the order price * the increase, but I want it to round to the nearest \$5 either up or down and not return any change. So if it returns 4,600.80 as the new price I want it to round down to 4600.00 not up to 4605.00. I hope that makes sense.

"SELECT
ITEM_NO,
QTY_ORDERED,
ORDER_DATE,
PRICING_METH,
Cast(ROUND(ORD_PRICE * @Increase,0) AS numeric(18,0)) AS NEW_PRICE
FROM (
SELECT
*,
max_date = MAX(ORDER_DATE) OVER (PARTITION BY ITEM_NO, QTY_ORDERED)
FROM ORDERS
) AS s
WHERE ORDER_DATE = max_date AND (CSCODE = @SearchValue) AND (ORDER_DATE BETWEEN @StartDate AND @StopDate) AND (NOT (COMPLETION_FLG = 'X')) AND (NOT(DUE_DATE_CD = 'WH'))
ORDER BY ITEM_NO, QTY_ORDERED"

Thank you,
Stacy

Try this:
...
Cast(ROUND((ORD_PRICE * @Increase + 2.5) / 5.0 * 5.0,0) AS numeric(18,0)) AS NEW_PRICE
...

It isn't rounding to the nearest \$5. As an example the original price was 1230.00 * 1.08 = 1328.4 I would want it to round to 1330.00, but using this code it is returning 1331.00.

Thanks,
Stacy

@StacyOW,
Use the modulo operator and subtract the modulus.

``````DECLARE
@Price money = 2567.29
, @Increase decimal(5, 3)= .025
, @Mod money = 5;

SELECT
@Price OldPrice
, @Price + @Price * @Increase NewPrice
, @Price + @Price * @Increase - ( @Price + @Price * @Increase ) % @Mod FinalPrice;`````` With no sample data, I wasn't able to test it before I posted.

Maybe this:

``````Cast(ROUND(ORD_PRICE * @Increase / 50,0) * 50 AS numeric(18,0)) AS NEW_PRICE
``````