Rounding a Calculated Field in Select Query

Transact-SQL
1

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

2

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

3

Thanks for your reply ScottPletcher!

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

4

@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;

5

With no sample data, I wasn't able to test it before I posted.

6

Maybe this:

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