Quotation number generation - 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

Quotation number generation

Author

Topic

roosterbrood
Starting Member

2 Posts

Posted - 2011-08-01 : 06:35:04

Hi, I am currently writing a database for my company in which all client details is post.

I like the SQL sever to create a quotation number in the following form: AMD/month/xxx, were the month is the month in which the quotation is made, AMD always stands in front of the number and then xxx is the following number in the list. Thus the server check the date and then which number is next for the month, always starting at 1 for each month. How would I do it?

Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 08:32:44

This bit of code would do it - but there is something that bothers me about doing it the way I have done (and you are proposing to do it). For one thing, it assumes that your business will remain a small business that generates no more than 999 invoices per month. But there is something more, not sure what that is though...



INSERT INTO YourTable
SELECT 
	'AMD/' 
	+ DATENAME(MONTH,GETDATE()) 
	+ '/' +
	RIGHT( '000' + CAST( COALESCE( MAX(RIGHT(invoice_number,3)), 0) + 1 AS VARCHAR(3)),3)
FROM 
	YourTable WITH (SERIALIZABLE);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-01 : 16:04:05

Probably need a WHERE clause (assuming 0-999 invoices per month):

INSERT INTO YourTable
SELECT 
	'AMD/' 
	+ DATENAME(MONTH,GETDATE()) 
	+ '/' +
	RIGHT( '000' + CAST( COALESCE( MAX(RIGHT(invoice_number,3)), 0) + 1 AS VARCHAR(3)),3)
FROM 
	YourTable WITH (SERIALIZABLE)
WHERE
    invoice_number LIKE 'AMD/' + DATENAME(MONTH,GETDATE()) + '%';

roosterbrood
Starting Member

2 Posts

Posted - 2011-08-04 : 01:47:15

Thanks for your reply, I will try it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources