1st task.sql

CREATE TEMPORARY TABLE sessions_calculation(
  wnum INT(11) NOT NULL,
  work_date DATE NOT NULL,
  worked_hours TIME DEFAULT 0,
  INDEX (wnum, work_date)
);

# Same day
INSERT INTO sessions_calculation (wnum, work_date, worked_hours)
SELECT wnum
     , DATE(work_end)
     , TIMEDIFF(work_end, work_start)
FROM
  sessions
WHERE
  DATE(work_end) = DATE(work_start);

# From dusk
INSERT INTO sessions_calculation (wnum, work_date, worked_hours)
SELECT wnum
     , DATE(work_start)
     , SUBTIME('24:00:00', TIME(work_start))
FROM
  sessions
WHERE
  DATE(work_end) > DATE(work_start);

# Till dawn
INSERT INTO sessions_calculation (wnum, work_date, worked_hours)
SELECT wnum
     , DATE(work_end)
     , SUBTIME(TIME(work_end), '00:00:00')
FROM
  sessions
WHERE
  DATE(work_end) > DATE(work_start);

# Through full day
INSERT INTO sessions_calculation (wnum, work_date, worked_hours)
SELECT wnum
     , DATE(work_end) - INTERVAL 1 DAY
     , '24:00:00'
FROM
  sessions
WHERE
  DATEDIFF(DATE(work_end), DATE(work_start)) = 2;

# Simulate `excuse` time as work time
INSERT INTO sessions_calculation (wnum, work_date, worked_hours)
SELECT wnum
     , DATE(absent_end)
     , TIMEDIFF(absent_end, absent_start)
FROM
  excuse
WHERE
  DATE(absent_start) = DATE(absent_end); # skip throughful absent days, it's an excepted situation

# Get em all
SELECT wnum
             , work_date
             , SEC_TO_TIME(SUM(TIME_TO_SEC(worked_hours))) AS
               worked_hours
        FROM
          sessions_calculation
        WHERE
          YEAR(work_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
          AND MONTH(work_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) # last month
          AND worked_hours < '07:00:00' # less than 8 hours
          AND DAYOFWEEK(work_date) NOT IN (1, 7) # skip sunday, saturday
        GROUP BY # aggregate by this groups
                 wnum
               , work_date;

DROP TABLE sessions_calculation;