Hello all,

I have tried to stay away from cursors, but I think it is the only answer.

I have a table with members. The key fields are MemberReference, DepSeqNo and EffDate, so there will be multiple entries for members with the EffDate being the ultimate decider on the uniqueness of the entry.

I created a new column called MemRowCount and I need to assign a row number for each entry for each member from 1 onwards. As soon as the cursor hits the next member it needs to start from 1 again counting down the rows for that member and so on.

I have drawn a total blank on this. I have done something similar in VB but I just can't get my head around creating a cursor.

Can anyone help...

Thanks in advance

Can you post the exact structure of that table with datatypes used and sample records for members?

_________________________
- Vijay G
Strive for Perfection

Hi there,

The Structure is as follows and some example entries are below that. The Key fields are MemberReference, SeqNo, and EffectiveDate as a compound in that order.

[MemberReference] [char] (10) NOT NULL ,
    [PayrollNumber] [char] (10) NULL ,
    [Surname] [char] (25) NULL ,
    [Forname] [char] (20) NULL ,
    [NINO] [char] (9) NULL ,
    [DOB] [char] (8) NULL ,
    [************] [char] (1) NULL ,
    [SeqNo] [char] (2) NOT NULL ,
    [EffectiveDate] [char] (8) NOT NULL ,
    [TaxWeek] [int] NULL ,
    [StandardPaidHrs] [decimal](18, 0) NULL ,
    [BasicPaidHrs] [decimal](18, 0) NULL ,
    [MemRowCount] [int] NULL


        1 1 Smith John TN130270M 19700213 M 00 19900101 1 35 35
    1 1 Smith John TN130270M 19700213 M 00 19900201 2 35 35
    1 1 Smith John TN130270M 19700213 M 00 19900301 3 35 35
    1 1 Smith John TN130270M 19700213 M 00 19900401 4 35 35
    2 2 Smith John TN130270M 19700213 M 00 19900101 1 35 35
    2 2 Smith John TN130270M 19700213 M 00 19900201 2 35 35
    5 5 Smith John TN130270M 19700213 M 00 19900201 2 35 35

Hope this helps

Could You explain in detail what would be the data for MemberRowCount Column.

Amit Jhawar
Developer

The data in the cloumn would be an integer and a counter on the lines for each member. So, for the first member line 1 would be 1, line 2 would be 2, and so on until the first line for the next member where the line number would revert back to 1 and so on again until the next member. Does this makes sense?

The reason why I need this is because I'm preparing the data to go into another system which isn't in a relational format. So each numbered line for each member on the SQL table is going to go into different columns but 1 row per member, so line 1 will go into column 1, 2 into column 2 etc.

Your Members in the table are identified by ref. No. or Name ....
Smith Jones is same name with 3 ref no...so would u consider them three different members or same member

Amit Jhawar
Developer

The Members are identified by Reference Number, but seeing as it is like a salary stack record you would expect to see the same Reference number and SeqNo, but not the same date. A member wouldn't have two entries at the same date. So we made the key fields, ReferenceNumber, SeqNumber and EffectiveDate.

I forgot to say that you will also get instancies where a member will leave and then return at a later date, so another reason why it has to be Member reference together with the other fields to define the entry.

Dow this help?

I don't really see any reason for adding MemRowCount and populating with numbers the way you want, which you can always get by running a query based on name if needed. Could you explain why you are trying to add another column and fill that with the numbers as you wanted? Just curious to know if that is really needed for some reasons, if not we could suggest you an atlernative.

Also I see redundance in MemberReference and PayrollNumber, why that has to be same? Is there any reason behind it?

Also explain on what does MemberReference denote and when does that change. As per the records that you posted, how does 1, 2 and 5 differ from each other, as I see them all having same name, same SeqNo, except that DATE Field.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Thanks for the help.

This is just dummy data that I gave you. It's not real. Payroll Number and Member Reference Number are from two different systems and will not be identical in over 90% of the cases so no redundancy. One if from a Pensions Admin System the other from a payroll system. Both are unique identifyers on there respective systems, but the payroll number, I have been told, sometimes gets reused so again can't use this. The MemberReference Number is allways unique. The additional field of SeqNo. (00 for member, 01 for spouse, etc) also will be unique combined with the Member Reference.

Running the query on name or another identifier (NINO) will also be no good. People can leave and rejoin the company and will receive a new Member Reference number (if they rejoin the scheme) and Payroll number. All this work is service based and this is where the effective date also comes into the equation.

So the compound keyfields are Member Reference, SeqNo. and Effective Date indexed in that order.

As I mentioned all this data is being modified and manipulated so that it can go into another system (the pensions admin system) which is not relational. The data for a member in row 1 of the SQL table will go into row 1 colomn 1 of the PensAdd system, data from row 2 for that same member will go into row 1 column 2 of the PensAdd system and so on.

I can see another way of doing it but because I'm going to be looking at more than 103Million rows for this one table alone, I think that that would be more of a resource drain on the ssystem.

I hope that this clears the situation up better.

Many thanks

John