Get first possible number that is not used in a range

Question

I have two tables defined:

TABNUMBERRANGES which defines a from-to definition of numbers which determine if a new organisation can be created that belongs to that number range ( its ID is within a given from-to record for that ngesnr), example

nFrom | nto | ngesnr
------+-----+-----
  1   |  2  |   1
  3   |  4  |   2
  5   |  7  |   1

TABGESDEF which is a definition of organisations and what ges they belong to

ID | ngesnr
---+-------
 1 |   1
 2 |   1
 3 |   2
 4 |   2
 5 |   1
 7 |   1

Now what I want to achieve is to get the smallest possible number available to use for TABGESDEF so that it aligns with the allowed ranges of TABNUMBERRANGES or to say that no space is available and a new range has to be created in TABNUMBERRANGES, when creating a TABGESDEF record I know what gesdef I will assign it to, now what I need help with is going through TABNUMBERRANGES for that ngesnr to get a available number, is there any way I can do this using a SQL query.

In the given example I want to create a new TABGESDEF for ngesnr 1 and I should get 6 as my ID as its the next possible number due to the record 5-7 in TABNUMBERRANGES, if all number ranges are occupied I would simply return 0 and handle it on the front end by displaying to the user that a new number range has to be created to expand records.

I'm trying to see if there is a way to so this directly on the database and then just forward the query result to the front to avoid creating lists and looping on the frontend solution

The query should for my solution go through 1,2,5,6,7 as they are allowed for gesnr 1 and tell me if any of those numbers are not used in IDs for TABGESDEF, after checking 5 it should say 6 is the next available one.

My solution on the frontend would be to select all number range records into a list of models from query results and add select min and max

List<int> usedNumbers = new List<int> (1,2,5)
RangeModel model1 = new RangeModel() {
    Min = 1,
    Max = 2
}

RangeModel model2 = new RangeModel() {
    Min = 5,
}
Max = 7

ListModels.add(model1)
ListModels.add(Model2)
Int freeNr = 0;

Foreach ( model in ListModels) {
  For(int i = model.min;i <= model.max ;i++) {
  If(usedNumbers.contains(i) {
    FreeNr = 0;
  }
  Else {
    FreeNr = i;
  {
Return FreeNr;

But I'm wondering if I could achieve this better directly in database.

Note that for simplicity tabgesdef is ordered, this does not have to be the case as a user can define the id themselves, the solution should only take the next id automatically if defined so, the ids do not have to be in order from smallest to largest

Answer 1

I have opted to resolve this on the server side rather than on DB so here's how i achieved this for anyone wondering

public int GetNextFreeIDforGesInRange(string nGesNr)
{
    int nReturnNr = 0;
    List<int> tabGesDefEntries = new List<int>();
    List<GesellschaftRangeModel> gesRanges = new List<GesellschaftRangeModel>();
    const string QUERY = "select nGesNr from TABGesDef order by nGesNr";
    using (OracleCommand cmd = new OracleCommand(QUERY, PoolObj))
    {

        using (OracleDatareader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                tabGesDefEntries.Add(Convert.ToInt32(reader["nGesNr"]));
            }
        }
    }

    const string QUERY2 = "select nNrVon,nNrBis,nGesNr from TABNrKreise where szTabName = 'M2TABGesDef' and nGesNr =:iNgesNr";
    using (OracleCommand cmd = new OracleCommand(QUERY2, PoolObj))
    {
        cmd.BindByName = true;
        cmd.Parameters.Add("iNgesNr", nGesNr);
        using (OracleDatareader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                GesellschaftRangeModel gesellschaftRange = new GesellschaftRangeModel();
                gesellschaftRange.nGesNr = Convert.ToInt32(reader["nGesNr"]);
                gesellschaftRange.nNrVon = Convert.ToInt32(reader["nNrVon"]);
                gesellschaftRange.nNrBis = Convert.ToInt32(reader["nNrBis"]);
                gesRanges.Add(gesellschaftRange);
            }
        }
    }
    foreach (var gesrange in gesRanges)
    {
        for(int i = gesrange.nNrVon; i <= gesrange.nNrBis; i++)
        {
            if(tabGesDefEntries.Contains(i))
            {
                nReturnNr = 0;
            }
            else
            {
                nReturnNr = i;
                return nReturnNr;
            }
        }
    }
    return nReturnNr;
}

this way i either return a free number i hit or i send 0 to display error message to inform user to create a new number range.