Skip to Content
0
Former Member
Oct 21, 2005 at 01:05 PM

How to build this internal table?

20 Views

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.