Identifying Items With Different Prices by Location 1

[JD5241]

We've been running into an issue where we have some items with multiple locations (up to 6), but their retail prices don't all match, probably human error when updating them. They are supposed to be the same across all locations. I need to come up with a way to identify any that don't match so we can correct them.

I've found that the correct price isn't always the same location, it can be any one of them. We've been finding them as one offs when someone enters a sales order and notices the price is incorrect. I'm looking to compare the prices across all locations for each item, and flag items where the price isn't the same across all locations. Meaning, three locations could have the right price, one could be wrong, or any combination of that.

My SQL skills are a bit rusty and I honestly don't know where to start to write a query to accomplish this in a quick manner. I couldn't find anything on the ECi support portal either, which is generally really good for troubleshooting articles.

Below is an example, looking at the iminvloc_sql table. I'm hoping someone else has run into this, or at least knows more than I do and can offer a suggestion. Any help is appreciated. Thanks!

We are on the latest version of Macola 10.

 

[crystalreporting]

If you are just looking to identify all item numbers where the prices are not consistent, you could just compare the lowest and highest prices;

SELECT il.item_no
FROM iminvloc_sql il
GROUP BY
il.item_no
HAVING MIN(il.price) <> MAX(il.price)



Peter Shirley

http://www.erpessentials.com

 

[JD5241]

That worked perfectly. Thank you!