i have a customer Invoice table that was joined with the actual sales table with the Invoice Id..up till alright..
now i want to show the Item cost of a particular product which was coming from the production table which i have linked with both the above tables with a key [Item Code + date]..Here date from Sales table is Invoice date and from Production table is Production date...
Now the Output which i want is that....for a particular Invoice Date , Item Cost will come from production table if;
1).invoice date = Production date OR
2).Invoice Date will take the Item Cost from the last Production date i.e. If
Invoice date Production date Item Cost
10/11/2010 1/11/2010 100
10/11/2010 4/11/2010 105
10/11/2010 6/11/2010 107
10/11/2010 9/11/2010 110
Then For Invoice date 10/11/2010 ,Item code should be 110/-.
Create a table with a production cost for each product for every single day. (TIP : peek() and previous() ) Now you will find a Cost for every prodoct on every invoice date
