×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Identifying Items With Different Prices by Location

Identifying Items With Different Prices by Location

Identifying Items With Different Prices by Location

(OP)
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.

RE: Identifying Items With Different Prices by Location

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

RE: Identifying Items With Different Prices by Location

(OP)
That worked perfectly. Thank you!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close