Round Prices to Stop Taking Money at Front Desk

Clearinghouse Repository Polaris Repository
, ,
1

This query will update item prices, rounding them to the nearest five cents. We’ve used this in a library that moved away from money and payment transactions at the desk, and they instead have patrons pay everything at self-checks. Since the self-check money handlers don’t accept pennies, we needed to round all prices to the nearest nickel to make this happen.

-- Create a temp table for ItemRecordIDs, the current price, and the newly calulated price
CREATE TABLE #TempItemPriceUpdate (
    ItemRecordID INT,
    Price MONEY,
    NewPrice MONEY
);

-- Populate the temp table
INSERT INTO #TempItemPriceUpdate

SELECT
    ItemRecordID,
    Price,
    CONVERT(MONEY, ROUND(ROUND(Price * 20, 0) / 20.0, 2)) AS NewPrice
FROM
    Polaris.Polaris.ItemRecordDetails WITH (NOLOCK)
WHERE
    Price IS NOT NULL;

UPDATE
    Polaris.Polaris.ItemRecordDetails
SET
    ItemRecordDetails.Price = #TempItemPriceUpdate.NewPrice
FROM
    #TempItemPriceUpdate
WHERE
    ItemRecordDetails.ItemRecordID = #TempItemPriceUpdate.ItemRecordID
AND
    ItemRecordDetails.Price != #TempItemPriceUpdate.NewPrice;

DROP TABLE #TempItemPriceUpdate;