Hello to All,
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 | |
| 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:
Month Item ExcisableStok Qty
-------------------------------------------------------------
Jan-2014 ACB 60
Feb-2014 ACB 0
Mar-2014 ACB 0
Apr- 2014 ACB 1500
-----------------------------------------------------------
Please provide your suggestion to accomplish this.
Thanks in Advance
