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.
#Average mortar worktime how to
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. Waste comes in many forms but mostly results from mortar that drops to the ground while pointing and mortar that cures too quickly and needs tossed.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). Waste and inconsistency has to be factored. But Let me save you some agony by saying, this just isn't so. In a perfect world, on a perfect job, yeah, 70 sq.
0 kommentar(er)
