合并sql表中的重叠日期间隔

Question

I have a table which contains shift sign in and sign out times of employees. Each record has AutoGenID, employeeID, start datetime and end datetime. Based on these data i want to find the hours of work for each employee for a given period of time such as a month.

This is not web based but a Mobile and a PC app which can run in offline mode.

The problem is that there may or may not be multiple records for a single employee with overlapping date intervals. This is because an employee can individually sigh in on multiple devices per day and all these entries are added on this centralized database table. Hence there may or may not be overlapping date intervals for a single employee.

For example lets say that user A signed in from his mobile at '2015-07-03 10:51:19' and later signed in again from his PC at '2015-07-03 12:36:14'

At the end of the day he signed off from his PC at '2015-07-03 18:12:29' and signed off his mobile at '2015-07-03 18:19:53'

And Next day he only uses his PC to sign in. So on the database i have the following records.

+----+-----------+---------------------+---------------------+
| ID |   EmpID   |         start       |         End         |
+----+-----------+---------------------+---------------------+
| 1  |   EM001   | 2015-07-03 10:51:19 | 2015-07-03 18:19:53 |
| 2  |   EM001   | 2015-07-03 12:36:14 | 2015-07-03 18:12:29 |
| 3  |   EM001   | 2015-07-04 11:34:52 | 2015-07-04 17:21:43 |
+----+-----------+---------------------+---------------------+

But when querying the data i only need the Hours the employee worked. So I need the first start time and the last end time if the dates are overlapping so that i can calculate the hours. Again this issue is there only for days with overlapping times and there may not be overlapping times for a single employee on some days.

This hour calculation is not for employee salary purposes. Its just to capture the work hour of the user.

So does anyone have any idea on how to do this.

Thanks

Answer 1

Dipen Shah has a point, but sometimes you can't change the way sign-ins are logged. In this case perhaps you can try grouping the table by the date and querying the min(start) and max (end) of every user and date, like this:

select empid, min(start), max(end) from signin group by empid, date(start);