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,
DennisRead the sticky at the top of the forum and give us what it asks for.

Thanks|||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|||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)

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
*/|||To auto increment based on another column, I don't think so.

Can't be DONE!

I don't think so.

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