Data Types:
TYPE : CHAR(1) starts at position 31
AMOUNT: DEC FIXED (9,2) starts at position 77 (just for clarity, I have mentioned below amounts in readable form)
Code:
TYPE AMOUNT
A 0.00
M +31.75
I +8.50
A -11.44
M -25.75
I -30.34
A +55.12
I 0.00
Requirement:
a) sort based on TYPE
b) count based on values i.e, positive ( >0), negative ( <0) and zero ( =0)
c) sum based on values i.e, positive ( >0), negative ( <0) and zero ( =0)
Output:
To be in readable report format as below
Note: since M does not have a 0.00 in input, output count should be populated with a 0
Code:
TYPE POSITIVE NEGATIVE ZERO POSITIVE-SUM NEGATIVE-SUM
A 1 1 1 +55.12 -11.44
I 1 1 1 +8.50 -30.45
M 1 1 0 +31.75 -25.75
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
It isn't clear from your example exactly what you want to do.
Can you only have at most one positive, one negative and one zero value for each type as shown in your example? Or can you actually have mre than one of each value? For example, could you have:
Code:
A +31.75
A +22.83
A -16.00
A -12.00
A 0.00
A 0.00
and if so, what would you want for the output record?
Also, when you give the position as 31, is that counting the RDW in positions 1-4 or is the position really 35, not 31?
Thanks for your reply... please find more information:
a) there can be more TYPE and more AMOUNT. like as you said
Code:
A can have +31.75,+22.83,-16.00,-12.00,0.00,0.00 .....
I can have +8.50,-30.34,0.00,-55.95,-9.99 ......
M can have +33.87,-3.89,0.00,-45,76,0.00,0.00 .....
X can have +22.83,+33.87,-12.00,0.00 ....
B can have +33.87,-3.89,0.00,-45.00,-76.00,-23.45,0.00 .....
:
b) in output, I want TYPE to be in sorted order and
count of all positive, negative, zero values, total of all positive values and total of all negative values for a given TYPE. for example, with above inputs, output should be as below:
Code:
TYPE POSITIVE NEGATIVE ZERO POSITIVE-SUM NEGATIVE-SUM
A 2 2 2 +54.58 -28.00
B 1 4 2 +33.87 -148.34
I 1 3 1 +8.50 -96.28
M 1 2 3 +22.83 -28.00
X 2 1 1 +56.70 -12.00
c) regarding positions,
This count does not include RDW and it is the real position of fields TYPE and AMOUNT as below:
TYPE : CHAR(1) starts at position 31
AMOUNT: DEC FIXED (9,2) starts at position 77
