VAT changes - how to change VAT and then round price up

With the UK VAT rate due to change in the New Year, several clients will need to update their sites to the new VAT rate. As prices are all stored in a MySQL table (excluding VAT), changin开发者_开发技巧g the actual VAT rate is a simple flag in the backend of most sites, but this leaves 'ugly' prices £10.87, rather than £10.99.

I'd like to run a query against the prices to alter the prices so that all of the new prices (when the new VAT rate is added) are 'nice' prices rather than ugly ones.

For instance:

Current Price (ex VAT): £8.50

Current Price (VAT @ 17.5%): £9.99

New Price (VAT @ 20%): £10.20

Desired Price (VAT @ 20%): £10.99

UPDATE products SET price = (CEIL(price*1.20)-0.01)/1.20 WHERE product_id = 123

if you'd always like prices with .99, it should be pretty easy to do:

update articles set price = round((ceil(8.50 * 1.2) - 0.01) / 1.2, 2);

proof:

mysql> select round((ceil(8.50 * 1.2) - 0.01) / 1.2, 2) as price;
+-------+
| price |
+-------+
|  9.16 |
+-------+
1 row in set (0.00 sec)

in favor of competitive prices you could also consider floor(x) instead of ceil(x) though ;)

Though it's dishonest to your customers to try and disguise a price rise as a VAT increase, you could use something like CEIL (which rounds a decimal up to the nearest integer), then - 0.01.