Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-15 23:55:00', '2020-10-15', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-15 23:53:00', '2020-10-15', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-15 23:59:00', '2020-10-15', '23:59:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:55:00', '2020-10-16', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:53:00', '2020-10-16', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:59:00', '2020-10-16', '23:59:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:55:00', '2020-10-17', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:53:00', '2020-10-17', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:59:00', '2020-10-17', '23:59:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:55:00', '2020-10-15', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:53:00', '2020-10-15', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:59:00', '2020-10-15', '23:59:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-16 23:55:00', '2020-10-16', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-16 23:53:00', '2020-10-16', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-16 23:59:00', '2020-10-16', '23:59:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-17 23:55:00', '2020-10-17', '23:55:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-17 23:53:00', '2020-10-17', '23:53:00');
Insert into BBB (emplsn, emplname skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-17 23:59:00', '2020-10-17', '23:59:00');
Regulation time is "00:30", allowing 40 minutes early clock in, how to use the SQL statement to get time to work, hope to get the following result
Gonghaowu name date time
0001 zhang 2020-10-15 null
0001 zhang 2020-10-16 2020-10-15 23:53:00
0001 zhang 2020-10-17 2020-10-16 23:53:00
The 2020-10-15, 0002, dick, and harry null
The 2020-10-16 2020-10-15 0002, dick, and harry 23:53:00
The 2020-10-17 2020-10-16 0002, dick, and harry 23:53:00
The date clock is not fixed, may all clock in 1 months, may also have some date not clock in, most of the flight is normal during the day shift, individual divisions are like the night shift (the next day the start or stop clock time) need a general SQL statements
CodePudding user response:
Must be clear, what's the clock time is allowed:The working hours of such as allowing 23:50 - the day the day before 0:30
Write a first filter conditions, meet the conditions of effective clock time, don't meet is null
Clock in case the when time between previous 23:50 and 00:30 that day then clock time else null end
This date time to add and subtract yourself tossing, date_add, date_sub function
Second, can be repeated clock in, just choose the smallest
Min (case when clock in time between the day before 23:50 and 00:30 that day then clock time else null end)
Combined with grouping conditions
Select id, name, date of punching, min (case when clock in time between the day before 23:50 and 00:30 that day then clock time else null end)
The from clock out record
Group by id, name