1. Registered User
Join Date
Nov 2005
Posts
3

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. Window Washer
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

3. Registered User
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. 9th inning DBA
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

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
*/```

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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```

#### Posting Permissions

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