SQL query that share products among storehouses

[TheXdeR2005]

Aloha,

it's my first time here and the reason is because 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. I attach the database for your convinience.

 

[Pat Hartman]

Use the totals button to create a query that sums the data.

Select ProductID, Sum(Quantity) as TotQuantity
From YourTable
Group By ProductID;

 

[TheXdeR2005]

Thank you for your answer. When you say to use the totals button what do you mean? Well i've tried with sum but nothing...Maybe i didn't say clear enough what i want to do.

I will take an example from my database:
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...

 

[Pat Hartman]

Sorry, I misunderstood your question. You will need to write some code to do this. You can't do it with just a single query. I couldn't begin to even suggest code without knowing exactly what the purpos of this is and the rules for allocating stock. Basically You'll have one query that looks for negative amounts and a second query that looks for positive amounts. Order the positive amounts query in descending order by quantity and the negative amounts in ascending order. This will maximize your chances of being able to obtain your allocation from a single source. Your code will then need to loop though the negative recordset and subtract that amount from the first available positive record if the positive value contains enough pieces. If it doesn't, you'll need to take what you can to only reduce it to zero, update the positive record, update the negative record, and match the remaining amount.

 

[TheXdeR2005]

Thank you for your concern, i will try to make the query using what you wrote.

The purpose of the query is to equally share the products between the storehouses. The storehouses which have a surplus of the product, give to the storehouses which have shortage. The storehouses which have surplus are those with positive difference and the others with shortage have negative. There aren't any rules for allocating stock.

Do you think that using just SQL can i make this query or have i to write in VB also?

I want to make this query because I want an automatic way to share the products and not by hand

 

[Pat Hartman]

No, you can't use a single query to do this. You need to write code. But, first you need to document the business rules completely.

 

[TheXdeR2005]

What do you mean saying the bussiness rules? Give me an example...

 

[Pat Hartman]

If one storehouse is negative, how do you choose which storehouse to obtain inventory from?
How do you actually accomplish the inventory transfer?
Are you generating two transactions - debit one storehouse and credit another? or are you making the mistake of trying to maintain running balances without any auditibility?
etc.

 

[TheXdeR2005]

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 transaction is better and safer...

I can't think any other rule...

 

[Pat Hartman]

I don't care what the rules are since I am not writing the code. You need to know the rules, preferably all of them, before you can write the code.

Two transactions are the best solution since you can debit one warehouse inventory and credit another. This provides some level of auditibility. Maintaining running balances is a bad idea because you will never be able to recover from any problem.

 

[RV]

Well, there are some rules, if the storehouse is negative then obtain products from the storehouse with the minimum positive value

I guess that should read maximum, not minimum...
Minimum is definitely not good business practice....

RV

 

[TheXdeR2005]

Yes, I believe you are right... it's maximum

 

[TheXdeR2005]

Aloha after a long time,

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.