INSERT INTO testtable
VALUES (' bm1 ', '2021-01-10 20:57:02', '2021-01-11 10:08:29)
, (' bm2 ', '2021-01-08 17:23:17', '2021-01-10 11:54:23)
Select * from testtable
ID NNAME begintime endtime
1 bm1 20:57:02. 2021-01-10 000 2021-01-11 10:08:29. 000
2 bm2 17:23:17. 2021-01-08 000 2021-01-10 11:54:23. 000
At 7:30 to 7:30 to day shift, 7:30 to 7:30 for late shift into:
ID NNAME begintime endtime remark
1 bm1 20:57:02. 2021-01-10 000 2021-01-11 06:59:59. 000 the 2021-01-10 night
2 bm1 07:30:00. 2021-01-11 000 2021-01-11 10:08:29. 000 the 2021-01-11 day
3 bm2 17:23:17. 2021-01-08 000 2021-01-08 18:59:59. 000 the 2021-01-08 day
4 bm2 19:30:00. 2021-01-08 000 2021-01-09 06:59:59. 000 the 2021-01-08 night
5 bm2 07:30:00. 2021-01-09 000 2021-01-09 18:59:59. 000 the 2021-01-09 day
6 bm2 19:30:00. 2021-01-09 000 2021-01-10 06:59:59. 000 the 2021-01-09 night
7 bm2 07:30:00. 2021-01-10 000 2021-01-10 11:54:23. 000 the 2021-01-10 day
SQL statement how to split, a great god help
CodePudding user response:
The CREATE TABLE # A (ID int identity (1, 1), NNAME VARCHAR (20), begintime DATEtime, endtime DATEtime);
INSERT INTO # A
VALUES (' bm1 ', '2021-01-10 20:57:02', '2021-01-11 10:08:29)
, (' bm2 ', '2021-01-08 17:23:17', '2021-01-10 11:54:23)
Select * from # A
The CREATE TABLE # B (ID int, NNAME VARCHAR (20), begintime DATEtime, endtime DATEtime);
DECLARE @ the BEGIN a DATETIME, @ END DATETIME, @ SQL VARCHAR (MAX), @ INT I=1, @ NAME VARCHAR (20), @ TIME DATETIME, @ TIME1 DATETIME
WHILE (@ I<#=(SELECT MAX (ID) FROM A))
The BEGIN
SELECT @ the BEGIN=BEGINTIME, @ END=ENDTIME, @ NAME=NNAME FROM # A WHERE ID=@ I
The SET @ TIME=LEFT (DATEADD (DD, 1, @ the BEGIN), 11) + '19:30:00'
WHILE (@ TIME<@ END)
The BEGIN
The SET @ TIME1=@ TIME
The SET @ TIME=DATEADD (SS, 41399, @ TIME)
INSERT INTO # B VALUES (@ I, @ NAME, @ TIME1, @ TIME)
The SET @ TIME1=@ TIME
The SET @ TIME=DATEADD (SS, 1801, @ TIME)
END
UPDATE # B SET begintime=@ the BEGIN WHERE NNAME=@ the NAME AND ID=@ I AND @ the BEGIN BETWEEN begintime AND endtime
DELETE # B WHERE NNAME=@ the NAME AND ID=@ I AND endtime & lt; @ the BEGIN
UPDATE SET endtime=@ # B END WHERE NNAME=@ the NAME AND ID=@ I AND @ the END BETWEEN begintime AND endtime
The SET @ I=@ I + 1
END
SELECT *, LEFT (the CONVERT (VARCHAR (30), begintime, 21)), 11) + "+ (CASE WHEN CAST (begintime AS TIME) BETWEEN '07:30:00' AND '18:59:59. 000' THEN 'day' ELSE 'night' END) REMARK FROM # B ORDER BY ID, begintime
DROP TABLE # B
DROP TABLE # A
to write the topic of a complex method, such as bosses get up answer tomorrow