counting days in multiple periods/absences in one month

Question

I have code that counts working days of absence in current month.
It works well if in month i need to count absence only has one period for person.
Dates from and till are saved on database.
Example 2016-08-09 - 2016-08-13 (lets say user was sick) it counts 4 working days

But if user have:

2016-07-27 - 2016-08-02 (lets say user was sick)
2016-08-08 - 2016-08-10 (lets say user was on vocation) and
2016-08-24 - 2016-09-02 (lets say again user was sick)
how to count all days in absence periods for this month 2016 August? In this case should be 12 days

Getting from database:

 $getaways =  mysqli_query($conn, "SELECT * FROM employeesAbsence WHERE workerID='".$row['worker']."' AND fromDate LIKE  '%2016-08%' AND toDate LIKE  '%2016-08%'); 
 $aways_row = mysqli_fetch_array($getaways);

        $fDate = $aways_row['fromDate'];
        $tDate = $aways_row['toDate'];

Counting code:

$startDate = $fDate;
$endDate =  $tDate; 
$weekdays = array('1','2','3','4','5'); //monday-friday

$begin = new DateTime($startDate);
$end = new DateTime($endDate);

$end = $end->modify( '+1 day' ); //add one day so as to include the end date of our range

$interval = new DateInterval('P1D'); // 1 Day
$dateRange = new DatePeriod($begin, $interval, $end);

$total_days = 0;
//this will calculate total days from monday to friday in above date range
foreach ($dateRange as $dates) {

 if (in_array($dates->format("N"),$weekdays)) {
        $total_days++; 
  }
}
echo $total_days;

Answer 1

You could likely do this entirely in the sql query - not tested but I think it should be ok.

For example:

select sum( datediff(`toDate`,`fromDate`) ) as 'sickdays' 
   from `employeesabsence`
   where `workerID`=$row['worker']

You could add additional conditions to the where clause to restrict to specific date range - like this perhaps:

select sum( datediff(`todate`,`fromdate`) ) as 'sickdays' 
    from `employeesabsence` 
    where `workerid`=$row['worker'] and month( `fromdate` ) between 1 and 6;

The solution, in sql, to find working days is a little trickier and the following was adapted from another post found on stack - it involves a peculiar looking string of integers.

For example

select 
sum( 5 * ( datediff( `todate`, `fromdate` ) div 7 ) + mid( '0123455401234434012332340122123401101234000123450', 7 * weekday(`fromdate`) + weekday(`todate`) + 1, 1) ) as 'sickdays'
from `employeesabsence` where month(`fromdate`) between 6 and 7

The post from which this was adapted can be found here

Answer 2

PHP DateTime Option:

    function get_days( $start, $end )
    {  
      $begin = new DateTime( $start );
      $end   = new DateTime( $end );
      //$end   = $end->modify( '+1 day' ); //add one day so as to include the end date of our range

      $total_days = 0;
    //this will calculate total days from monday to friday in above date range
      for( $i = $begin; $i <= $end; $i->modify( '+1 day' ) )
      {
        // Check that the date is between Monday and Friday and only in August
        if( ( $i->format( 'N' ) >= 1 && $i->format( 'N') <= 5 ) && $i->format( 'm' ) == '08' )
        {
          $total_days++;
        }
      }
      return $total_days;
    }

    $total = 0;
    $total += get_days( '2016-07-27', '2016-08-02');
    $total += get_days( '2016-08-08', '2016-08-10');
    $total += get_days( '2016-08-24', '2016-09-02');