PDA

View Full Version : SQL query that share products among storehouses



TheXdeR2005
06-05-2005, 09:22 AM
Aloha,

I have a problem with a query. I have a database of a storehouse. Each storehouse can be in one city. Storehouses have 5 products A, B, C, D, and E. A storehouse may have more, less or equal quantities of a product than it needs. I want to write an SQL query that shares the quantities of a product among storehouses. I tried to write but it was to difficult than i thought, so i ask your help.

Here is an example:
For product A:
In LONDON the difference is -5
In PARIS the difference is 14
In MADRIT the difference is 64
In ROME the difference is 59
In BERLIN the difference is -3 etc...etc...

Now i want to take 5 from PARIS or MADRIT or ROME, doesn't matter, and give it to LONDON. So when i do this my database will be, if i take 5 from PARIS:

In LONDON the difference is 0
In PARIS the difference is 9
In MADRIT the difference is 64
In ROME the difference is 59
In BERLIN the difference is -3 etc...etc...

The same for BERLIN, if i take 3 from PARIS:

In LONDON the difference is 0
In PARIS the difference is 6
In MADRIT the difference is 64
In ROME the difference is 59
In BERLIN the difference is 0 etc...etc...

I attach the database for your convinience

bosboer
06-05-2005, 03:28 PM
I'm affraid this will be impossible with only one SQL query. You could try a loop that selects the storehouse with highest shortage. Then select the storehouse with the largest surplus. Then transfer as much items from one storehouse to the other as needed (and possible). Repeat the loop until there is no storehouse with a shortage or surplus.

xCav8r
06-05-2005, 04:38 PM
I think your suggestion assumes that he or she wants to evenly divide the inventory among warehouses, but my guess is that won't make sense for the business. I think we need more information about the business rules before we can start offering quality advice. For example, can or should any warehouse transfer inventory to any other warehouse? Should geography, shipping time and costs not be taken into account? Does the minimum inventory threshold mean in fact that all surplus inventory can be shipped away, or should preference be given to warehouses with surpluses exceeding a certain value? How does demand for these goods factor in? Surely it's not the same across all geographical regions for every product. Does that need to be taken into account?

Besides, I'm not sure given the current inventory that equalizing inventories across the warehouses is possible. A sum of the differences between the minimum requirements and actual inventories produces this:

A: shortfall of 330 units
B: surplus of 26 units
C: surplus of 126 units
D: shortfall of 10 units
E: surplus of 3 units
I do agree, however, that no easy answer with SQL alone will help solve this problem. It's certainly possible to do with VBA, but without some more rules, I think equalizing the inventory based solely on quantity might produce some goofy shipping requirements and be valuable solely as an academic exercise.

On a final note, I might suggest that the tables be slightly restructed to allow for better growth--if that's something that will be necessary. For example, create separate tables for warehouses and inventory, and don't include calculated values in your tables. That's what queries are for. If you use a query to calculate a value and then stick it back into the table, the calculated value will only remain valid as long as the underlying data remains constant.

TheXdeR2005
06-07-2005, 05:18 AM
Thank you for your concern.

Well, there are some rules, if the storehouse is negative then obtain products from the storehouse with the minimum positive value and this storehouse belogs to a city of the same country. If there is not a city of the same country then go to another country.

First i thought to run balances without any audibility but i think that the 2 transactions, debit one storehouse and credit another, is better and safer...

xCav8r
06-07-2005, 06:57 AM
How do you keep track of this now? Is it currently done by brain? I can help you with some code to equalize your inventory, but I just want to make sure that I understand what you want. It sounds like there are only two rules: get excess inventory in country if possible, out of country if not.

TheXdeR2005
06-20-2005, 03:49 AM
I made a piece of code in VBA and not for all the storehouses, but for those that belongs in the same country. The code doesn't update the db, it shows only the trunsactions in the Immediate window in VBA. I want a second opinion about the code. I attach it.

xCav8r
06-20-2005, 06:45 PM
I don't mean to keep harping on stuff that might not be important to you, but have you no need for taking into account geographical demand in addition to shipping costs and times?

Either way, your VBA code looks good, though the spaces in your field names makes writing it somewhat more tedious. I might reiterate that if this is a database that will need to grow in the future, you might consider restructuring your tables. At the very least, get rid of the calculated fields.

TheXdeR2005
06-23-2005, 06:09 AM
Aloha,

You are right about the geographical demand in addition to shipping costs and time. When i was told to make this query i thought it like that, but my boss told me that he was going to change the results according to his own needs, so i chose the simplest solution. Even if i take into account distances,shipping costs etc i have to rewrite the database. Though it will be a good implementation.

How i could reconstruct the tables? Give me some examples.

Thank you for your help

xCav8r
06-23-2005, 06:29 AM
Restructuring would only be necessary if this is something that will grow. What you have now obviously works, so I don't mean to make things unnecessarily complicated. Anyway, when I mentioned this, I should not have said tables, but table...specifically the product table. I'd create a separate table for storehouses, and I'd remove the field DIFFERENCE IN STOREHOUSE. That's a calculated value determined by the current inventory vs. the desired minimum quantity. Leave that in the query.