Search code examples
mysqldatetimesummarizeholidays-gem

How to summarize leave to without public holidays?

Hi all (before holidays).

In this case I have added new leave in this table:

+--------+---------+---------+-------------+----------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME   | LNAME | BEGIN_DATE         | END_DATE            | 
+--------+---------+---------+---------+-------------+--------------------+------
| 8      |   10    | MARIO   | NEED  |2019-04-22 07:00:00 |2019-04-23 15:00:00  | 
+--------+---------+---------+-------------+----------+--------------------------

But I know the holidays are representing like this:

enter image description here

What I have done yet?

I have done that query which summarize leave time of all employees grouped by ID_LEAVE

SELECT leave.ID_LEAVE, leave.ID_WORKER, workers.FNAME, workers.LNAME, leave.BEGIN_DATE, leave.END_DATE,  
FROM 
(SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value 
 FROM 
(SELECT 0 t0 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 t1 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 t2 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 t3 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 t4 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) calendar 
INNER JOIN leave ON calendar.date_value BETWEEN DATE(leave.BEGIN_DATE) AND DATE(leave.END_DATE) 
INNER JOIN workers ON leave.ID_WORKER = workers.ID_WORKER 
WHERE NOT WEEKDAY(date_value) IN (5, 6)
GROUP BY ID_LEAVE;

Now I'd like that query which summarize leave time of all employees but without holidays which I represented in above picture. What should I do? Should I create new "holidays" table and then download that date from this table or add dates in above query?

Solution

  • Ok after analysing all comments and answer i've found that solution:

    1) I created holidays table.

    2) I've created that query which selects all and summarize leave_time without weekends and holidays:

    SELECT *, 
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIME(leave.END_DATE), TIME(leave.BEGIN_DATE))))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM 
(SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value FROM 
(SELECT 0 t0 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 t1 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 t2 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 t3 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 t4 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) calendar 
INNER JOIN `leave` ON calendar.date_value BETWEEN DATE(leave.BEGIN_DATE) AND DATE(leave.END_DATE) 
WHERE NOT WEEKDAY(date_value) IN (5, 6) AND NOT DATE(date_value) IN (SELECT DATE_HOLIDAY FROM holidays) GROUP BY ID_LEAVE;

    I've just tested in below links:

    https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=a25a3dc5762dba08541c002f12efc024

    Has someone any warnings on it? Thx a lot and have a nice easter ;)