new year new number Generating in SQL server
-
how can ido this ?
Every new year new number Generating in SQL server
evry new year Generating new number !
-------------------------------------
I fond this code
it is a vbscript
how can i convert it to sql server
-----------------------------------------------------------
rs.open "select max(coloumn) as mcoloumn from table ",conn
if not rs.eof then
mval = rs("mcolumn") + 1
end if
if isnull(mval) then
mval = 1
end if
mprint = (year(date) * 1000000) + mval
---------------------------------------------------------------------------------------
thkns ilan
-
Not sure what you mean. Do you mean you want to have like a generated number but start at say 20030000 to provied numbers 20030000 - 20039999 or some other bit? ANd if so do you know your maximum number of records per year.
-
That looks like a snippet of code from a bigger VB program. You appear to be accessing a table thru an ADO connection. You need to alter the ADO Connection string to point from its current location to your SQLServer database (assuming the tables/columns have been recreated there).
The code should run as is.
-
i wont new evry year
to start from from 1
but like this
in start of the year 2003
1-2003 2-2003 ......................................................................99999-2003
and in the start of the year 2004
like this
1-2004 2-2004 ......................................................................99999-2004
and evry new year after
how can i do this ?????
-
I see one way this can be done is by using two fields and making a composite key if this is to be a driving data key.
Create a table with a smallint column with a default of datepart(yyyy,getdate()) and the secound column is an identity field. Like so
CREATE TABLE [tblTest] (
[YEARVAL] [smallint] NOT NULL CONSTRAINT [DF_tblXXX_YEARVAL] DEFAULT (datepart(year,getdate())),
[AUTONUM] [int] IDENTITY (1, 1) NOT NULL ,
[ValX] [char] (1) NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[YEARVAL],
[AUTONUM]
 
ON [PRIMARY]) ON [PRIMARY]
GO
Now the year will handle itself. But you have a job that at midnight goes out and does a DBCC CHECKIDENT with reseed like so.
DBCC CHECKIDENT ('tblTest',reseed,0)
The reason for 0 is the next value after is used so each year setting back to 0 will have the identity field start at 1 to whatever.
It works fine simply because the key is based on both fields not the one.
This is a trick we use on some systems to segment years. Works best on systems where overnight is mostly not used.
-
i have a little problem
i wont to add this(the new) Column
to table thet i use thet have records !!!
and i have one IDENTITY Column with AUTONUM
-
problem with the code
----------------------------------
CREATE TABLE [tblTest] (
[YEARVAL] [smallint] NOT NULL CONSTRAINT [DF_tblXXX_YEARVAL] DEFAULT (datepart(year,getdate())),
[AUTONUM] [int] IDENTITY (1, 1) NOT NULL ,
[ValX] [char] (1) NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[YEARVAL],
[AUTONUM]
ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------------------
the error
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ON'.
-
The character face should be a ) not the face, replace it within the code and should work right.
-
not work !!
-----------------
CREATE TABLE [tblTest] (
[YEARVAL] [smallint] NOT NULL CONSTRAINT [DF_tblXXX_YEARVAL] DEFAULT (datepart(year,getdate())),
[AUTONUM] [int] IDENTITY (1, 1) NOT NULL ,
[ValX] [char] (1) NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED ,
[YEARVAL],
[AUTONUM],
) ON [PRIMARY]
GO
----------------------
Server: Msg 173, Level 15, State 1, Line 8
The definition for column 'YEARVAL' must include a data type.
-
Antares686's codes work well. Please morify your codes to the codes below. I tested it out and it work well. The idea is good enough to solve your problem.
CREATE TABLE [tblTest] (
[YEARVAL] [smallint] NOT NULL CONSTRAINT [DF_tblXXX_YEARVAL] DEFAULT (datepart(year,getdate())),
[AUTONUM] [int] IDENTITY (1, 1) NOT NULL ,
[ValX] [char] (1) NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[YEARVAL],
[AUTONUM]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Regards,
kokyan 
-
Just noticed. Please do not cross post. Related thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=99613&app=true
-
I see you state you already have a coulmn with autonumber in it.
How are your inserts done. Direct insert statement, select insert statement, wrapped in Stored Procedure or some other way?
-
I was looking for a way to solve a similar problem, after some research, this is what I came up with:
1st, create a table like this one,
CREATE TABLE [dbo].[tblUniqueID] (
[SeqYear] [int] NOT NULL ,
[SeqNextID] [int] NOT NULL
) ON [PRIMARY]
GO
2nd, populate it by assigning a record for each year, with the SeqNextID set to 1
3rd, I then use this stored procedure to look up the current year, grab the current value of the SeqNextID field and then bump the SeqNextID value by one.
CREATE proc uspGetNextID
@SeqYear int,
@NextID int OUTPUT
AS
set nocount on
declare @err int,
@rc int
-- check that counter for this table exists
if not exists ( select * from tblUniqueID where SeqYear = @SeqYear ) goto errhandler
-- get the next id
update tblUniqueID
set @NextID = SeqNextID = SeqNextID + 1
where SeqYear = @SeqYear
select @err = @@error, @rc = @@rowcount
if (@err <> 0) goto errhandler
if (@rc <> 1) goto errhandler
return @err
errhandler:
if ( @@trancount > 0 ) ROLLBACK TRANSACTION
declare @errmsg varchar(255)
set @errmsg = 'procedure: ' + object_name(@@procid) + ' *** FATAL ERROR *** '
raiserror(@errmsg, 16, -1) with log
return @err
4th, create some mechanism to execute the stored procedure.
Hope this helps, it is working very well for my application.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply