HI all,
kindly help me to solve.
i have a scenario .I have to find parts creation date based on the stock condition.
I have below sample data.
The opening count -here shows the count of parts on a opening of a day
buying quantity-purchased qty on a particular day
selling quantity -sales of a part on a particular day
i need to find parts creation date based on the stock.
For example,the parts creation date for first row is the same as the date in the Column1.
Then the calculation of parts creation date follows as below;
we need to look up for the cumulative count of the 'selling quantity' against the opening count.
100>10 =1-jan-14
100>10+50=1-jan-14
100>10+50+20=1-jan-14
100>10+50+20+20=1-jan-14
100>10+50+20+20+25 condition is false as 100>125 so it should check for the next parts creation date.
here the difference is 25 .it checks the first row of buying quantity which is 5 .
i.e 5>25 -false
then 5+10 >25 -false
then 5+10+10>25 -false
then 5+10+10+10>25 -true,so the corresponding date is taken as the next part creation date.here 4-jan-2014
Then if the buying quantity is null or zero,the parts creation date should be the previous date on which buying quantity has values.
here 5-jan-2014.
Then the next part creation date will be the date where buying quantity has values.
| DATE | opening count | buying quantity | selling quantity | Closing count | Parts creation date | cumulative |
| 1-Jan-14 | 100 | 5 | 10 | 95 | 1-Jan-14 | 10 |
| 2-Jan-14 | 95 | 10 | 50 | 55 | 1-Jan-14 | 60 |
| 3-Jan-14 | 55 | 10 | 20 | 45 | 1-Jan-14 | 80 |
| 4-Jan-14 | 45 | 10 | 20 | 35 | 1-Jan-14 | 100 |
| 5-Jan-14 | 35 | 10 | 25 | 20 | 4-Jan-14 | 125 |
| 6-Jan-14 | 30 | 0 | 20 | 10 | 5-Jan-14 | 145 |
| 7-Jan-14 | 10 | 0 | 10 | 0 | 5-Jan-14 | 155 |
| 8-Jan-14 | 0 | 0 | 0 | 0 | 5-Jan-14 | |
| 9-Jan-14 | 0 | 0 | 0 | 0 | 5-Jan-14 | |
| 10-Jan-14 | 0 | 0 | 0 | 0 | 5-Jan-14 | |
| 11-Jan-14 | 0 | 30 | 20 | 10 | 11-Jan-14 | |
| 12-Jan-14 | 10 | 15 | 10 | 15 | 11-Jan-14 | |
| 13-Jan-14 | 15 | 20 | 15 | 20 | 13-Jan-14 | |
