CODEWITHSUNDEEP
sqlmysqljoinself-join
Jin Kwon
Jin Kwon

Reputation: 22027

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

Upvotes: 0

Views: 60

Answers (3)

marcothesane
marcothesane

Reputation: 6749

MySQL now supports OLAP functions, among which we can find the LEAD() function, and this one can help us here in a pretty efficient way:

https://www.geeksforgeeks.org/mysql-lead-and-lag-function/

I added a few more data - and one row overlaps its successor in time; I marked it by calling the department id 'over' .

I had to nest two queries, as you can't use an OLAP function in a WHERE condition.

WITH
-- creating a set of data
-- the row with department 'over' OVERLAPS
employees (emp_no,dept_no,from_date,to_date) AS (
          SELECT 499001,'d001',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499002,'d002',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499003,'d003',DATE '2024-02-02',DATE '9999-12-31'
UNION ALL SELECT 499004,'d004',DATE '2024-02-02',DATE '9999-12-31'
UNION ALL SELECT 499005,'d005',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499006,'d006',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499007,'d000',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499008,'d001',DATE '2024-02-02',DATE '9999-12-31'
UNION ALL SELECT 499009,'d002',DATE '2024-02-02',DATE '9999-12-31'
UNION ALL SELECT 499010,'d003',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499010,'d003',DATE '2024-02-02',DATE '2024-03-02'
UNION ALL SELECT 499010,'d003',DATE '2024-01-02',DATE '2024-02-02'
UNION ALL SELECT 499010,'d003',DATE '2023-12-02',DATE '2024-01-02'
UNION ALL SELECT 499010,'d003',DATE '2023-11-02',DATE '2023-12-02'
UNION ALL SELECT 499011,'d004',DATE '2024-03-02',DATE '9999-12-31'
UNION ALL SELECT 499018,'over',DATE '1992-08-28',DATE '1994-08-31'
UNION ALL SELECT 499018,'d009',DATE '1994-08-28',DATE '1996-03-25'
UNION ALL SELECT 499018,'d004',DATE '1996-03-25',DATE '9999-01-01'
)
-- end of input data,real query starts here
-- replace following comma with "WITH"                                         
,
w_next_dt AS (
  SELECT
    *
  , LEAD(from_date) OVER(PARTITION BY emp_no ORDER BY from_date) AS next_from_d
  FROM employees
)
SELECT
  *
FROM w_next_dt
WHERE to_date > next_from_dt;
emp_no dept_no from_date to_date next_from_dt
499018 over 1992-08-28 1994-08-31 1994-08-28

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

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.

Upvotes: 1

ysth
ysth

Reputation: 98398

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)

Upvotes: 2

Related Questions