-1

I have one table for sales order. In that table i want store bill number. That bill number identity start from 1 to n number for financial year, After completion of financial year it will start with new bill number,

like start from : 1 to n number.

And that time i want to insert also financial year in a column.

Help me what should i do for achieve this functionality.

For Example:

enter image description here

Manish Sharma
  • 2,406
  • 2
  • 16
  • 31
  • please provide some examples what you want to achieve using examples rather in terms of words – TheGameiswar Jun 05 '16 at 07:39
  • 1
    you may want to check here ,question is still unclear :https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – TheGameiswar Jun 05 '16 at 07:43
  • Take the total count of bill number in every financial year and increment by one, then save it manually to database, so that you can get bill number from 1to n every year – Arun Prasad E S Jun 05 '16 at 07:45
  • Have a look into this: https://blogs.msdn.microsoft.com/askjay/2012/10/08/sequence-objects-in-sql-2012-and-sql-2008/ – Alex Jun 05 '16 at 07:46
  • I don't think it's a good idea to achieve this with identity, or you can start a new table for every year, could give a better performance – Arun Prasad E S Jun 05 '16 at 07:47
  • 1
    @ARUN, "Take the total count of bill number in every financial year and increment by one" is a bad solution, as you will have concurrency problems – Alex Jun 05 '16 at 07:47
  • @ARUN, I don't want to create new table for the same.I have existing application only i want to change the bill number. I think Alex is right. – Manish Sharma Jun 05 '16 at 07:49
  • But it's endless sequence, we can't start over again i think – Arun Prasad E S Jun 05 '16 at 07:51
  • If you want to make a change to old data, what will happen, I don't know much finance, whether closed are edited – Arun Prasad E S Jun 05 '16 at 07:57
  • @ARUN, That I'll do but first i want solution for this. – Manish Sharma Jun 05 '16 at 07:59
  • Have a a look at composite key, haven't used it myself – Arun Prasad E S Jun 05 '16 at 08:01
  • 1
    @Manish Sharma, in my opinion I would not restart bill numbers for each financial year as this may cause confusion. Just let them increase ad infinitum. Storing financial year information next to bill number is enough to allow you to find which bill belongs to which year. You may have other specific reasons for why you want numbers to restart. The answer provided by Akshey Bhat should work when you have a clearly defined cut over event/time i.e. you allocate all bills entered before this moment to current financial year and then after this moment it is next financial year. – Alex Jun 05 '16 at 08:02
  • Because CHECKIDENT RESEED is dangerous I would use a [custom solution](https://stackoverflow.com/questions/37419447/how-to-best-generate-a-unique-user-number-without-duplicates-in-a-multi-tenant-d). – Bogdan Sahlean Jun 05 '16 at 12:14
  • Why do you care what the number is? either it's an internal key, in which case you shouldn't care, or it's a business key, which you should store in a different field. Don't mix them up. Does this requirement come from a reasonable business requirement? What impact will it be if the number for the new year starts at 2? at 10? at 1000? – Nick.Mc Jun 05 '16 at 12:41
  • Another alternative is to use "sequences table" described here: https://blogs.msdn.microsoft.com/sqlcat/2006/04/10/sql-server-sequence-number/ – Alex Jun 06 '16 at 05:43

1 Answers1

4
DBCC CHECKIDENT ("table name", RESEED, 0);

After inserting rows for one financial year run the above script to reset identity column.

Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20