Post reply

TSQL HELP NEEDED

  • May 9, 2005 at 10:40 am

    #66417

    I have a table with the following structure:

     

    MemberNumber,  CategoryNumber,  Group Number,  Startdate

    Every time a CategoryNumber or GroupNumber changes in the Member's record

    a new record is added.  I need to add a sequence number to identify

    a Member's record, thus:

     

    MemberNumber, CategoryNumber, GroupNumber, Startdate, MemberSeqNbr

    So, if a Member has five records, I would like to sequence numbers 1 - 5 for that member.

    Given the existing table structure, is there a single SQL statement I can use to populate the new MemberSeqNbr column?

     

     

  • May 9, 2005 at 10:59 am

    #557656

    I haven't tested this but you can give it try.

    Update YourTable

    set MemberSeqNbr =

     (Select ISNULL(max(MemberSeqNbr),0) + 1

      From YourTable yt

      Where yt.MemberNumber = YourTable.MemberNumber)

    Where MemberSeqNbr IS NULL

    Edit: added isnull to Max()

    Edit: A quick test indicates that this does NOT give the desired results - sorry.

     

  • May 9, 2005 at 12:25 pm

    #557681

    Is there anything that currently makes these rows unique?

     

  • May 9, 2005 at 12:31 pm

    #557683

    MemberNumber and Startdate

  • May 9, 2005 at 1:53 pm

    #557696

    can you post a sample row and desired MemberSeqNbr ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • May 9, 2005 at 2:00 pm

    #557697

    I believe I solved my own problem. Thus,

    ALTER TABLE MYTABLE

    Add MemberSeqNbr int NULL

     

    update mytable set memberseqnbr =  (select count(*) + 1 from mytable m1 where mytable.membernbr = m1.membernbr and mytable.startdate > m1.startdate&nbsp

     

    Thanks for your interest and assistance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply