Author Topic: Increasing inventory prices to cover shipping...rounding to nearest 50c -HELP-  (Read 6069 times)

Knowltons4Awana

  • Newbie
  • *
  • Posts: 3
    • NCCoV Awana
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?
« Last Edit: February 10, 2020, 11:01:32 AM by Annette »
Rachel Knowlton

Faith is taking the first step even when you don't see the whole staircase...Martin Luther King, Jr.

Knowltons4Awana

  • Newbie
  • *
  • Posts: 3
    • NCCoV Awana
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
Rachel Knowlton

Faith is taking the first step even when you don't see the whole staircase...Martin Luther King, Jr.

Rick Leffler

  • Guest
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)
« Last Edit: September 23, 2011, 11:05:31 AM by Rick Leffler »