How to get The Closing Stock For a Month....

[deepakk]

Hi All,

I have Application where I am Calculating the stokc per day at the back end. For Eg

Data:

Date	Prod	Qty
1-Jan-09	A	10
2-Jan-09	A	30
3-Jan-09	A	40
4-Jan-09	A	-10
5-Jan-09	A	-10
6-Feb-09	A	-10
7-Feb-09	A	30

Calculating Stock per Date at Script Level.

Date	Prod	Qty1
1-Jan-09	A	10
2-Jan-09	A	40
3-Jan-09	A	80
4-Jan-09	A	70
5-Jan-09	A	60
6-Feb-09	A	50
7-Feb-09	A	80

NOw I want to Display Closing stoock for each month

Output required :

Prod

Prod	Month	Qty
A	Jan	60
A	Feb	80

Please let me know if you all require any other information.

Thanks & Regards

Deepak

[prieper · Accepted solution]

You may continue to determine the stocks at the end of the Month with a script like:

STOCK_PER_MONTHEND:
LOAD
 *,
 IF(MONTH(PEEK(Date)) <> MONTH(Date), Qty1) AS StockMonthEnd
 RESIDENT
 STOCK_PER_DATE
 ORDER BY
 Prod,
 Date DESC;

DROP TABLE STOCK_PER_DATE;

Doing so, you will have one additional field showing always the last stocks at the last date for each month (and for each product).

edit: Have added a sample-file along with alternative way to calculate the stocks

HTH
Peter

[deepakk]

Thanks Peter.... It worked Fine for Me............