[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Create key column value

Maurice

3/21/2007 8:30:00 AM

Hi there,

Hope that anyone can help me on this one.

I want to create a key column which consists of the following:
Year/Dept.nr/Incrementnumber

By that I mean when I insert a record in the table I want de field
Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
and 1 for being the increment number.

For the incrementnumber can I use something like @@Identity to get the last
used Identity and then concatenate the string? If so how do I go about it?

I have a table with Id field set to Identity(1,1). The departmentnumbers
will be designatedand the year should be the current year.


Any help appreciated.

Maurice
6 Answers

Uri Dimant

3/21/2007 8:41:00 AM

0

Hi
My guess you would want somethimg like that

create table #t
(id int identity(1,1) ,[year] int not null
, deptnumber varchar(10) not null,
project_id as cast([year] as varchar(4))+deptnumber+cast(id as varchar(10))
)

insert into #t ([year],deptnumber) values (2007,'05')
insert into #t ([year],deptnumber) values (2007,'06')

select * from #t


Note: I assumed that all columns don't allow NULL




"Maurice" <Maurice@discussions.microsoft.com> wrote in message
news:F5880FEF-C9DC-4C94-9EEE-4B022496F053@microsoft.com...
> Hi there,
>
> Hope that anyone can help me on this one.
>
> I want to create a key column which consists of the following:
> Year/Dept.nr/Incrementnumber
>
> By that I mean when I insert a record in the table I want de field
> Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
> and 1 for being the increment number.
>
> For the incrementnumber can I use something like @@Identity to get the
> last
> used Identity and then concatenate the string? If so how do I go about it?
>
> I have a table with Id field set to Identity(1,1). The departmentnumbers
> will be designatedand the year should be the current year.
>
>
> Any help appreciated.
>
> Maurice


masri999

3/21/2007 8:44:00 AM

0

On Mar 21, 1:30 pm, Maurice <Maur...@discussions.microsoft.com> wrote:
> Hi there,
>
> Hope that anyone can help me on this one.
>
> I want to create a key column which consists of the following:
> Year/Dept.nr/Incrementnumber
>
> By that I mean when I insert a record in the table I want de field
> Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
> and 1 for being the increment number.
>
> For the incrementnumber can I use something like @@Identity to get the last
> used Identity and then concatenate the string? If so how do I go about it?
>
> I have a table with Id field set to Identity(1,1). The departmentnumbers
> will be designatedand the year should be the current year.
>
> Any help appreciated.
>
> Maurice

Since you are using identity , you can use update only for the
column . Identity is ever increasing number and I don't know the
length of your Project_ID column

You can do something like this after insertion of record

update table set
Project_id = cast( year(getdate()) as VARCHAR(4))+ replace(STR(deptnum,
3),' ','0')
+ replace (STR(id,5),' ','0')

assuming deptnum will not be more than 3 digits and id will not be
more than 5 digits

Roy Harvey

3/21/2007 11:34:00 AM

0

You realize that the next thing you will be doing is writing a query
to return all the rows for a department?

WHERE SUBSTRING(Project_ID,5,3) = '055'

Please consider storing this information in three columns, not one.

As for the use of identity, it does not fit this case at all. You are
looking for each year/department to sequence from 1, incrementing by
1, are you not? Identity gives one sequence for the table, not for
any group of rows. You will have to calculate the "next number"
yourself during the INSERT.

Roy Harvey
Beacon Falls, CT

On Wed, 21 Mar 2007 01:30:14 -0700, Maurice
<Maurice@discussions.microsoft.com> wrote:

>Hi there,
>
>Hope that anyone can help me on this one.
>
>I want to create a key column which consists of the following:
>Year/Dept.nr/Incrementnumber
>
>By that I mean when I insert a record in the table I want de field
>Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
>and 1 for being the increment number.
>
>For the incrementnumber can I use something like @@Identity to get the last
>used Identity and then concatenate the string? If so how do I go about it?
>
>I have a table with Id field set to Identity(1,1). The departmentnumbers
>will be designatedand the year should be the current year.
>
>
>Any help appreciated.
>
>Maurice

Uri Dimant

3/21/2007 11:41:00 AM

0

Hi,Roy
> Please consider storing this information in three columns, not one.

While writing my post I assumed that the OP has a three columns and wanted
fourth column, now that I read you post Isee that the OP has only one column
to be concatinated. Thanks



"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:um520354gvcg2qat4ejn85uhgv256i1qda@4ax.com...
> You realize that the next thing you will be doing is writing a query
> to return all the rows for a department?
>
> WHERE SUBSTRING(Project_ID,5,3) = '055'
>
> Please consider storing this information in three columns, not one.
>
> As for the use of identity, it does not fit this case at all. You are
> looking for each year/department to sequence from 1, incrementing by
> 1, are you not? Identity gives one sequence for the table, not for
> any group of rows. You will have to calculate the "next number"
> yourself during the INSERT.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 21 Mar 2007 01:30:14 -0700, Maurice
> <Maurice@discussions.microsoft.com> wrote:
>
>>Hi there,
>>
>>Hope that anyone can help me on this one.
>>
>>I want to create a key column which consists of the following:
>>Year/Dept.nr/Incrementnumber
>>
>>By that I mean when I insert a record in the table I want de field
>>Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
>>and 1 for being the increment number.
>>
>>For the incrementnumber can I use something like @@Identity to get the
>>last
>>used Identity and then concatenate the string? If so how do I go about it?
>>
>>I have a table with Id field set to Identity(1,1). The departmentnumbers
>>will be designatedand the year should be the current year.
>>
>>
>>Any help appreciated.
>>
>>Maurice


Maurice

3/21/2007 11:41:00 AM

0

I think you might be just helping me out here. Let's say I want de number of
the last Identity value added as the last number (project_Id can be what I
want it to be).

So if I use your first piece of code getting the year and dept.nr - how can
I add the last inserted identity from an ID column (@@Identity?)

Let me get this clear for you. In the table the last added record has an
identity of 5. So I want to create an id of 20070556 where the 6 is the last
added identity + 1

Hope this makes sense.. Thanks for your effort.

Maurice

"M A Srinivas" wrote:

> On Mar 21, 1:30 pm, Maurice <Maur...@discussions.microsoft.com> wrote:
> > Hi there,
> >
> > Hope that anyone can help me on this one.
> >
> > I want to create a key column which consists of the following:
> > Year/Dept.nr/Incrementnumber
> >
> > By that I mean when I insert a record in the table I want de field
> > Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
> > and 1 for being the increment number.
> >
> > For the incrementnumber can I use something like @@Identity to get the last
> > used Identity and then concatenate the string? If so how do I go about it?
> >
> > I have a table with Id field set to Identity(1,1). The departmentnumbers
> > will be designatedand the year should be the current year.
> >
> > Any help appreciated.
> >
> > Maurice
>
> Since you are using identity , you can use update only for the
> column . Identity is ever increasing number and I don't know the
> length of your Project_ID column
>
> You can do something like this after insertion of record
>
> update table set
> Project_id = cast( year(getdate()) as VARCHAR(4))+ replace(STR(deptnum,
> 3),' ','0')
> + replace (STR(id,5),' ','0')
>
> assuming deptnum will not be more than 3 digits and id will not be
> more than 5 digits
>
>
>
>

Maurice

3/21/2007 12:01:00 PM

0

Roy,

Message received. I'll create three columns and go from that point on..

Thanks

Maurice

"Roy Harvey" wrote:

> You realize that the next thing you will be doing is writing a query
> to return all the rows for a department?
>
> WHERE SUBSTRING(Project_ID,5,3) = '055'
>
> Please consider storing this information in three columns, not one.
>
> As for the use of identity, it does not fit this case at all. You are
> looking for each year/department to sequence from 1, incrementing by
> 1, are you not? Identity gives one sequence for the table, not for
> any group of rows. You will have to calculate the "next number"
> yourself during the INSERT.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 21 Mar 2007 01:30:14 -0700, Maurice
> <Maurice@discussions.microsoft.com> wrote:
>
> >Hi there,
> >
> >Hope that anyone can help me on this one.
> >
> >I want to create a key column which consists of the following:
> >Year/Dept.nr/Incrementnumber
> >
> >By that I mean when I insert a record in the table I want de field
> >Project_ID to be like this: 20070551 being the year 2007 dept.nr being 055
> >and 1 for being the increment number.
> >
> >For the incrementnumber can I use something like @@Identity to get the last
> >used Identity and then concatenate the string? If so how do I go about it?
> >
> >I have a table with Id field set to Identity(1,1). The departmentnumbers
> >will be designatedand the year should be the current year.
> >
> >
> >Any help appreciated.
> >
> >Maurice
>