Computing and Incrementing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Computing and Incrementing

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2004-07-26 : 11:02:47

Hi ALL
lets say you had a table with a PK containing random number, invoice Number , price columns.
and you needed to add a forth column that will increment each itme on like invoice number and start back at 1 for new invoice number.....

example
PK Inv# $ ?
867 A005 $5.00 1
986 A005 $3.00 2
989 A005 $6.00 3
968 B008 $4.00 1
969 C009 $5.00 1

do I need to create a complex cursor or is there a function like compute or something that can handle this

Thanks Steve

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 11:47:11

Nope....

Let me ask...how do you know which one to order to be the first, last, ect.

Why is this important to you?

Brett

8-)

EDIT: And how many rows are we discussing?

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 11:47:28

[code]
DECLARE @ItemNo int,
@LastInvoiceNumber
SELECT @LastInvoiceNumber = 0 -- Force first item to start at Item 1
UPDATE MyTable
SET @ItemNo = CASE WHEN @LastInvoiceNumber = MyInvoiceNumber THEN 0 ELSE @ItemNo END,
@ItemNo = MyItemColumn = @ItemNo+1,
@LastInvoiceNumber = MyInvoiceNumber
[/code]
But you would have to do something to order the records by InvoiceNo - which probably means using a temporary table, updating that with the new ItemNo information, and then JOINing that to the original table to UPDATE that.

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 11:48:56

quote:
Originally posted by X002548

Let me ask...how do you know which one to order to be the first, last, ect.

Why is this important to you?

Perhaps he just wants a decent PK on the invoice items - I would :)

Kristen

shebert
Yak Posting Veteran

85 Posts

Posted - 2004-07-26 : 12:20:52

Hello
I beleive that is the idea of this column to create an order where none exist......not alot of data 1000 maybee.
Steve

shebert
Yak Posting Veteran

85 Posts

Posted - 2004-07-27 : 10:58:23

I was able to modify the logic to get what I needed.

DECLARE @IncrNo varchar(50),
@LastInvoiceNumber float
SELECT @LastInvoiceNumber = 0 -- Force first item to start at Item 1
UPDATE @orderdetail
SET @IncrNo = CASE WHEN @LastInvoiceNumber <> ordermastercounter THEN 1 ELSE @IncrNo+1 END,
U_RegNo =u_RegNo+'/'+@IncrNo,
@LastInvoiceNumber = ordermastercounter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources