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.
Cast(ROUND(ORD_PRICE * @Increase,0) AS numeric(18,0)) AS NEW_PRICE
max_date = MAX(ORDER_DATE) OVER (PARTITION BY ITEM_NO, QTY_ORDERED)
) 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"