pls help

Hi guys,
i appreciate the speed at which the response come and the quantity as
well.
i want an identity column with repetitions allowed after a certain
condition is reached.
actually, i want to generate invoice number automatically. it will be
an integer data type.
but the seed should initialize every time when the financial year is
changed.
for example, you have to display invoice number as 1/2005 upto
1200/2005
and we should have both of these fields different viz year and invoice
number. then we will contecate them using a slash as shown.
on the next financial year it will be changed as 1/2006 upto 1200/2006.
i want a stored procedure for this that should return an identity.
thanks and regards
Suresh BeniwalSuresh
for year you can simply use YEAR(GETDATE()) AS DEFAULT.
for invoice number, I think one should not use identity. use int and
increment in the stored procedure/client
--
Regards
R.D
--Knowledge gets doubled when shared
"SureshBeniwal" wrote:

> Hi guys,
> i appreciate the speed at which the response come and the quantity as
> well.
> i want an identity column with repetitions allowed after a certain
> condition is reached.
> actually, i want to generate invoice number automatically. it will be
> an integer data type.
> but the seed should initialize every time when the financial year is
> changed.
> for example, you have to display invoice number as 1/2005 upto
> 1200/2005
> and we should have both of these fields different viz year and invoice
> number. then we will contecate them using a slash as shown.
> on the next financial year it will be changed as 1/2006 upto 1200/2006.
> i want a stored procedure for this that should return an identity.
> thanks and regards
> Suresh Beniwal
>|||and dont be misled by the word reseed. It wont set 1 again but the one more
than max number.So it is not possible to reset back to one when new year
starts.
DBCC CHECKIDENT ('table_name') or DBCC CHECKIDENT ('table_name', RESEED)
--
Regards
R.D
--Knowledge gets doubled when shared
"SureshBeniwal" wrote:

> Hi guys,
> i appreciate the speed at which the response come and the quantity as
> well.
> i want an identity column with repetitions allowed after a certain
> condition is reached.
> actually, i want to generate invoice number automatically. it will be
> an integer data type.
> but the seed should initialize every time when the financial year is
> changed.
> for example, you have to display invoice number as 1/2005 upto
> 1200/2005
> and we should have both of these fields different viz year and invoice
> number. then we will contecate them using a slash as shown.
> on the next financial year it will be changed as 1/2006 upto 1200/2006.
> i want a stored procedure for this that should return an identity.
> thanks and regards
> Suresh Beniwal
>|||Thanks a lot, R.D.
that should help
Regards
Suresh Beniwal|||>> want to generate invoice number automatically. it will be an integer dat
a type. but the seed should initialize every time when the financial year is
changed. <<
You did check to see that it follows the conventions of your accountng
department and accounting software first? I doubt it.
Does your accounting department know that you are planning to destroy
the audit trail? IDENTITY has gaps, no check digits, etc. You need to
stop and learn how to design codes. If you want to follow this pattern
put the year first, then a sequence number, then a check digit; make
sure it is fixed length. You do not want to give away information, you
can use an additive congruential generator to get values in
pseudo-random order.