I have Two Tables
Tabel 1 is
Purchase
code
billno
billdate
quantity
Tabel 2 is
Stock
code
quantity
purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100
stock
code quantity
prod1 130
prod2 200
pl note
I would like to list all the last purchases corresponding to the stock lying with us i.e. by FIFO method.
then the result should be
code billno date purqty balance
prod1 1001 01/01/2007 120 30
prod1 1002 10/2/2007 100 100
prod2 1003 11/3/2007 200 100
prod2 1004 11/3/2007 100 100
Thanks and Regds
Narendra
Sorry, I still don't get it, maybe I'm impossible. I make one last attempt to get a grasp on it; if I still cannot I'll admit to be too dumb and let others have a go. So let's go step by step:
At 01/01/2007 (before the first transaction) stocks are: prod1=130, prod2=200. I assume that stocks do not grow during transactions (purchases).
Transaction: prod1 1001 01/01/2007 120
It should get listed because 120<130, with an after-transaction balance of prod1=10.
According to your results it gets listed with a balance of 30. Where does this balance come from?
Transaction: prod1 1002 10/2/2007 100
Does it get listed? Note, that 100>10 (10=balance), so it cannot be fulfilled.
According to your results it gets listed with a balance of 100. Where does this balance come from?
Transaction: prod2 1003 11/3/2007 200
It should get listed because 200=200, with an after-transaction balance of prod2=0.
According to your results it gets listed with a balance of 100. Where does this balance come from?
Transaction: prod2 1004 12/4/2007 100
Does it get listed? Note, that 100>0 (0=balance), so it cannot be fulfilled.
According to your results it gets listed with a balance of 100. Where does this balance come from? Also, I believe that the date listed is copied and not modified (should be 12/04/2007).