I have the calendar table with all the dates of the month of December 2021 (I will only exemplify some dates within the table, but it is understood that it actually contains all the days of said month):

I have the users table:

I have the table assistances

I have a query to display the employee number, their name, the days they attended and the days they were absent:

SELECT u.num_employee,
       u.name,
       a.date AS attendances,
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),a.date)) as faults FROM users u
JOIN (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.num_employee = u.num_employee

With the above query, I get this:

Now, the point is that in addition to the attendances table, I have to consider another table called vacations. The structure of this table is as follows:

And taking this table into consideration, the days within the ranges that are handled as vacations should stop appearing in the "faults" column. The result should be the following:

How can I adapt my query to get the above?

The query in question cannot be adapted to use CTE given the version of MariaDB I am using. I am working on phpMyAdmin.

CodePudding user response：

As MySQL and MariaDB are bad with sequences, it is good that you have a calendar table to run against.

So another subquery that retrieves the dates of the vacation is needed.

I used a GROUP BY in the subselect as there could be more than 1 vacation period in a month.

SELECT u.num_employee,
       u.name,
       a.date AS attendances,
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),a.date)
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),vac.vac_days)) as faults 
FROM users u
LEFT JOIN  (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.num_employee = u.num_employee
LEFT JOIN (SELECT v.`num_employee`, GROUP_CONCAT(DAY(c.`date`)) vac_days
FROM vacations v INNER JOIN calendar c ON c.`date` BETWEEN v.`initial_date` AND `final_date`
AND c. date BETWEEN '2021-12-01' AND '2021-12-31'
GROUP BY v.`num_employee`) vac ON vac.`num_employee` = u.num_employee

num_employee | name   | attendances | faults     
-----------: | :----- | :---------- | :----------
         101 | Andrew | 3,4,5       | 1,2,6,10,11
         102 | Mary   | 3,4,6       | 1,2,5,10,11

db<>fiddle here