Convert sequence number into ordinal value during load

[toddbuss]

I had this question, and then my agency's ETL guys already solved the problem.  Now my question is purely academic....

Here's a fictional table to illustrate my question:

Owner, pet, sequence

bill, cat, 1

bill, dog, 2

bill, fish, 3

jack, cat, 3  //note there is no sequence 1&2 for jack.

jill, dog, 1

jill, cat, 4 //note there is no sequence 2&3 for jill.

during the data load, i need to assign a value to each record that indicates the ordinal value of each pet.  I want to add a field to the table called "order":

Owner, pet, sequence, order

bill, cat, 1,1

bill, dog, 2,2

bill, fish, 3,3

jack, cat, 3,1  //note there is no sequence 1&2 for jack.  the cat is the first pet in the order.

jill, dog, 1,1

jill, cat, 4,2   //note there is no sequence 2&3 for jill. the cat is the 2nd pet in the order.

[MK9885]

Thats stupid requirement...

I'd say to business whats in the Order, it's just Cats & Dogs.

[toddbuss]

Agreed.  it's a stupid requirement because it's not real.

The example is just an abstract representation of the problem.  I'm dealing with mental health diagnoses (multiple per patient) which are recorded in an ordinal way so that the primary diagnosis will be the earliest in the sequence.  Each record also has a time, date, and category assigned to it.  My abstract was just trying to distill things down.

I could find for the diagnosis with the minimum sequence in each case, using set analysis, but then I'd need to do the same thing with the secondary diagnosis, etc...with up to 10 sequences per patient.  I'd rather assign the ordinal value during the load.