Skip to content

Instantly share code, notes, and snippets.

@logical-and

logical-and/1st task.sql

Last active December 19, 2015 07:09
Show Gist options
  • Save logical-and/5917209 to your computer and use it in GitHub Desktop.
Save logical-and/5917209 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. And renamed this gist Jul 3, 2013. 1 changed file with 0 additions and 0 deletions.
    0 1st task → 1st task.sql
    View file
    File renamed without changes.
  2. And renamed this gist Jul 3, 2013. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.sql → 1st task
    View file
    File renamed without changes.
  3. And created this gist Jul 3, 2013.
    74 changes: 74 additions & 0 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    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;