A creak within a range

Question

I have a problem and I’m looking for the best way to solve it.

I have values of Min and Max coming from a database: Ex:

 id      Min    Max
 1        5     10
 2       20     25
 3       60     90 
 4       91     99

If you are going to insert a new Min and Max it should not overlap over a range that has already been set ex: from 11-19 is allowed, from 24-50 not pq that would enter the range of 20-25.

Any idea how to solve something like this?

Answer 1

We will divide your problem into parts:

1. Avoid entering a value if any conditions occur
2. Identify existence of intersection in real number ranges

So we can independently treat each point independently.

Conditional insertion

A conditioned insert is a insert with a conditional clause; I’ve seen insert-select being used for this purpose, conditioning the insertion of the clause where, join or similar things from select or a CTE. Removing these alternatives, I know of no other way to make a conditioned insertion using only DML. As you did not specify SGBD, I will not use here any trigger or procedure for this insertion.

An insertion with conditional is similar to the insertion with values but, instead of specifying a tuple with values to be inserted, the return of a query will be inserted.

For example, imagine a report that depends on very heavy consultation. To not run the query multiple times, store your result in a table:

INSERT INTO relatorio_bacana (mes_inicio, mes_fim, total)
SELECT
    '03' AS mes_inicio,
    '04' AS mes_fim,
    sum(d.vr) AS total
FROM
    dados d
WHERE
    '03' <= d.mes AND
    d.mes < '04'

Roughly speaking, this is an insertion with selection. Note that a condition has been placed in the selection: the `mes column needs to be between the start and end months. Note that it is possible to decrease the verbosity of inserted constant terms: we do not need to write twice the start month, nor twice the end month; we can write each only once, using a CTE.

WITH q AS (
    SELECT
        '03' AS mes_inicio,
        '04' AS mes_fim
)
INSERT INTO relatorio_bacana (mes_inicio, mes_fim, total)
SELECT
    q.mes_inicio AS mes_inicio,
    q.mes_fim AS mes_fim,
    sum(d.vr) AS total
FROM
    dados d, q
WHERE
    q.mes_inicio <= d.mes AND
    d.mes < q.mes_fim

Intersection detection between intervals

There are 7 possible relationships between two intervals:

1. Identical:

   <--->
   <--->
   

2. The first contains the second in its own way:

   <----------->
        <----->
   

3. The first is contained in the second in its own way:

         <--->
   <---------->
   

4. Intersection at the beginning of the first:

         <----->
   <------->
   

5. Intersection at the end of the first:

   <------>
         <------->
   

6. No intersection, strictly minor:

    <--->
                  <----->
   

7. No intersection, strictly larger:

                     <--->
   <--->
   

As we are working with closed intervals (this was my understanding of the question), it is enough that one of the ends coincides so that there is case of relevance (smaller with smaller/larger with greater) or partial intersection (smaller with greater/greater with lesser). If you mixed the opening of the extremities, then you would need more care.

Be it m the smallest of the range and M the greater of the range. Let m1 and M1 relating to the first interval, and similarly m2 and M2 to the second.

If m1 is in (m2,M2) then we have a case of self-importance (3) or intersection at the beginning of the interval (4). Since we don’t care what kind of relationship there is between the two intervals, that’s enough.

If m2 is in (m1,M1) then we have a case of own relevance (2) or intersection at the end of the interval (5).

Consideration with closed intervals ensures extreme cases of intersections and equal intervals. Therefore, to have some form of intersection (cases 1 to 5 of the 7 possible relationships), the following relationship needs to be satisfied:

/* m1 dentro de [m2,M2] para casos 1,3,4 */
(m2 <= m1 E m1 <= M2)

    OU

/* m2 dentro de [m1,M1] para casos 1,2,5 */
(m1 <= m2 E m2 <= M1)

In sql, if the table is called a table:

SELECT
    t1.id,
    t2.id
FROM
    tabela t1,
    tabela t2
WHERE
    (t2.min <= t1.min AND t1.min <= t2.max)
         OR
    (t1.min <= t2.min AND t2.min <= t1.max)

Thus, for each intersection in the table tabela, the ids of the lines that generated that intersection shall be printed.

Putting it all together

We want to insert a new line only if it has no intersection with a previously existing one (considering the auto-generated id):

WITH novos_dados AS (
    SELECT
        11 AS min,
        19 AS max
)
INSERT INTO tabela (min, max)
SELECT
     n.min,
     n.max
 FROM
     novos_dados n
 WHERE NOT EXISTS (
      SELECT
          1
      FROM
           tabela t
      WHERE
          (t.min <= n.min AND n.min <= t.max)
               OR
          (n.min <= t.min AND t.min <= n.max)
 )