Hi,
I have an internal table with the following data. Table has 4 fields, CUSTOMER, SALESORDER, DATE and STATUS. The data has been sorted(high to low) by CUSTOMER DATE. There can be any number of records for a each customer. STATUS ia blank for all the records initially.
(I am simply using this symbol '.|.', for display)
CUSTOMER.|.SALESORDER.|.DATE.|.STATUS.|
12345678.|.5674.|.10/05/2005
12345678.|.4200.|.06/10/2005
12345678.|.3852.|.04/15/2005
12345678.|.6852.|.01/05/2005
12345678.|.3500.|.10/20/2004
87654321.|.6200.|.10/20/2005
87654321.|.4900.|.05/26/2005
87654321.|.5874.|.02/20/2005
87654321.|.3800.|.12/15/2004
87654321.|.4000.|.06/18/2004
87654321.|.2500.|.01/15/2003
From this table, I am only interested in only TOP TWO records(based on most recent dates) for each CUSTOMER. This is what I want to retain.
CUSTOMER.|.SALESORDER.|.DATE.|.STATUS.|
12345678.|.5674.|.10/05/2005
12345678.|.4200.|.06/10/2005
87654321.|.6200.|.10/20/2005
87654321.|.4900.|.05/26/2005
Once I can get to this data, now I want to populate the 'STATUS' field with the following logic. For every Customer, Most recent record gets the record 'C' and the previous record gets the status 'P'..
CUSTOMER.|.SALESORDER.|.DATE.|.STATUS.|
12345678.|.5674.|.10/05/2005.|.C.|
12345678.|.4200.|.06/10/2005.|.P.|
87654321.|.6200.|.10/20/2005.|.C.|
87654321.|.4900.|.05/26/2005.|.P.|
Can anyone help me with this, please.