How to display the name of the employees along with the date they were absent?-CodePudding

I have the following table called employees:

num_employee	name
001	George
002	Mary

And the table called records:

num_employee	date
001	2021-12-01
002	2021-12-01
001	2021-12-02
002	2021-12-01
001	2021-12-03
002	2021-12-06

In the example above, both employees attended on December 1st and 2nd; María was absent on day 3th and Jorge was absent on day 6th. Days 4th and 5th were weekends, so they are not considered absences since they are non-working days (working days are from Monday to Friday).

What I am looking for is to obtain a result in which the employee number, his name and the date on which he was absent are displayed:

num_employee	name	missing
001	George	2021-12-03
002	Mary	2021-12-06

For now, the only thing I have achieved is to display the absences of a single employee with the following query:

SELECT GROUP_CONCAT(date) as missing FROM
(SELECT ADDDATE('1970-01-01',t4.i*10000   t3.i*1000   t2.i*100   t1.i*10   t0.i) date FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE date BETWEEN '2021-12-01' AND '2021-12-31' AND date NOT IN (SELECT DATE_FORMAT(date,'%Y-%m-%d') FROM records WHERE num_employee = 001 AND date BETWEEN '2021 -12-01' AND '2022-12-31') AND DAYOFWEEK(date) BETWEEN 2 AND 5

And with that, I generate a table with all the dates, I specify a date range and I specify the employee number (in this case employee 001) and I get the following result:

missing
2021-12-06

How can I adapt my query to stop requiring filtering by employee and display them all with their respective dates when they were absent?

I am working with MariaDB in phpMyAdmin.

CodePudding user response：

If I did understand your question correctly, this will do the trick.

this will give you all the data joined.

select e.num_employee, e.name, r.date
from employees e
join records r on r.num_employee = e.num_employee

this will give you a distinct per employee with the last missing.

select e.num_employee, max(r.date) as last_missing
from employees e
join records r on r.num_employee = e.num_employee
group by employees.num_employee

CodePudding user response：

SELECT e.num_employee, e.name, max(r.date) AS missing

FROM records AS r

JOIN employees AS e ON e.num_employee = r.num_employee

GROUP BY e.num_employee

ORDER BY e.num_employee ASC

CodePudding user response：

Assuming you have a date table (which you should have), you can do the following:

select
    d.date
  , e.num_employee
  , e.name
from employees e
cross join date d
where 1=1
  and d.is_weekend = 0
  and d.date >= '2022-01-01'

This will give you all possible combinations of names and dates. Filter it, cause most date tables will hold dates till 1900 or 1990. Most of the time you will want to do that per year. Yes, cross join is not the best, but with those few records it won't be a problem.

Now you want to filter this, cause you only want the days where people were missing, i.e. where no entry exists.

select
    sub.num_employee
  , sub.name
  , sub.date as missing
from (
  select
      d.date
    , e.num_employee
    , e.name
  from employees e
  cross join date d
  where 1=1
    and d.is_weekend = 0
    and d.date >= '2022-01-01'
) sub
where not exists (
  select
    1
  from records r
  where 1=1
    and r.num_employee = sub.num_employee
    and r.date = sub.date
)

CodePudding user response：

Assuming the records table contains employee presences and not absences, and assuming there's always at least one employee present every working day, this should work:

with u as (select distinct `date` from records),
v as (select * from u cross join employees)
select v.num_employee, v.name, v.date as missing
from v left join records
on v.`date` = records.`date`
and v.num_employee = records.num_employee
where records.`date` is null;

Fiddle

Basically, you get all the distinct dates where at least one employee was present (from the records table), cross join it with the employees table to get a match between every employee and every working day, then outer join the result with the records table to check for absences.