Approved Workman Database
Community Contributions => SQL Workbench => Topic started by: Knowltons4Awana on August 20, 2008, 12:42:02 PM
-
Rick was showing me how to increase the prices in the inventory to cover the cost of shipping. Awana Headquarters estimated the shipping to be an added 15-25% so Rick worked up this SQL for me
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
You can use an SQL statement (in the SQL Workbench) to automatically set the Sell Price of all Inventory Items = Cost + 15% for example.
Update Inventory set Sell_Price = ROUND((Cost * 1.15),2)
The "2" at the end of the Round function will round to 2 decimal places, thus a $3.00 item and make it sell for $3.45. Placing a "1" at the end will round to nearest dime so $3.00 becomes $3.40
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Always wanting more, I wanted to see if there was a way to round it up to the nearest 50 cents. Rick and I were talking and didn't come up with a solution but thought there might be some of you technical geniuses that would know without breaking a sweat....Anyone?
-
I did a google search and found an answer (the web is amazing!)
In case anyone else wants to do the same, I thought I'd post my results:
Update Inventory set Sell_Price = ROUND((Cost * 1.15)/ .5,0)* .5
It worked like a charm for me in version 3.1.2.4
-
You are SO SMART! Thank you for sharing this information with us.
This is a great example of how we can use this forum to share ideas and info so we all benefit! :)
More on this topic:
The following SQL will round the Sell_Price up to the next even $0.50 or $1.00 increment. For example, an item with a Sell_Price of $0.34 becomes $0.50 and an item with a Sell_Price of $21.99 becomes $22.00
Again, please run the Backup Utility BEFORE you run any SQL statements that modify data.
Update Inventory set Sell_Price = ROUND((Sell_Price)/ .5,0)* .5
(This works with all versions through at least 5.0.0.3)