Solved

Select first unused number in a range between 00 and 99

Posted on 2004-08-06
26
2,238 Views
Last Modified: 2012-05-05
Hi

I have a table called Operators, for some obscure reason that defies logic, a numeric field called OperatorID of type Long Integer has been throttled by a business policy, stating that "there shall never be more than 100 operators" hmmmm.

The throttling is in the form of an input mask '00' and a format of '00' being applied at table level indexed(no duplicates).

The Problem is this:
I need to assign a new OperatorID, now operatorID's 01, 02 and 03 have been assigned, as have 99, 98, 97.

I need some SQL script that will return the first available number in the range 1 - 100 that has not been consumed.

Hope that is clear enough for you, if not, let me know, and I will attempt to clarify.

Dont really want to use a function(builtin or custom) for this if at all possible, want it in SQL script please.



Regards Alan
0
Comment
Question by:Alan Warren
  • 8
  • 6
  • 6
  • +2
26 Comments
 
LVL 34

Expert Comment

by:flavo
Comment Utility
Only SQL, i dont think so.

Public function getID() as integer

Dim db as dao.database
Dim rs as dao.recordset
Dim lLast as long

Set db = currentdb
Set rs = db.openrecordset("Select OperatorID from Operators")

rs.movefirst
lLast = rs(0).Value
rs.movenext

While not rs.EOF
   If rs(0).value - 1 <> lLast then
        getID = lLast + 1
   End if
 lLasr = rs(0).value
 rs.movenext

Wend

End function

You can use it in SQL like

INSERT INTO operators ( operatorID, otherfield ) VALUES " & getID " & 'myValue'"

Dave


0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
should be

"INSERT INTO operators ( operatorID, otherfield ) VALUES ( " & getID " & 'myValue' )" (in VB)

or in SQL builder

INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
Do you have your range in a table? This may sound clunky but I would use a table that contains the whole range of operator IDs as well as the operator name, then you can easily build an SQL that finds the first unused ID.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I was thinking along the lines of having a table holding values 1 to 100

then u can do something like this

select top 1 num from lookupnumberstable where num not in (select operatorid from operators) order by num

0
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
Hi Dave :)
how ya going mate?

This has to be do-able, without temp tables and without a vba function.
never say never.

Using a temp table that contains the range we could:

  Select Top 1 NumField from tblRange where Numfield Not In(Select OperatorID From Operators) From tblRange

Was hoping not to create temp table, MDE.

Alan
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
damn, too slow, all these posts been added, should of refreshed!
anyway looks like Will has the same idea as me but has beat me to it


Morning Dave,
0
 
LVL 8

Accepted Solution

by:
arcross earned 500 total points
Comment Utility
why not to create a query in your table Operators adding a counter agaisnt

counter    OperatorID
-------------------------
1                 1
2                 2
3                 4 -------->> Here you wouls see the diff
4                 5
.....................

Then select top as rocki mentioned. All this you could do in the same query or using 2 queries.

Álvaro
0
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
Think I got it guys!


SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators
WHERE ((([OperatorID]+1) Not In (select OperatorID From Operators) And ([OperatorID]+1)<100))
ORDER BY [OperatorID]+1;


Thanks anyway.
0
 
LVL 8

Expert Comment

by:arcross
Comment Utility
wow, it works. Ive tried myself.

Very very good alan :)
0
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
Álvaro,

I read your post after I posted my "I got it guys" post.
Exactly what I did, good one friend, thank you.

Alan
0
 
LVL 8

Expert Comment

by:arcross
Comment Utility
hey thanks for the points Alan :)

but you did it !!

again, very good !

Álvaro
0
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
Your logic was good Álvaro.

Instead of selecting what was there, select what wasn't there; by adding one and filtering unmatched on what was there.

good one
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
Alan,

Long time no see mate.  How's things going down in  Adelaide.  You been busy with work??

Rocki,

Ill be going for an adventure to the UK for about 6 months {i here you Europeans have transport problems too} next February, ill have to meet up with you somewhere and buy you that Kronie.

Keep up the good work guys, i guess my "love" of VB gets the better of me...

Dave.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Author Comment

by:Alan Warren
Comment Utility

Hi Dave,
things is pretty good here, eeking out a megre existance contracting, sick of this long winter.
Hows them traffic control algorithm's going, can you arrange me a clear run through Brisbane in late September?
Got a strong affinity with VBA too, but I always prefer to solve it with SQL if possible, its easilly preserved as serverside procedure. To be honest mate, it ended up being an ADO/SQL/VBA function anyway.

