php - MySQL Rolling inventory with expiration date -

i hoping guidance point me in right direction regarding balancing inventory expiration date. looking know how many of each 'flavor' have left in inventory , expiration dates of inventory.

all items (flavor) received, in purchase order table, time stamped , capture expiration date (ie. qty 300 exp_date 7/04/14, qty 200 exp_date 7/14/14) .

all orders (per flavor)are processed , time stamped (qty 2 6/27/14...), sum orders processed in given day/week or whatever.

what best solution (temp table, query, or else) identify , allow me display have qty 98 w/ exp_date of 7/04 , qty 200 w/ exp date of 7/14 above example display real time?

here schema....

-- table structure table `flavor` -- create table `flavor` (   `id` int(4) not null auto_increment,   `pfamily` int(5) not null,   `model` varchar(10) not null,   `upc` varchar(17) default null,   `active_flavor` tinyint(1) not null default '1',   `description` varchar(150) not null,   `price` decimal(7,2) not null,   `priority` tinyint(2) default null,   `weight` decimal(6,3) default null,   primary key  (`id`) ) engine=myisam  default charset=utf8 comment='flavors' auto_increment=892 ;  -- table structure table `orders` --  create table `orders` (   `id` int(6) not null auto_increment,   `customer_id` int(3) not null,   `owner_id` int(3) not null,   `ordnum` int(10) not null,   `orddate` int(6) not null,   `buyer` varchar(50) not null,   `model` varchar(10) not null,   `f_id` int(4) not null default '0',   `ord_qty` int(3) not null,   `quantity` int(3) not null,   `wh_price` decimal(7,2) not null,   `invoice_price` decimal(7,2) default '0.00',   `last_updated` int(10) default '0',   `rma` tinyint(1) default '0',   `rma_num` int(10) default '0',   `rma_qty` int(3) default '0',   primary key  (`id`) ) engine=myisam  default charset=utf8 auto_increment=269339 ;  -- table structure table `p_orders` -- create table `p_orders` (   `id` int(6) not null auto_increment,   `vendor_id` int(3) not null,   `owner_id` int(3) not null,   `p_ordnum` int(10) not null,   `p_orddate` int(6) not null,   `f_id` int(4) not null,   `model` varchar(10) not null,   `quantity` int(3) not null,   `qty_rcvd` int(3) default '0',   `gr` tinyint(1) default '0',   `exp_date` date default '0000-00-00',   `date_rcvd` date default '0000-00-00',   `u_date_rcvd` int(10) default null,   primary key  (`id`)  ) engine=myisam  default charset=utf8 auto_increment=9216 ; 

using simple query 1 model

select flavor.id, flavor.model,orders.orddate,orders.quantity   flavor  left join orders on orders.f_id = flavor.id flavor.id = '1' 

i orders

id  model       orddate quantity 1   1000-0001   140622  1 1   1000-0001   140622  1 1   1000-0001   140622  15 1   1000-0001   140622  1 1   1000-0001   140622  1 1   1000-0001   140622  1 1   1000-0001   140622  2 1   1000-0001   140622  2 1   1000-0001   140623  1 1   1000-0001   140623  1 1   1000-0001   140623  2 1   1000-0001   140624  1 1   1000-0001   140624  1 

now goes inventory, each of these have different expiration date

id  model       p_orddate   quantity 1   1000-0001   140615          90 1   1000-0001   140622          72 

sum of orders =80 sum of p_orders=162

so output should have 10 left expiration date 6/15 shipment , 72 left expiration date of 6/22 shipment. hope explains better