>I cant figure out how to do this. I have a table which looks like:
>
>
>proc_code ind1 val1 date1 ind2 val2 date2 ind3 val3 date3
>--------------------------------------------------------------------------
>000000001 A 102 10/11/99 A 155 04/02/01 Y 155 05/16/98
>000000002 C 865 02/19/98 W 251 03/12/00 K 157 12/12/98
>
>
>What I need to do, is when the IND value repeats in any of the other
>IND columns on the SAME record, get the VAL value for the latest date.
>
>In the above example, this would mean that on the first record, I would
>get back the VAL2 value of 155, because the IND "A" repeats and Date2
>is the greatest date.
>
>On record 2, the IND does not repeat, so ignore the record.
>
>Anyone have any ideas?
Kevin,
Yes, redesign your data to be normalized and get rid of the multivalued attributes.
New design:
Products
Fields
ProdCode
etc ...
Ind (? i have no idea what these are)
Fields
ProdCode
Ind
Val
Date
these two tables have a one to many relation;
Sample data in Ind
ProdCode Ind Val Date
000000001 A 102 10/11/99
000000001 A 155 04/02/01
000000001 Y 155 05/16/98
000000002 C 865 02/19/98
000000002 W 251 03/12/00
000000002 K 157 12/12/98
Now your task is simple, look at the Ind table ordered on ProdCode + Ind + Date (Descending) You can scan the table for each prodcode and recognize a repeated Ind value for the same ProdCode, move to the firs tof these and you have your most recent date. You are also not limited to a set number of Ind's nor are you wasting space when you have less Ind's.
Of course you could write the code to take each Prodcode in your existing structure, read each Ind field and find any repaeting values then compare each of the corresponding dates to find the most recent and then get the value for that date. It would never be a fast as the suggested approach but it can be made to work. That is until you need more Ind's than your structure allows for.