I have to find the names of the employees who have never worked for two consecutive days in the same-CodePudding

I have to create a code, in sql, that allows me to find the id of the employees who haven't worked for two days in a row My data has this structure:

id_emp	date	id_center
0001	2020-12-11	0045
0001	2020-12-12	0045
0001	2020-12-13	0045
0002	2020-12-11	0047
0002	2020-12-13	0047
0003	2020-12-11	0043
0003	2020-12-12	0043
...	...	...

The table to be returned must be structured like this:

id_emp
0002

CodePudding user response：

Test this:

WITH cte AS (
    SELECT id_emp, DATEDIFF(date, LAG(date) OVER (PARTITION BY id_emp, id_center ORDER BY date)) = 1 consecutive
    FROM src_table
),
SELECT id_emp
FROM cte
GROUP BY id_emp
HAVING NOT SUM(consecutive)

CodePudding user response：

select distinct id_emp
from mystery_table_name
where id_emp not in (
    select distinct id_emp
    from mystery_table_name a
    join mystery_table_name b on b.id_emp=a.id_emp and b.id_center=a.id_center and b.date=a.date   interval 1 day
)

Though looking for employees in your table of who worked when and where is strange, and will leave out employees who have never worked. Surely you have an employee table.

If on mysql 8 or mariadb 10.2 or better, Akina's answer may be more performant, especially if you do not have an (id_emp,id_center,date) index.