How to create new key? - 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

General SQL Server Forums

New to SQL Server Programming

How to create new key?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-03-04 : 21:41:14

Hi,

For Accounting purpose, the company would like all accounts to have running number for invoices and PO in the format ( 3 digit companycode - 4 digit year - 0000001)

e.g. ABC-2009-0000001, and the next invoice will be ABC-2009-0000002 etc.

how do I retrieve the next running number for the new key?

I can retrieve the company as an input into the stored procedure, and the year from the year(GetDate()) function. But I am not sure how to retrieve the last running number (can be 6 or 7 digits as needed) and add one to the next invoice.

As a example, the table name is Invoice, and the PK is invoice number.

Any help is very much appreciated.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-04 : 22:56:16

You can have an identity column created on that table, and use that value to build this key.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-03-04 : 23:33:01

Quote:

"You can have an identity column created on that table, and use that value to build this key."

err.. let me repharse my question

I have a the identity column already in invoice table, with column named invoice number.

What I am trying to do is to concat the previous invoice number so that I have a new key, in the format ABC-year-0000001

CREATE Procedure CreateNewKey()
@companyCode varchar(4)
AS
BEGIN
Select @companyCode + '-' + varchar(year(getDate())) + '-' + (Select
Last key)
END
GO

The problem here is I dunno how to select the last key so that it can return me a new key.

What you are suggesting is having another column that has an autonumber, then I concat to the above right.

The problem is that there are multiple companys, and each company have their own unique running number. e.g. REC-2009-000001 is different from ABC-2009-000001. If I use your suggestion, then I will end up with REC-2009-1 then ABC-2009-2. (the prevailing 0s will be terminated, and the number is not running for each company). :(

Any better suggested solutions to this requirement is very much appreciated.

Thank you.

regards,

check out my blog at http://www.aquariumlore.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources