Sum days of person vacation in month or year

Question

I have table of vacation:

`holiday` (`ID_holiday`, `holiday_start`, `holiday_end`, `holiday_typ`, `ID_empl`)
(18, '2019-07-07', '2019-07-18', 1, 6),
(19, '2019-06-25', '2019-06-25', 3, 5),
(20, '2019-05-30', '2019-06-05', 1, 6),
(21, '2019-05-30', '2019-05-30', 1, 5),
(22, '2019-06-30', '2019-06-30', 1, 5),
(23, '2019-06-30', '2019-06-30', 1, 6),

I need the number of days of vacation per month and year (sum).
Vacation is entered during weekends and public holidays - I only need the sum of working days.

holiday_typ 1 .. 1 day
holiday_typ 3 .. 0.5 day

Is it possible to solve it by mysql query or mysql+php? Thanks

I try .. for one employer:

for ($Day = 1; $Day <= $last_day_month; $Day++) {
      $date = $YYYY.'-'.$Month.'-'.$Day;
      $cz_svatek = cz_svatek ($Month, $Day, $YYYY);  // return name of public holiday or ''

      $sql = "SELECT *
              FROM holiday
              JOIN holiday_typ  USING (ID_holiday_typ)
              WHERE ID = ".$ID."
                AND holiday_start <= '".$date."'
                AND holiday_end >= '".$date."'";
      $result = MySQL_Query($sql);

      while ($dbMuf = MySQL_Fetch_Array($result)){
        if( date("N", mktime(12, 0, 0, $Month, $Day, $YYYY)) < 6 && $cz_svatek == '') $holiday[$dbMuf["ID_dov_typ"]]++;
      }
$sum_holiday = $holiday[1]+0.5*$holiday[3];

it is possible to do it via single sql query - this will help a lot - https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates — myxaxa, Nov 06 '19 at 13:19
Are there no working days between '2019-07-07', '2019-07-18'? ANd how come the same employee can take the same day off twice? — Strawberry, Nov 06 '19 at 14:43
@Strawberry Not working days: 7.7 (Sunday), 13. - 14. (weekend) .. and 2019-07-18 (public holiday) .. I need sum {8.9.10.11.12.15.16.17} = 9 vac.days from this interval — DaveMX, Nov 12 '19 at 08:33

DaveMX · Answer 1 · 2019-11-19T07:43:13.277

I did it this way (for now):

('Hol_len' from table holiday_typ ... 0.5 || 1.0 || 0.0 ) (ID = ID_Empl)

$frs_day_y = mktime( 0, 0, 0, 1, 1, $YYYY );
$lst_day_y = mktime( 23, 59, 59, 12, 31, $YYYY );

for ($Day_mk = $frs_day_y; $Day_mk <= $lst_day_y; $Day_mk += 86400 ) {
    $Day   = date('d', $Day_mk);
    $Month = date('n', $Day_mk);
    $date  = date('Y-m-d', $Day_mk);

$sql = "SELECT *
              FROM dovolena
              JOIN holiday_typ  USING (ID_holiday_typ)
              WHERE Hol_len > 0
                AND holiday_start <= '".$date."'
                AND holiday_end >= '".$date."'";
    $result = MySQL_Query($sql);

    while ($dbMuf = MySQL_Fetch_Array($result)){
      if( date("N", $Day_mk) < 6 && cz_svatek ($Month, $Day, $YYYY) == '') {

        if( isset($Hol_sum[$Month][$dbMuf["ID"]]) ) $Hol_sum[$Month][$dbMuf["ID"]] += $dbMuf["Dov_len"];       //$dbMuf["ID_dov_typ"]
        else $Hol_sum[$Month][$dbMuf["ID"]] = $dbMuf["Hol_len"]+0;

        if( isset($Hol_sum_y[$dbMuf["ID"]]) ) $Hol_sum_y[$dbMuf["ID"]] += $dbMuf["Hol_len"];       //$dbMuf["ID_dov_typ"]
        else $Hol_sum_y[$dbMuf["ID"]] = $dbMuf["Hol_len"];

        $id[$dbMuf["ID"]] = 1;
      }
    }
  }

Sum days of person vacation in month or year

1 Answers1