Question 63828 in PhpMyAdmin Question posted in
The official documentation can be found here.

How to display the name of the employees along with the date they were absent? – Phpmyadmin

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; Mary was absent on day 3th and George 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.

Tags:

4

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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
    Login or Signup to reply.
  3. 0

    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
)
    Login or Signup to reply.
  4. 0

    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:

    select v.num_employee, v.name, v.date as missing
from (select * from (select distinct `date` from records) u cross join employees) 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.

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

Back To Top
Search