difference between dates excluding custom periods

Question

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.

Answer 1

I tidied it up a bit. I finally got what I needed.

At first I changed the calendar table:

mysql> select id,date,workday from calendar where date between '2017-06-20' and '2017-06-30';
+-----+------------+---------+
| id  | date       | workday |
+-----+------------+---------+
| 202 | 2017-06-20 | 1       |
| 203 | 2017-06-21 | 1       |
| 204 | 2017-06-22 | 0.625   |
| 205 | 2017-06-23 | Holiday |
| 206 | 2017-06-24 | Holiday |
| 207 | 2017-06-25 | NULL    |
| 208 | 2017-06-26 | 1       |
| 209 | 2017-06-27 | 1       |
| 210 | 2017-06-28 | 1       |
| 211 | 2017-06-29 | 1       |
| 212 | 2017-06-30 | 1       |
+-----+------------+---------+
11 rows in set (0.00 sec)

1 in workday column says it's an ordinary workday, NULL means that it is not (weekend) and 0.625 is shortened workday (5/8=0,625).a shortened working day is 5 hours. This basically means if i sum the workday column and multiply it with the length of an ordinary workday, I get the normal working hours according to the law between selected period of time.

To do this, at the moment i just set up some variables, this is just a work in progress:

set @START='2016-12-01';
set @END='2016-12-31';
set @WORKDAY=8;
set @norm=(select sum(workday)*@workday from calendar where date between @start and @end);

So the variables @START and @END give me the period i am interested in. @NORM will give me the normal working hours if one is not on vacation or sick leave between @START and @END.

I added some generic data to schedule:

mysql> select id,position,start,end,away from schedule where away='P';

    +-----+----------+---------------------+---------------------+------+
    | id  | position | 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    |
    | 847 | 2.3      | 2017-07-31 00:00:00 | 2017-08-06 00:00:00 | P    |
    | 848 | 2.3      | 2017-08-21 00:00:00 | 2017-08-27 00:00:00 | P    |
    | 849 | 2.3      | 2017-06-26 00:00:00 | 2017-07-09 00:00:00 | P    |
    | 850 | 2.5      | 2017-08-29 00:00:00 | 2017-09-15 00:00:00 | P    |
    | 851 | 2.3      | 2017-08-29 00:00:00 | 2017-09-15 00:00:00 | P    |
    | 852 | 2.1      | 2016-11-15 00:00:00 | 2016-12-20 00:00:00 | P    |
    | 853 | 2.2      | 2016-11-10 00:00:00 | 2017-01-15 00:00:00 | P    |
    +-----+----------+---------------------+---------------------+------+
    19 rows in set (0.00 sec)

And to get the info about each employees specific working I use following select:

    SELECT position,@NORM AS normhrs,@NORM - (SUM(vac_workday))*@WORKDAY AS wrkhrs
FROM
(
SELECT position,holiday_start,holiday_end,
(SELECT SUM(workday) FROM calendar WHERE date BETWEEN holiday_start AND holiday_end) AS vac_workday
FROM
(
SELECT position,
DATE_FORMAT(CASE WHEN start<=@START THEN @START ELSE start END,'%Y-%m-%d') AS 'holiday_start',
DATE_FORMAT(CASE WHEN end>=@END THEN @END ELSE end END,'%Y-%m-%d') AS 'holiday_end'
FROM schedule WHERE away IS NOT NULL AND DATE_FORMAT(CASE WHEN start<=@START AND end>=@START THEN @START
WHEN start>=@START AND start<=@END THEN start
 ELSE NULL end,'%Y-%m')=DATE_FORMAT(@START,'%Y-%m')
)
AS outertable
) 
AS outertable2 GROUP BY position;

which gives me the output. there are only people who have vacations from @START to @END. If the person is not in the output, norm hours apply:

+----------+---------+--------+
| position | normhrs | wrkhrs |
+----------+---------+--------+
| 2.1      |     165 |     53 |
| 2.2      |     165 |      0 |
| 2.3      |     165 |    133 |
| 5.3.5.6  |     165 |    120 |
+----------+---------+--------+
4 rows in set, 6 warnings (0.00 sec)

This is the output i was looking for. i still need to come up with SELECT to sum up the actual working hours but it is easy enough not to put it in here.

Sorry, i know i'm over explaining some stuff but i being total noob, have found similar, rather superthorough answers, very helpful.

Answer 2

To calculate working hours do this:

$to_time = strtotime("$row[2]");
$from_time = strtotime("$row[3]");
$diff = $to_time - $from_time
echo round(abs($diff) / 3600,2). " hour(s)";

Where $row[2] & $rows[3] are your start and end dates. They /3600 is for calculating hours, you can get minutes by just writing 60 instead of 3600.

As for the working days and vacations, you can set up a counter and increment it for days and decrement it for vacations using the $diff variable. Count the number of hours or days you like and then increment/decrement it the way you like.