Assumption based calculation

[#1]

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	Imported
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 'Imported' qty and after this 'un-imported' qty.

Now the requirement is that, we have to calculate month wise opening & closing stock for 'imported' and 'un-imported' Qty. If Imported qty is not available in stock then it will show zero balance.

For Example, available stock is as follow:

Month       Item       ImportedQtyStok   

-------------------------------------------------------------

Jan-2014     ACB       60

Feb-2014     ACB       0

Mar-2014    ACB         0

Apr- 2014    ACB

[anbu1984]

Can you explain more on your requirement? How did you calculated Imported Stock qty?

[#3]

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	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:

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