I have the following table called vacations, where the employee id is displayed along with the start and end date of their vacations:

What I am looking for is to visualize the number of vacation days that each employee had, but separating them by month and without non-working days (Saturdays and Sundays).

For example, if you wanted to view the vacations for employee 1001, the following result should be displayed:

I have the following query that I have worked with:

SELECT id_employee,
       EXTRACT(YEAR_MONTH FROM t.Date) as YearMonth,
       COUNT(1)                           as Days
       FROM (SELECT v.id_employee,
                    DATE_ADD(v.start, interval s.seq - 1 DAY) AS Date
                    FROM vacations v
                    CROSS JOIN seq_1_to_100 s
                       WHERE  DATE_ADD(v.start, interval s.seq - 1 DAY) <= v.end
                    ORDER BY v.id_employee, , v.start, s.seq
        ) t
        GROUP BY id_employee,
                 EXTRACT(YEAR_MONTH FROM t.Date)

With this query I separate the days between a range of two dates with their respective month, but how could I adapt it to stop considering Saturdays and Sundays? I'm working with MySQL 5.7 in phpMyAdmin

CodePudding user response：

instead of count sum the compaarison of weekday function, which give what day it is .

But you should always save fates n a valid mysql manner 2021-10-28

SELECT id_employee,
       EXTRACT(YEAR_MONTH FROM t.Date) as YearMonth,
       SUM(WEEKDAY(`Date`) < 5)                            as Days
       FROM (SELECT v.id_employee,
                    DATE_ADD(v.start, interval s.seq - 1 DAY) AS Date
                    FROM vacations v
                    CROSS JOIN seq_1_to_100 s
                       WHERE  DATE_ADD(v.start, interval s.seq - 1 DAY) <= v.end
                    ORDER BY v.id_employee, v.start, s.seq
        ) t
        GROUP BY id_employee,
                 EXTRACT(YEAR_MONTH FROM t.Date)

CodePudding user response：

Cant comment but in response to:

Your query works fine when it's a single row per employee, but when some employee >takes separate vacactions (5 days at the start of the month and other 5 days at the >end of the same month, for example), you need to insert more than one row for the >same employee and that's when your query fails. How can I fix this?

You could add a second column as in "vacation1", "vacation2" and up. Sum the vacation columns up for the total amount of vacation days. Then it is also possible to count the number of vacations one has taken in one month. More data points is always better.

To the original question, create your own date/time dataset without these days then link it up. But again it is not good to remove, it is better to add.