Question 176954 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL extract name values from records with overlapping dates?

I have a table containing areas whose size changes over time. The timeframe (in years) for which an area size is valid is stored in the columns "VALID_FROM" and "VALID_UNTIL". The timeframes for one area must not overlap. I want to create a query with which I can check if someone has entered overlapping timeframes.

In the example table the validity timeframes from area1 do overlap.

Table "areas":

NAME SIZE VALID_FROM VALID_UNTIL
area1 55 1990 2005
area1 40 2000 2009
area1 45 2010 2099
area2 79 1990 2099
area3 33 1990 1999
area3 37 2000 2009

Note that end dates are inclusive e.g. there is no gap in the two dates for area3.

It would be sufficient to have a result like this:

NAME REMARK
area1 ‘timeframes overlap!’

My problem is that an area may occure n times in the table, I dont know how to compare the "VALID_FROM" and "VALID_UNTIL" columns if there could be an arbitrary number of rows for one area.

So far I got as far as reducing the table to those rows which occur more than once.

WITH duplicats AS (
    SELECT `NAME`
    FROM `areas`
    GROUP BY `NAME`
    HAVING COUNT(`NAME`) > 1
)
SELECT * 
FROM       `areas`
INNER JOIN `duplicats`
        ON `areas`.`NAME` = `duplicats`.`NAME`
Tags:

4

Answers


  1. 0

    You can do it by self join your tables :

    select s1.*
from mytable s1
inner join mytable s2 on (
    s1.VALID_FROM between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s1.VALID_UNTIL between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s2.VALID_FROM between s1.VALID_FROM and s1.VALID_UNTIL
) and s1.NAME = s2.NAME and s1.VALID_FROM <> s2.VALID_FROM and s1.VALID_UNTIL <> s2.VALID_UNTIL

    Result :

    NAME    SIZE    VALID_FROM  VALID_UNTIL
area1   40      2000        2009
area1   55      1990        2005

    Or if you want result grouped by area then :

    select s1.NAME, group_concat(DISTINCT concat(s1.VALID_FROM,'-', s1.VALID_UNTIL)) as REMARK
from mytable s1
inner join mytable s2 on (
    s1.VALID_FROM between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s1.VALID_UNTIL between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s2.VALID_FROM between s1.VALID_FROM and s1.VALID_UNTIL
) and s1.NAME = s2.NAME and s1.VALID_FROM <> s2.VALID_FROM and s1.VALID_UNTIL <> s2.VALID_UNTIL
group by s1.NAME

    Result :

    NAME    REMARK
area1   1990-2005,2000-2009

    Demo here

    Login or Signup to reply.
  2. 0

    To test for overlaps you would simply:

    select *
from t
where exists (
    select *
    from t as x
    where x.id <> t.id -- pri. key check is needed to avoid comparing row with itself
    and x.name = t.name
    and t.valid_until >= x.valid_from
    and t.valid_from  <= x.valid_until
)

    The two from-until date comparisons are sufficient to find all kind of overlaps. The answer assumes until date is inclusive.

    Login or Signup to reply.
  3. 0

    In MySQL 8.x, you could subtract the previous valid_until value from the valid_from value, if there is a negative difference then there is an overlap.

    select NAME, 'timeframes overlap!' AS Remark
from
(
  select *,
    VALID_FROM - lag(VALID_UNTIL) over (partition by NAME order by VALID_FROM) df
  from areas
) t
group by NAME
having sum(df <= 0) > 0

    demo

    Login or Signup to reply.
  4. 0

    JOIN a calendar table or cte. Do a GROUP BY and use HAVING to find years having two (or more) rows with the same area.

    with recursive years (y) as
    (select min(valid_from) from areas
     union all
     select y + 1 from years
     where y <= (select max(valid_until) from areas)
    )
select distinct a.name
from areas a
join years on y between valid_from and valid_until
group by a.name, y
having count(*) > 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search