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

Mysql – How can I query no two-column period overlaps?

I’m studying Employees Sample Database which has the following table.

create table dept_emp
(
    emp_no    int     not null,
    dept_no   char(4) not null,
    from_date date    not null,
    to_date   date    not null,
    primary key (emp_no, dept_no),
    constraint dept_emp_ibfk_1
        foreign key (emp_no) references employees (emp_no)
            on delete cascade,
    constraint dept_emp_ibfk_2
        foreign key (dept_no) references departments (dept_no)
            on delete cascade
);

create index dept_no
    on dept_emp (dept_no)

And two views are defined which imply that from_date and to_date are not overlaps.

create definer = root@localhost view current_dept_emp as
select `employees`.`l`.`emp_no`    AS `emp_no`,
       `d`.`dept_no`               AS `dept_no`,
       `employees`.`l`.`from_date` AS `from_date`,
       `employees`.`l`.`to_date`   AS `to_date`
from (`employees`.`dept_emp` `d` join `employees`.`dept_emp_latest_date` `l`
      on (((`d`.`emp_no` = `employees`.`l`.`emp_no`) and (`d`.`from_date` = `employees`.`l`.`from_date`) and
           (`employees`.`l`.`to_date` = `d`.`to_date`))));

create definer = root@localhost view dept_emp_latest_date as
select `employees`.`dept_emp`.`emp_no`         AS `emp_no`,
       max(`employees`.`dept_emp`.`from_date`) AS `from_date`,
       max(`employees`.`dept_emp`.`to_date`)   AS `to_date`
from `employees`.`dept_emp`
group by `employees`.`dept_emp`.`emp_no`;

Here comes a query and a result.

select *
from dept_emp
where emp_no = 499018
;

emp_no dept_no  from_date    to_date
------------------------------------
499018    d004 1996-03-25 9999-01-01
499018    d009 1994-08-28 1996-03-25

How can I query the dept_emp table so that I can confirm, at least in database, and implicitly, that

  • at each given emp_no
  • no period(from_date and to_date) doesn’t overlaps
Tags:

2

Answers


  1. 0

    To find overlaps, use the lag window function:

    select emp_no, from_date, to_date, previous_to_date
from (
    select emp_no, from_date, to_date, lag(to_date) over (partition by emp_no order by from_date) previous_to_date
    from dept_emp
) emps
where from_date < previous_to_date

    (or <= instead of < if the previous to_date being equal to the from_date is considered an overlap)

    Login or Signup to reply.
  2. 0

    Use an EXISTS clause to see whether there is an overlapping row for some row.

    We want to check whether for some row there exists another row for the same employee with an overlapping range. We detect that it is another row, by looking at the department, as
    the combination of employee ID and department ID is unique in the table. In other words: an employee cannot switch to and fro.

    select *
from dept_emp
where exists
(
  select null
  from dept_emp other
  where other.emp_no = dept_emp.emp_no
  and other.dept_no <> dept_emp.dept_no
  and other.from_date < dept_emp.to_date
  and other.to_date > dept_emp.from_date
;

    If you consider your example overlapping (i.e. there is no switch day where the employee is in both departments, but it must be different days), then you must change < and > to <= and >= in this query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search