I have a table holding product,year and period as well as stockposition,purchase qty and sales qty. the number of periods varies by product, since sales and purchase varies by product.Stock is always recorded at the current period. There is no constant number of year and period records. The table is sorted by product, year and period.
The table roughly looks like this:
product, year, period, stock, purchase, sales, balance
A, 2011, 10, 100, 50, 175, -25
A, 2011, 11, 0, 50, 100, -75
B, 2011, 10, 25, 5, 15, 15
B, 2011, 11, 0, 0, 20, -5
B, 2011, 12, 0, 50, 30, 15
C, 2011, 10, 5, 0, 0, 5
C, 2012, 02, 0 15, 20, 0
What I want to do is to calculate the balance over time per year and period so the balance for the first line per product is stock+ purchase - sales.
The next line for the same product it should start with the balance of the previous line + purchase - sales = balanace. This should be continued until the product changes. It should then start again calculating the balance for the first line (stock + purchase - sales) and continue with the next line from the table.
Is there an easy way to get this done?? Thanks for any advice and/or help.
