How to get weekly data from a timestamp?

Question

I have two tables, "Gate_Logs" and "Employee".

The "Gate_Logs" table has three columns.

1. Employee ID - A 4 Digit Unique Number assigned to every employee
2. Status – In or Out
3. Timestamp – A recorded timestamp

The "Employee" Table has

1. Employee ID
2. Level
3. Designation
4. Joining Date
5. Reporting Location
6. Reporting Location ID - Single Digit ID

I want to find out which employee had the highest weekly work time over the past year, and I am trying to get this data for each individual location. I want to look at the cumulative highest. Let's say Employee X at Location L worked 60 hours in a particular week, which was the highest at that location, so X will be the person I wanted to query.

Please provide any pointers on how I can proceed with this, have been stuck at it for a while.

SQL version 8.0.27

Answer 1

It can use window function LAG to pair In/Out records

• periods - pair in/out records
• sumup_weekly - compute weekly work hours for each employee
• rank_weekly - rank employees per location per week

and finally select those rank one

---

WITH periods AS (
  SELECT 
    `employee_id`, 
    `status` to_status,
    `timestamp` to_timestamp,
    LAG(`status`) OVER w AS fr_status,
    LAG(`timestamp`) OVER w AS fr_timestamp
  FROM gate_log
  WINDOW w AS (PARTITION BY `employee_id` ORDER BY `timestamp` ASC)
),
sumup_weekly AS (
  SELECT 
    `employee_id`, 
    WEEKOFYEAR(fr_timestamp) week, 
    SUM(TIMESTAMPDIFF(SECOND, fr_timestamp, to_timestamp)) seconds
  FROM periods
  WHERE fr_status = 'In' AND to_status = 'Out'
  GROUP BY `employee_id`, `week`
),
rank_weekly AS (
  SELECT
    e.`employee_id`,
    e.`location_id`,
    w.`week`,
    SEC_TO_TIME(w.`seconds`) work_hours,
    RANK() OVER(PARTITION BY e.`location_id`, w.`week` ORDER BY w.`seconds` DESC) rank_hours
  FROM sumup_weekly w
  JOIN employee e ON w.`employee_id` = e.`employee_id`
)
SELECT *
FROM rank_weekly
WHERE rank_hours = 1

DEMO