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 (start or stop clock time) the next day, need a general SQL statements
CodePudding user response:
It's so late still in the study? Don't sleep?CodePudding user response:
The CREATE TABLE # A
(
EMPLSN VARCHAR (20),
EMPLNAME VARCHAR (20),
SKDATETIME DATETIME,
SKDATE DATE,
SKTIME TIME
)
# insert into A values (' 0001 ', 'zhang', '2020-10-15 23:55:00', '2020-10-15', '23:55:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-15 23:53:00', '2020-10-15', '23:53:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-15 23:59:00', '2020-10-15', '23:59:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:55:00', '2020-10-16', '23:55:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:53:00', '2020-10-16', '23:53:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-16 23:59:00', '2020-10-16', '23:59:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:55:00', '2020-10-17', '23:55:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:53:00', '2020-10-17', '23:53:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0001 ', 'zhang', '2020-10-17 23:59:00', '2020-10-17', '23:59:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:55:00', '2020-10-15', '23:55:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:53:00', '2020-10-15', '23:53:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-15 23:59:00', '2020-10-15', '23:59:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-16 23:55:00', '2020-10-16', '23:55:00');
# insert into A (emplsn emplname, skdatetime, skdate, sktime) values (' 0002 ', 'bill', '2020-10-16 23:53:00', '2020-10-16', '23:53:00');
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull