| Author |
Topic |
|
Sohaib
Starting Member
10 Posts |
 Posted - 2005-06-20 : 07:41:26
|
| Hi guyz!!I want to know the details abt the last record information...The problem which i m facing tht i want to generate a key on the basis of date....right....with serial number increases one after the other...I m asking this bcoz i think so if i know the last record information then i will able to generate next key accurately if u people ve any good idea how it can b manipulate so i will really thankful to that guy;..Let we have today's date:-20-06-2005, K=KeyThe key should b like thtK-050620-serialNumberit will continue increases one till the end of the day....I want some query related to tht or some technique how i can implement it...plz help me in this regard....Bye |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-06-20 : 07:51:17
|
| If you want to make a key like that, then you should hold the seperate parts of the key in seperate columns, in my opinion.-------Moo. :) |
 |
|
|
Sohaib
Starting Member
10 Posts |
Posted - 2005-06-20 : 08:07:27
|
| Hi Mr.Mist!!I think so u r right enough in some sense but if i m trying to make serial number with a separate column can u understand that i have to restart serial whenever a new date started......so there will not b any primary key as well bcoz everything is repeating one after the other....anyways thnx for ur reply if u ve another good advice or u can refine my idea so it will b helpful to me...ok Bye |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-06-20 : 08:24:51
|
| The primary key is the combination of all three columns. It would be the same whether you split them or not. I don't see why you need to restart the serial number every day, but in the event that that's what you choose to do, I could only suggest that you use a trigger on the table that checks for the (max) serial grouped by day. Another reason to keep that part seperate, really.-------Moo. :) |
|
|
|
Sohaib
Starting Member
10 Posts |
Posted - 2005-06-20 : 11:13:01
|
| Hi Mr.Mist!!It's really a good option which u ve given me but i can't make a good trigger to work on...so plz help me...and if u can make a right trigger for me then i will really appreciate u...Have a nice day...Hope for the positive response from u..OkBye |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-20 : 11:45:15
|
| Sohaib, can you explain why you you need to reset the serial number every day? I think you should reconsider mr_mist's suggestion of keeping seperate columns of an identity column and a datetime column. One (large) problem with home-grown key sequencing processes is the possibility of collisions from simultaneous inserts. The max(id) plus one thing can either cause deadlocking or unique constraint collisions depending on how you try to implement it.Also, can you please take the extra effort to spell out complete words in your posts? I can't tell you how difficult and annoying it is to read sentences full of abbreviations.Be One with the OptimizerTG |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-20 : 13:57:19
|
Hi.Below is a function that can be used as the default for the sequence number on your table. It will inspect the table for the highest sequence number for a given date.I must warn you though: This just presents a novel technique, and may not be the best approach - especially since it joins the table on itself on inserts. You may want to consider the previous posts regarding the requirement of sequence (yes - it seems nice to have the data sequenced this way, but is it really neccessary), and pay attention to the comments regarding collisions.Again: This is a novel way to accomplish what you requested. Consider an alternative approach.Cheers.Create Function DBO.fn_NextSeqByDate (@TheDate datetime) Returns intBeginDeclare @SeqNo intSelect @SeqNo = Max(SeqNo) From MyTable Where Datepart(yyyy,Entry_Date) + Datepart(mm, Entry_Date) + Datepart(dd, Entry_Date) = Datepart(yyyy,@TheDate) + Datepart(mm, @TheDate) + Datepart(dd, @TheDate)Set @SeqNo = IsNULL(@SeqNo,0) + 1Return @SeqNoEndGOCreate Table MyTable (Entry_Date datetime not null default getdate() , SeqNo int not null default DBO.fn_NextSeqByDate(getdate()) , mydata varchar(256) )ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (Entry_Date, SeqNo)Set NoCount ONInsert Into Mytable (mydata) values ('Row1')Insert Into Mytable (mydata) values ('Row2')Insert Into Mytable (mydata) values ('Row3')Insert Into Mytable (mydata) values ('Row4')Insert Into Mytable (mydata) values ('Row5')Select * From MyTable |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-20 : 14:04:39
|
OOps - Change the date comparision to: Convert (Char(4),Datepart(yyyy,Entry_Date))+ Right('0' + Convert(VarChar(2),Datepart(mm, Entry_Date)),2)+ Right('0' + Convert( Char(2),Datepart(dd, Entry_Date)),2)= convert (Char(4),Datepart(yyyy,@TheDate))+ Right('0' + Convert(VarChar(2),Datepart(mm, @TheDate)),2)+ Right('0' + Convert( Char(2),Datepart(dd, @TheDate)),2) |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-20 : 14:07:19
|
| And: There is a better way to do the date comparisons. (Subtract the components you don't need.)Too late now to show you - gotta go. Sorry.Cheers. |
|
|
|
|
Last Record Information!!!