Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: Query Assistance

    Hi,

    I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:


    ClaimNumber LineNumber SequenceNumber
    abc123 1 1
    abc123 2 2
    abc123 3 3
    def321 5 1
    def321 6 2
    ghi456 2 1
    jkl789 3 1
    jkl789 4 2


    So...
    SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable


    Is there any way to do this?


    Thanks,
    Dennis

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read the sticky at the top of the forum and give us what it asks for.

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2005
    Posts
    3
    CREATE TABLE myTable(ClaimNumber varchar(6), LineNumber int)


    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',1)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',2)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',3)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('def321',5)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('def321',6)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('ghi456',2)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('jkl789',3)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('jkl789',4)



    My question: Is it possible to have a calculated field that, in essence renumbers (or auto increments) a particular column based on the value in another column?

    Thanks,
    Dennis

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    To auto increment based on another column, I don't think so.

    For this application, maybe a small two column table holding the ClaimNumber and LastLineNumber. As a quick example (not tested)
    Code:
     
    CREATE TABLE dbo.ClaimLineno (ClaimNo varchar(6) not null, LastLineNo int not null)
    GO
     
    ALTER TABLE dbo.ClaimLineno ADD 
    CONSTRAINT [PK_Claimno] PRIMARY KEY CLUSTERED 
    (
    [ClaimNo]
    GO
     
    CREATE PROC ap_GetNextLine @ClaimNo varchar(6), @LastLineNumber int OUTPUT 
    AS
     
    declare @rcount int
     
    BEGIN TRANSACTION GetNo
    SELECT @LastLineNumber = LastLineNo
    FROM dbo.ClaimLineno
    WHERE ClaimNo = @ClaimNo
     
    SELECT @rcount = @@rowcount
     
    IF @rcount = 1
    BEGIN
    SELECT @LastLineNumber = @LastLineNumber + 1
    UPDATE dbo.ClaimLineno
    SET	LastLineNo = @LastLineNumber
    WHERE ClaimNo = @ClaimNo
    END
    ELSE
    IF @rcount = 0
    BEGIN
    SELECT @LastLineNumber = 1
    INSERT dbo.ClaimLineno (ClaimNo, LastLineNo)
    VALUES (@ClaimNo, @@LastLineNumber
    END
     
    IF @rcount in (0,1)
    BEGIN
    COMMIT
    RETURN
    END
     
    /*
    Code appropriate error handling routine here because your table is screwed up
    */

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by tomh53
    To auto increment based on another column, I don't think so.

    Can't be DONE!

    I don't think so.


    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable(ClaimNumber varchar(6), LineNumber int)
    GO
    
    
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',1)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',2)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('abc123',3)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('def321',5)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('def321',6)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('ghi456',2)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('jkl789',3)
    INSERT INTO myTable(ClaimNumber, LineNumber) VALUES ('jkl789',4)
    GO
    
       SELECT * FROM myTable
    
       SELECT a.ClaimNumber, a.LineNumber
    	, COUNT(b.LineNumber)+1 AS Seq
         FROM myTable a 
    LEFT JOIN myTable b
           ON a.ClaimNumber = b.ClaimNumber
          AND b.LineNumber < a.LineNumber
     GROUP BY a.ClaimNumber, a.LineNumber
     ORDER BY a.ClaimNumber, a.LineNumber
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •