I am trying to perform a complex calculation which is based on an Assumption as per company policy.
For this, we have two tables: one for receiving transactions and other for consumption transactions.
Receiving Trans:
Date
Item
Rec Qty
Excisable
Jan/02/2014
ACB
500
Yes
May/02/2014
ACB
300
No
Jan/03/2014
ACB
200
No
Jan/03/2014
ACB
200
Yes
Feb/08/2014
ACB
1000
No
Mar/08/2014
ACB
10
No
Apr/08/2014
ACB
1550
Yes
May/03/2014
ACB
500
Yes
Jun/03/2014
ACB
30
No
Jul/03/2014
ACB
40
Yes
Aug/04/2014
ACB
100
Yes
Sep/05/2014
ACB
450
No
Oct/02/2014
ACB
305
Yes
Nov/02/2014
ACB
100
No
Dec/02/2014
ACB
50
Yes
Consumption Trans:
Item
Issued Qty
Date
ACB
300
Jan/02/2014
ACB
300
May/02/2014
ACB
150
Jan/03/2014
ACB
190
Jan/03/2014
ACB
800
Feb/08/2014
ACB
40
Mar/08/2014
ACB
50
Apr/08/2014
ACB
430
May/03/2014
ACB
30
Jun/03/2014
ACB
40
Jul/03/2014
ACB
80
Aug/04/2014
ACB
300
Sep/05/2014
ACB
150
Oct/02/2014
ACB
200
Nov/02/2014
ACB
130
Dec/02/2014
As shown above, consumption table do not have 'Imported' Flag. But Company assume that first they will consume 'Excisable' qty and after this 'un-excisable' qty.
Now the requirement is that, we have to calculate month wise opening & closing stock for 'Excisable' and 'un-excisable' Qty. If Excisable qty is not available in stock then it will show zero balance.
For Example, available Excisable stock should be as follow:
