auto increment int value and reset to 1 based on

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)

auto increment int value and reset to 1 based on

Author

Topic

sql20
Starting Member

3 Posts

Posted - 2005-07-12 : 11:40:02

Hello experts

I need some help in figuring out the best way to handle this I have a table that has three columns an I need to increment the line number on each matching invoice and reset on the next invoice any suggestions will greatly be appreciated

Account linenumber cost
1000-5 1 .50
2000-5 1 .25
3000-5 1 2.00
3000-5 2 2.00
3000-5 3 50
4000-5 1 .10
4000-5 2 .50

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 14:14:23

My suggestion is that you don't really need that. What are you using the linenumbers for?

is it part of a key?
is it to order lineitems in order they were entered?
is it to add linenumbers to the invoice report?

if it's the first 2 then just use an identity, it doesn't need to reset for each invoice
if it's just for the invoice report, generate them at the presentation layer (the report writer)

Be One with the Optimizer
TG

sql20
Starting Member

3 Posts

Posted - 2005-07-12 : 14:19:56

Thanks For the Response

but It's a little bit more complicated than that

An invoice may just one line or it can have multiple lines
when the invoice is entered I need to build sequences for it for example invoice 500 has 3 lines and invoice 600 has 2 lines the table should look like this

Invoice# Seq
500 1
500 2
500 3
600 1
600 2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 14:25:22

Sorry, I don't get why the sequence can't be:

500 1
500 2
500 3
600 4
600 5

invoce 500 still has 3 lines and invoice 600 still has 2 lines

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-07-12 : 16:29:35

I see what he is saying, since they are two different invoices he wants to simply have an ordering (line items) per invoice.
It is very simple sql20, I had to do something similiar with issues with multiple action items.
You just need one field to increment per invoice:

(SELECT ISNULL(MAX (ActionItemNumber), 0)+1 FROM ActionItem WHERE IssueID=@IssueID)

So you will simply add that line to your insert for the LineNumber:

INSERT INTO YourInvoices( InvoiceID, LineNumber)
SELECT @InvoiceID,
(SELECT ISNULL(MAX (LineNumber), 0)+1 FROM YourTableHere WHERE InvoiceID=@InvoiceID)

The results:

500 1
500 2
500 3
500 4
600 1
600 2
700 1
800 1
800 2
800 3

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-07-12 : 16:33:01

BTW, be careful with deletes, in order to delete you will need to pass in the InvoiceID and the LineNumber:

Something like so:

DELETE FROM YourTable
WHERE InvoiceID = @InvoiceID AND LineNumber = @LN

UPDATE YourTable
--reset the numbering of the line items
SET YourTable.LineNumber = YourTable.LineNumber-1
--for anything greater than what is going to be deleted
WHERE YourTable.InvoiceID=@InvoiceID AND YourTable.LineNumber > @LN

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-12 : 16:47:13

>>he wants to simply have an ordering (line items) per invoice

These will order the same:
600 1
600 2

600 4
600 5

I really don't mean to be persnickidy, but why go to the extra trouble especially for the deletes. Is a nice, sequencial, starting-at-one lineitemnumber worth making multiple updates whenever you want to delete a record? And how do you insert a whole invoice worth of lineitems at once?

Aren't you glad you don't have to work with me?

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-07-12 : 16:55:12

TG technically you are correct, it's those damn users who want to see line items :) with no gaps, although I agree with you, sometimes I just don't get my ways though :).

BTW whole invoices wont be an issue either, you can insert the invoice and call a procedure to insert each line item.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-07-12 : 16:56:15

quote:
Originally posted by TG

Aren't you glad you don't have to work with me?

Be One with the Optimizer
TG

Actually I could use some help :).

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

sql20
Starting Member

3 Posts

Posted - 2005-07-12 : 18:10:37

Thanks for the assistance

I really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources