Add Sequential numbering of groups of records by date 1

[cgarmas]

Hi all,
I am using MS SQL and I need help with creating a column were I can add a sequantial numbering of groups of records based on sequantial dates. The table structure is as follows
MemberID EnrollDate TermDate
001 01/01/2005 05/30/2005
001 07/01/2005 09/30/2005
001 10/15/2005 12/31/2005
002 01/12/2005 06/30/2005
002 07/15/2005 12/30/2005
003 02/01/2005 08/30/2005
003 09/01/2005 12/25/2005

I need this table to look like this

MemberID EnrollDate TermDate Seq_Number
001 01/01/2005 05/30/2005 1
001 07/01/2005 09/30/2005 2
001 10/15/2005 12/31/2005 3
002 01/12/2005 06/30/2005 1
002 07/15/2005 12/30/2005 2
003 02/01/2005 08/30/2005 1
003 09/01/2005 12/25/2005 2

I'll appreciate any advice to help me resolve this problem.
Cgarmas

 

[gmmastros]

You said...

In this database we have two fields that have MemberIDs, one is the MemberID, and MemberID2, the first one stores the unique ID for each member, but the same member may have multiple MemberID2.
The problem is that some times members start a new enrollment period and on the same date of that enrollment they receive a new MemberID2 and therefore the new MemberID2 would have the same EnrollDate, creating a duplicate record for the unique MemberID

Those Min's were added to the code so that we wouldn't get any duplicate values. It's possible that Min was a poor choice and that you should really be using Max instead. In situations like this, it's difficult because I don't really understand your data like you do.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom