FIFO in MySQL db -

i'm using mysql (not mssql) database , bumped onto problem seem unable solve. appreciate finding solution. i'd create view using 2 tables, described below:

1. product_in product_code received_time received_amount  2. product_out product_code delivery_time delivered_amount 

the “view” should provide following:

product_code received_time received_amount of_which_delivered 

my problem product_out administered first incoming data (fifo: first in first out), since amount delivered either more or less received amount, not know how calculate “of_which_delivered”.

so far, managed put order incoming data, , sum outgoing (delivered) goods using sum.

select        sn,       product_code,        received_time,        received_amount,       delivered_amount            ( select                received_time,                received_amount,                @rend2 := if( @rend1 = product_code, @rend2 + 1, 1) sn,                @rend1 := product_code product                             product_in,                ( select @rend1 := 0, @rend2 := 0 ) tt              order                product_code,                received_time ) k           left join           ( select                    product_code prdct,                   sum(delivered_amount) delivered_amount                                  product_out                group                   product_code ) b              on aru = product_code 

i have not succeded in creating loop make possible analyze if output amount more, or less received amount on given day, , if more, difference added received amount of day.

to more precise, here example:

product   date    qty nr.1      sep 2   500  nr.1      sep 3   300 nr1.      sep 4   200 on 4th.  

900 pcs delivered out on 5th.

in case should see following in view:

product  action    date    qty nr.1     received  sep 2   500   (delivered 500) nr.1     received  sep 3   300   (delivered 300) nr.1     received  sep 4   200   (only 100 delivered) 

i grateful me find solution!

sorry no query adjustment you, but... having worked accounting systems in past, database structure appears short on better handling of in/out fifo method (or lifo). underlying accounting system had inventory table receipts (abbreviated)

table: itemtrans itemtransid   (auto-increment id transaction) itemid        (item id of inventory item) status        (status, such in, our or inventory adjustment) date          (date of activity) qty           (quantity) qtyused       (running total column inventory used up) 

as items sold or adjustments. sales details table show itemtrans record quantities used for. so, if had inventory on hand have in sample, sales order detail line show 900. inventory used table show specific itemtransid quantities allocated , how many said block, showing intended activity of release of actual product @ time of sale.

this way, don't have recreate inventory @ given point in time. query sales order details , items pulled data , how many each block of receipt went against.

this simplifies process of generating cogs (cost of goods sold) reported general ledger sub-journal such accounts receivable (sales orders) activity.

do have ability introduce such adjustments database structures?