Function mGetNextOperatorID() As Long

  ' aw 6-aug-2004
  '=============================================================================
  ' Function to generate new OperatorID
  '=============================================================================
 
  Dim lngNextOperatorNumMax As Long
  Dim sql As String
  Dim sWhere As String
  Dim sOrderBy As String
  Dim rs As ADODB.Recordset
 
  Set rs = New ADODB.Recordset
 
  sql = "SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators"
  sWhere = " WHERE ((([OperatorID]+1) Not In (SELECT OperatorID FROM Operators) And ([OperatorID]+1)<100))"
  sOrderBy = " ORDER BY [OperatorID]+1"
  sql = sql & sWhere & sOrderBy
  rs.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  With rs
    If Not .BOF And Not .EOF Then
      lngNextOperatorNumMax = .Fields("NextID")
    Else
      lngNextOperatorNumMax = 0
    End If
    .Close
  End With
  Set rs = Nothing
 
  mGetNextOperatorID = Format(lngNextOperatorNumMax, "00")  '  Format is redundant
 
End Function


Take care mate, stay in touch, maybe catch you when Im in town.

Alan


0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
I only just realised that it was you how asked the Q.  I would have said hello earlier.

I'm still stick with A97 at work, hence the DAO, plus i wanted to get my train go home, and didnt really want to play with SQL.

Clean run in Brissie, good luck, unless you are driving at 2am and you're lucky.

Take care buddy,

Dave
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Dave, transport problems magnified in the UK, especially these damn trains! Im fed up with commuting!

feb's gonna be cold, good idea to meet up,
been damn hot last two days, yesterday I spent all day lazing on the grass, at the cricket ground with bbq and loads of beer, no krony but stella was there. I was totally shagged by the end of the day, heat and beer!
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
>> transport problems magnified in the UK

Glad to hear (for my sake)


>> feb's gonna be cold

Yeah i know, and it sickens me.

>> at the cricket ground with bbq and loads of beer

You an Aussie mate, that's my perfect summer Sunday.

>> heat and beer

It kills everyone, but they go thogether soooo well.

My folks are moving over there soon (just sold their house over here) so im going to bum with them for a while, cheap accom, cant complian with that.

Catch you both later.

Dave
0
 
LVL 8

Expert Comment

by:arcross
Comment Utility
>> feb's gonna be cold

and august too. I havent seen a bloody sunny day this summer yet.  
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
It sounds like its cold 24/7/365.  Does it get over 30C??  I used to live in Townsvile (north Qld - Australia) and 35 was "cool" for summer.  30C wasn't unusual for "winter".
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
its been damn hot last weekend, temp about 26 to 28C
now its just chucking it down, so much rain, but still warm

0
 
LVL 8

Expert Comment

by:arcross
Comment Utility
Dave, sorry for dissapoint you, but 30C!!! you are joking !! I've been over 2 years here and maybe its gone over that, once or twice. I used to live in Alicante (Spain) and i think they are having 35 average.

Now, im getting used to it.
0
 
LVL 8

Expert Comment

by:arcross
Comment Utility
Well, true, last weekend was good maybe best of the year, nearly 30C!
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Dave, warmest months August-September (mid)

Its hot enough to merit putting on the AC in the car, otherwise your messed!

Occasionally July and June, even May
Decent weather in April, though it has snowed once, that was a freak
starts getting cold October, gets better after feb

from my experience, south of England is generally a little warmer than the north. But then maybe it depends on whereabouts you are.

Bring your winter woolies, not the aussie ones, t-shirt and shorts but the british ones
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
>> 26 to 28C

Hot?? my @$%#%@!!!!

That's cold dude!!!

Its winter and gets up to 25+ most days during the day ( below 5 is really cold at night, and thats cold......brrrrrr.... - considering no-one has heating or anytihng, only the "rich" have air-con, and thats only used during summer, even if it is reverse cycle {i havent used mine at all since Feb - summer} ) (({{}{{{{{{[[[{ cheese }]]]}}}}}{}]))
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
thats why the assuie cricket team only come in the english summer, cant stand the cold
now play the english in cold conditions, perhaps the english may win the ashes for once!
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
>> for once!

...... good luck, they'll need more than that!

Im off to bed, tooo many beers ( public holiday tomorrow, its after 11pm ).  Had a few too many brews at a mates house after work.

Catch you all later.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now