Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lindsaykesler
Starting Member
5 Posts |
 Posted - 2010-10-13 : 15:29:32
|
| Hello, I have a script to get the next available Id number starting from a predetermined number (ex. 101) and I now need to exclude the number 666. Here is my script...If (Select Min(ServingNum) From dbo.Student Where ServingNum > (101 - 1)) = 101 Select IsNull(Min (aa.ServingNum + 1), 101) As Id From dbo.Student As aa Left Join dbo.Student As bb On (aa.ServingNum + 1) = bb.ServingNum Where bb.ServingNum Is null And aa.ServingNum + 1 >= 101 Else Select 101 As IdI tried adding 'And (aa.ServingNum + 1) <> 666' but if Id's 666-670 and 680-700 are all available it would skip over the entire 666-670 section and return 680.Thanks for any help!Lindsay |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-10-14 : 01:49:23
|
what's so bad about 666 anyway. it comes right after 665. if I had ticket 666 and you skipped me I'd be mad.
elsasoft.org |
 |
|
|
lindsaykesler
Starting Member
5 Posts |
Posted - 2010-10-14 : 09:23:37
|
| Brett - I'm trying to "get the next available Id number starting from a predetermined number (ex. 101) and I now need to exclude the number 666." Sorry, I thought that explaination was pretty clear but I will break it down. I have a Student table and that table contains a ServingNum (int) column and that SertingNum is auto-generated. The user can select a starting number for the ServingNum, in my example the starting number is 101. So the first student created is assigned ServingNum 101, the second 102, the third 103 and so on... My issue is that when students are deleted we would like to reuse their ServingNum so we can't just increment from the greatest ServingNum used. My script was working great for this until our customers requested that we don't assign a student a ServingNum of 666. So now I'm am looking for any suggestions on how to alter my script, or a new one, so that the number 666 is never returned. Thank you,Lindsay |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
lindsaykesler
Starting Member
5 Posts |
Posted - 2010-10-14 : 10:30:53
|
| I guess I misspoke, we are not really reassigning ID's, the StudentNum is not the primary Id, it is just a number assigned to a student. The Student table also has an "Id" field which is the primary id as well as an IdSeed field, which is an index, since they can choose what to set as the "Id". Your rephrase sounds correct and so far we have not had any complaints about 13 or prime numbers.Thanks,Lindsay |
|
|
|
|
|
|
|
|
Get next available Id number, excluding 666