January 26, 2006 at 9:41 am
I have a table with the following structure:
MemberNbr char(9)
StartDate datetime
EndDate datetime
MemberGrp1 varchar(8)
MemberGrp2 varchar(8)
MemberGrp3 varchar(8)
Clustered Index on MemberNbr, StartDate, MemberGrp1, MemberGrp2, MemberGrp3
Sample Data:
MemberNbr StartDate EndDate MemberGrp1 MemberGrp2 MemberGrp3
123456789 01/01/2005 01/31/2005 A12345 B12345 C12345
123456789 02/01/2005 02/28/2005 A12345 B12345 C12345
123456789 03/01/2005 03/31/2005 V12345 B12345 C12345
123456789 04/01/2005 04/30/2005 A12345 B12345 C12345
What I need to do is establish a sequence number for each group where the
Membernbr, MemberGrp1, MemberGrp2, and MemberGrp3 are the same; sequencing the records as 1,2,3.....etc. by StartDate AND also re-starting the
sequence number to 1, if there is a break of continuity, as there is in the above example, for the 3rd and 4th lines, where there is a change in MemberGrp1. The 4th line, in the above example, would be the start of a new sequencing, even though lines 1 and 2 have the same Membernbr, MemberGrp1, MemberGrp2, and MemberGrp3. There is a break in the continuity so I need to start the sequence number at 1 for the 4th line, thus:
MemberNbr StartDate EndDate MemberGrp1 MemberGrp2 MemberGrp3 Seq
123456789 01/01/2005 01/31/2005 A12345 B12345 C12345 1
123456789 02/01/2005 02/28/2005 A12345 B12345 C12345 2
123456789 03/01/2005 03/31/2005 V12345 B12345 C12345 1
123456789 04/01/2005 04/30/2005 A12345 B12345 C12345 1
Any help with SQL to do the correct sequencing?
January 26, 2006 at 11:07 am
Surely possible with a cursor.
Will think about a set based solution.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply