CODEWITHSUNDEEP
sqlmariadb
Andrea Duran
Andrea Duran

Reputation: 190

How to count only the working days between two dates?

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

id_employe start end
1001 2020-12-24 2021-01-04

What I am looking for is to visualize the amount of vacation days that each employee had, but separating them by employee number, month, year and number of days; without taking into account non-business days (Saturdays, Sundays and holidays).

I have the following query, which manages to omit Saturday and Sunday from the posting:

SELECT id_employee, 
       EXTRACT(YEAR FROM t.Date) AS year, 
       EXTRACT(MONTH FROM t.Date) AS month, 
       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);

My question is, how could I in addition to skipping the weekends, also skip the holidays? I suppose that I should establish another table where the dates of those holidays are stored, but how could my * query * be adapted to perform the comparison?

If we consider that the employee 1001 took his vacations from 2020-12-24 to 2021-01-04 and we take Christmas and New Years as holidays, we should get the following result:

id_employee month year days
1001 12 2020 5
1001 1 2021 1

Upvotes: 1

Views: 472

Answers (1)

FanoFN
FanoFN

Reputation: 7114

After you have created a table that stores the holiday dates, then you probably can do something like this:

SELECT id_employee, 
       EXTRACT(YEAR FROM t.Date) AS year, 
       EXTRACT(MONTH FROM t.Date) AS month, 
       SUM(CASE WHEN h.holiday_date IS NULL THEN WEEKDAY(`Date`) < 5 END) 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 
LEFT JOIN holidays h ON t.date=h.holiday_date
GROUP BY id_employee, EXTRACT(YEAR_MONTH FROM t.Date);

Assuming that the holidays table structure would be something like this:

CREATE TABLE holidays (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
holiday_date DATE,
holiday_description VARCHAR(255));

Then LEFT JOIN it to your current query and change the SUM() slightly by adding CASE expression to check. If the ON t.date=h.holiday_date in the left join matches, there will be result of field h.holiday_date, otherwise it will be NULL, hence only the CASE h.holiday_date WHEN IS NULL .. will be considered.

Demo fiddle

Adding this solution compatible with both MariaDB and MySQL version that supports common table expression:

WITH RECURSIVE cte AS
(SELECT id_employee, start, start lvdt, end FROM vacations 
    UNION ALL
 SELECT id_employee, start, lvdt+INTERVAL 1 DAY, end FROM cte 
   WHERE lvdt+INTERVAL 1 DAY <=end)

SELECT  id_employee,
        YEAR(v.lvdt) AS year, 
        MONTH(v.lvdt) AS month,
        SUM(CASE WHEN h.holiday_date IS NULL THEN WEEKDAY(v.lvdt) < 5 END) AS days
  FROM cte v 
  LEFT JOIN holidays h
     ON v.lvdt=h.holiday_date
  GROUP BY id_employee,
        YEAR(v.lvdt), 
        MONTH(v.lvdt);

Upvotes: 1

Related Questions