除自定义期间之间的日期差异(difference between dates excluding custom periods)

 早上好， 
 
 我需要一个解决方案来计算特定日期之间的天数。 此外，我需要排除特定日期（公众假期和周末），这很容易，并且有很多关于如何做的说明。 
 
 但我还需要做一件事。 对于每个人，我也有自定义休假期，我需要从之前的结果中减去它们。 
 
 目前，我有两张桌子。 一个用于定制度假期间： 
 
+-----+----------+---------------------+---------------------+------+
| id  | employee | start               | end                 | away |
+-----+----------+---------------------+---------------------+------+
| 835 | 2.3      | 2016-12-05 00:00:00 | 2016-12-10 00:00:00 | P    |
| 836 | 5.3.5.1  | 2017-01-03 00:00:00 | 2017-01-23 00:00:00 | P    |
| 837 | 5.3.5.6  | 2016-12-21 00:00:00 | 2017-04-01 00:00:00 | P    |
| 838 | 5.3.3.1  | 2017-01-01 00:00:00 | 2017-01-03 00:00:00 | P    |
| 839 | 5.3.1.2  | 2017-01-03 00:00:00 | 2017-01-12 00:00:00 | P    |
| 840 | 5.3.1.6  | 2017-01-01 00:00:00 | 2017-01-01 00:00:00 | P    |
| 841 | 5.1.7    | 2017-01-09 00:00:00 | 2017-01-15 00:00:00 | P    |
| 842 | 2.2      | 2017-02-16 00:00:00 | 2017-02-26 00:00:00 | P    |
| 843 | 2.5      | 2017-07-31 00:00:00 | 2017-08-06 00:00:00 | P    |
| 844 | 2.5      | 2017-08-21 00:00:00 | 2017-08-27 00:00:00 | P    |
| 845 | 2.5      | 2017-06-26 00:00:00 | 2017-07-09 00:00:00 | P    |
| 846 | 2.4      | 2017-04-04 00:00:00 | 2017-04-08 00:00:00 | P    |
+-----+----------+---------------------+---------------------+------+
12 rows in set (0.00 sec)
 
 我在开始和结束时有日期和时间的原因是该表实际上也有关于他们的工作时间表的信息。 休假计划标记为“离开”列设置为“P”。 
 
 第二个表看起来像这样： 
 
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| date      | varchar(45) | YES  |     | NULL    |                |
| dayofweek | varchar(45) | YES  |     | NULL    |                |
| short     | varchar(45) | YES  |     | NULL    |                |
| holiday   | varchar(45) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
 这个表基本上是一个日历，其中包含有关星期几的其他信息（我知道这可以通过dayofweek（）完成，但我也需要此列），'short'确定它是否是缩短的工作日（在计算小时数时必不可少）因为工作日较短的一年中有几天而公共假期是“假期”。 
 
 我试图通过减去公共假日和周末（我可以做并且相对容易）来计算如何计算一个月（2017年8月1日 - 2017年8月31日）的工作日，并减去自定义的假期。 主要问题是，对于某些人来说，一个月内可能会有一个以上的缺席期。 
 
 如果您查看上面的示例，员工2.5的休假期从7月开始到8月结束，我只需要减去该期间8月份的金额。 员工2.5在8月有另一个假期，我也需要减去这个假期。 除此之外，该员工在6月至7月期间还有另一个假期。 
 
 完美的结果将是这样的： 
 
+----------+-------+---------------+--------------+---------------+
| employee | month | working hours | working days | vacation days |
+----------+-------+---------------+--------------+---------------+
 
 我能够想出的唯一解决方案是创建一个名为vacations的新表，每天都有一列，每行显示一名员工的数据。 或者另一种方式是员工是列和行的日期。 但在我知道没有更容易的方法之前，我不想开始玩这个想法。 
 
 再一次，我确信在mysql中有一个简单的功能，但我无法弄明白。 

Good morning,
 
I need a solution for counting days between specific dates. In addition I need to exclude specific dates (public holidays and weekends), which is easy and there are plenty of instructions on how to do it.
 
But I need to accomplish one more thing. For every person, I also have custom vacation periods and I need them to be subtracted from the previous result.
 
At the moment, i have two tables. One for the custom vacation periods:
 
+-----+----------+---------------------+---------------------+------+
| id  | employee | start               | end                 | away |
+-----+----------+---------------------+---------------------+------+
| 835 | 2.3      | 2016-12-05 00:00:00 | 2016-12-10 00:00:00 | P    |
| 836 | 5.3.5.1  | 2017-01-03 00:00:00 | 2017-01-23 00:00:00 | P    |
| 837 | 5.3.5.6  | 2016-12-21 00:00:00 | 2017-04-01 00:00:00 | P    |
| 838 | 5.3.3.1  | 2017-01-01 00:00:00 | 2017-01-03 00:00:00 | P    |
| 839 | 5.3.1.2  | 2017-01-03 00:00:00 | 2017-01-12 00:00:00 | P    |
| 840 | 5.3.1.6  | 2017-01-01 00:00:00 | 2017-01-01 00:00:00 | P    |
| 841 | 5.1.7    | 2017-01-09 00:00:00 | 2017-01-15 00:00:00 | P    |
| 842 | 2.2      | 2017-02-16 00:00:00 | 2017-02-26 00:00:00 | P    |
| 843 | 2.5      | 2017-07-31 00:00:00 | 2017-08-06 00:00:00 | P    |
| 844 | 2.5      | 2017-08-21 00:00:00 | 2017-08-27 00:00:00 | P    |
| 845 | 2.5      | 2017-06-26 00:00:00 | 2017-07-09 00:00:00 | P    |
| 846 | 2.4      | 2017-04-04 00:00:00 | 2017-04-08 00:00:00 | P    |
+-----+----------+---------------------+---------------------+------+
12 rows in set (0.00 sec)
 
The reason I have date and time on start and end is that the table actually has info about their working schedule also. The vacation schedule is marked by column 'away' set as 'P'.
 
The second table looks like this:
 
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| date      | varchar(45) | YES  |     | NULL    |                |
| dayofweek | varchar(45) | YES  |     | NULL    |                |
| short     | varchar(45) | YES  |     | NULL    |                |
| holiday   | varchar(45) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
This table is basically a calendar with additional information about what day of week it is (i know that this can be done with dayofweek() but i need this column also), 'short' determines whether it's a shortened workday (essential when calculating hours because there are a couple of days in a year when the working day is shorter) and 'holiday' which marks public holidays.
 
I am trying to figure out how to calculate working days during one month (01.08.2017 - 31.08.2017) by subtracting public holidays and weekends (which I can do and is relatively easy) and also subtract custom period of vacations. The main problem is that for some people, there can be more than one period of absence during one month. 
 
If you look at the example above, employee 2.5 has a vacation period that starts in July and ends in August and I need to subtract only the amount in that period that is in August. Employee 2.5 has another vacation in August and I need to subtract this also. In addition to that, this employee has another vacation from June to July. 
 
The perfect outcome would be something like this:
 
+----------+-------+---------------+--------------+---------------+
| employee | month | working hours | working days | vacation days |
+----------+-------+---------------+--------------+---------------+
 
The only solution I am able to come up with is to create a new table called vacations with a column for each day and each row shows data for one employee. Or the other way around where employees are columns and rows are dates. But I would not like to start playing with this idea before i know that there isn't an easier way to do it.
 
Once again, I am sure that there is an easy function in mysql for this but I fail to figure it out.

原文：https://stackoverflow.com/questions/41218919