1

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

employee start end
1001 26/10/21 22/11/21

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:

days month
4 10
16 11

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

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Andrea Duran
  • 180
  • 8
  • [MySQL function to find the number of working days between two dates](https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates) – Larbi Marref Oct 28 '21 at 18:35

1 Answers1

0

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)
nbk
  • 45,398
  • 8
  • 30
  • 47
  • 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? – Andrea Duran Oct 28 '21 at 18:57
  • for that you must present a dbfiddle with the problem – nbk Oct 29 '21 at 08:56
  • https://www.db-fiddle.com/f/7To9yjc4W3w6t7EMj2izui/2 – Andrea Duran Oct 29 '21 at 21:45
  • 1
    no inserting the row for every vacation as separate row is a normal procure, otherwise you can show the separate vacation and you can't plant for someone to take over as you can't see when the vacation is, so you need two rows https://www.db-fiddle.com/f/7To9yjc4W3w6t7EMj2izui/3 – nbk Oct 29 '21 at 22:21