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

If you want SQL to only look for those not on 0 or 5 already swap out WHERE Price IS NOT NULL; with

WHERE price like ‘%.01’
or price like ‘%.02’
or price like ‘%.03’
or price like ‘%.04’
or price like ‘%.06’
or price like ‘%.07’
or price like ‘%.08’
or price like ‘%.09’
or price like ‘%.12’
or price like ‘%.13’
or price like ‘%.14’
or price like ‘%.16’
or price like ‘%.17’
or price like ‘%.18’
or price like ‘%.19’
or price like ‘%.21’
or price like ‘%.22’
or price like ‘%.23’
or price like ‘%.24’
or price like ‘%.26’
or price like ‘%.27’
or price like ‘%.28’
or price like ‘%.29’
or price like ‘%.31’
or price like ‘%.32’
or price like ‘%.33’
or price like ‘%.34’
or price like ‘%.36’
or price like ‘%.37’
or price like ‘%.38’
or price like ‘%.39’
or price like ‘%.41’
or price like ‘%.42’
or price like ‘%.43’
or price like ‘%.44’
or price like ‘%.46’
or price like ‘%.47’
or price like ‘%.48’
or price like ‘%.49’
or price like ‘%.51’
or price like ‘%.52’
or price like ‘%.53’
or price like ‘%.54’
or price like ‘%.56’
or price like ‘%.57’
or price like ‘%.58’
or price like ‘%.59’
or price like ‘%.61’
or price like ‘%.62’
or price like ‘%.63’
or price like ‘%.64’
or price like ‘%.66’
or price like ‘%.67’
or price like ‘%.68’
or price like ‘%.69’
or price like ‘%.71’
or price like ‘%.72’
or price like ‘%.73’
or price like ‘%.74’
or price like ‘%.76’
or price like ‘%.77’
or price like ‘%.78’
or price like ‘%.79’
or price like ‘%.81’
or price like ‘%.82’
or price like ‘%.83’
or price like ‘%.84’
or price like ‘%.86’
or price like ‘%.87’
or price like ‘%.88’
or price like ‘%.89’
or price like ‘%.91’
or price like ‘%.92’
or price like ‘%.93’
or price like ‘%.94’
or price like ‘%.96’
or price like ‘%.97’
or price like ‘%.98’
or price like ‘%.99’;
2 Likes
3

Or using a regular expression in Sierra…
WHERE price::TEXT ~ ‘^[0-9]+\.[0-9][1-46-9]0+$’

The ::TEXT is PostgreSQL shorthand for “cast to TEXT”, as priceis of type NUMERIC. Tweak accordingly for Polaris money format.

2 Likes
4

I think, modulo would also be an option here:

WHERE (PRICE * 100) % 5 != 0

This will return anything that won’t divide by 5 evenly (with no remainder).

1 Like
5

Modulo would also be the most efficient approach in terms of computational effort.

2 Likes
6

My daughter just started at community college this week and, for some reason, she’s interested in databases. (No, literally, I’ve done nothing to foster this. She just kinda decided, hey, ya know… databases.)

Anyway, I’m going to show her this post and the comments so she can see that there are many roads to the same destination. I posted a totally different solution on the Discord, mostly because I’m not that great with regex. :smiley:

7

Yep, rarely is there a right answer, so long as the results are what you expected.

However, given that this is probably one of the two times in my life I’ve thought “oh hey modulo could solve that”, I’m a bit biased towards that approach :slight_smile: It does make me wonder though what type of “old school” problems they were regularly trying to solve that caused modulo to get added to the SQL standard.

Interestingly (to me anyhow), the person who came up with using the term modulo for the mathematical process of dealing with remainders is the same person who the Gaussian Blur is named after.

1 Like
8

Gauss rules!  Back in the day, I never saw a CRT monitor with a De-Einstein button on it.

1 Like