Hi,
I have the following data in Prod table:
WK_NUM Prod Qty
------------- ------ -----
1 A 0
2 A 10
3 A 20
4 A -15
5 A -20
6 A 10
7 A 5
8 A 50
9 A 15
10 A 0
11 A -10
12 A -30
13 A -40
14 A 100
15 A -20
16 A 40
17 A 35
18 A 20
19 A 10
I need to generate the below Result column:
WK_NUM Prod Qty Result Comment on how to generate the Result column
------------- ------ ----- -------- --------------------------------------------------------------------
1 A 0 0 Value is zero, so copy as is
2 A 10 10 Value is positive, so copy as is
3 A 20 20 Same
4 A -15 0 Negative so make zero, Keep it zero till the point
where the total does not become positive again
5 A -20 0
6 A 10 0
7 A 5 0
8 A 50 30 Addition of values from WK_NUM=4 to
WK_NUM=8 becomes positive again
9 A 15 15 Value is positive, so copy as is
10 A 0 0
11 A -10 0 Negative so make zero, Keep it zero till the point
where the total does not become positive again
12 A -30 0
13 A -40 0
14 A 100 20 Addition of values from WK_NUM=11 to
WK_NUM=14 becomes positive again
15 A -20 0 Negative so make zero, Keep it zero till the point
where the total does not become positive again
16 A 40 20 Addition of values from WK_NUM=15 to
WK_NUM=16 becomes positive again
17 A 35 35 Value is positive, so copy as is
18 A 20 20 Value is positive, so copy as is
19 A 10 10 Value is positive, so copy as is
Hope I have been clear in providing the correct details.
Any hints/solutions on how to generate the above Result column would be appreciated.
Thanks in advance,