I have a transaction table A (brand, prod_id, prod_age, in, out). My user needs a delivery pick-up list report. The pick-up requirement is usually based on brand. For example, in my warehouse, I have 4000 items from various product under brand A and 5000 items from various products under brand B. My user wants a list with 300 from brand A and 200 from brand B., '300' can be 20 from prod_1, 50 from prod_2, etc. The oldest product should be pick-up first for each brand. The requirement is stored in another table B (brand, req_qty)

What is the simplest way to do it?

In case you don't understand what I write, I hope the expected result can help..

Brand_A
--------------
Prod_1 20
Prod_5 50
Prod_6 80
....
--------------------
Subtot 300
--------------------


Brand_B
--------------
Prod_3 46
Prod_9 23
....
------------------
Subtot 200
------------------