![]() | Empl_Name | last_month_year | last_month | total_days_at_work | total_seconds | total_hours | avg_hours_per_day_worked | avg_hours_per_day_prev_month | WHERE YEAR(t.coming) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)ĪND MONTH(t.coming) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) SELECT e.Empl_Name, a.Date_Time coming, b.Date_Time leaving, TIMEDIFF(b.Date_Time, a.Date_Time) AS timein TIME_TO_SEC(SUM(t.timein))/3600/DAY(LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)) avg_hours_per_day_prev_month TIME_TO_SEC(SUM(t.timein))/3600/COUNT(DISTINCT DATE(t.coming)) avg_hours_per_day_worked, TIME_TO_SEC(SUM(t.timein))/3600 total_hours, TIME_TO_SEC(SUM(t.timein)) total_seconds, MONTHNAME(CURRENT_DATE - INTERVAL 1 MONTH) last_month,ĬOUNT(DISTINCT DATE(t.coming)) total_days_at_work, YEAR(CURRENT_DATE - INTERVAL 1 MONTH) last_month_year, In the next step we could wrap it in a subquery to calculate the total hours and the average hours per day for the previous month: SELECT | Empl_Name | coming | leaving | timein | INNER JOIN empl b ON e.Empl_Name = b.Empl_Name AND b.Direction = 0 AND a.Date_Time < b.Date_Time INNER JOIN empl a ON e.Empl_Name = a.Empl_Name AND a.Direction = 1 One possible approach is to start with a list of coming and leaving, grouped by employees: SELECT e.Empl_Name, a.Date_Time coming, b.Date_Time leaving, TIMEDIFF(b.Date_Time, a.Date_Time) AS timein Empl_Name TimeInDate AvgTimeDiffSecs SumTimeDiffSecs GROUP BY p1.Empl_Name, DATE_FORMAT(p1.Date_Time_In,'%Y-%m-%d') ON p1.Empl_Name = p2.Empl_Name AND p1.TimeInRank = p2.TimeOutRankĪND DATE_FORMAT(p1.Date_Time_In,'%Y-%m-%d') = DATE_FORMAT(p2.Date_Time_Out,'%Y-%m-%d') (SELECT t.Empl_Name, DATE_FORMAT(t.Date_Time,'%Y-%m-%d') AS `TimeInDate`, SUM(TIMEDIFF(p1.Date_Time_In, p2.Date_Time_Out)) AS `SumTimeDiffSecs` SELECT p1.Empl_Name, DATE_FORMAT(p1.Date_Time_In,'%Y-%m-%d') AS `TimeInDate`,ĪVG(TIMEDIFF(p1.Date_Time_In, p2.Date_Time_Out)) AS `AvgTimeDiffSecs`, ![]() Once the two sets are joined then outer query aggregates grouping by Empl_Name and Date for average or total time differences by day. Hence, a rank specifies the first, second, and onward times employee checks in and out, incrementing across all days. Each derived table accounts for Time In records and Time Out records respectively.Īlso, a TimeRank is integrated for those multiple records per day since a join by date outputs all same day combinations but we only want time period pairings (start/end) joined within a day. That is mean that I want to sum work time for every employee andĬonsider a join of derived tables matching Empl_Name and Date (latter pulled from datetime field). +-+-+-+Įxpected output: a1 spent on the workplace 7/30 secs, a2 spent 5/30 secs, a3 spent 4/30 secs. How to write a request that will calculate the average number of hours per day that the employee spent on the workplace for the previous month?Įxample of table. Within one day the employee can repeatedly enter and leave. If the employee enters, then he necessarily leaves on the same day. I have table Time_Periods (Empl_Name, Direction, Date_Time), which stores data on the entry and exit of employees.ĭate_Time - The date and time the action was committed (detailed until a second).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |