SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE XX (
MNo VARCHAR(20),
MType VARCHAR(20),
MStart time,
MEnd time);

INSERT INTO XX VALUES 
('CT-001','Run','01:00:00','02:00:00'),
('CT-002','Run','01:15:00','01:52:00');

SELECT *
FROM XX;


MNo
MType
MStart
MEnd


CT-001
Run
01:00:00.0000000
02:00:00.0000000

CT-002
Run
01:15:00.0000000
01:52:00.0000000


CREATE TABLE YY (
MNo VARCHAR(20),
MType VARCHAR(20),
MStart time,
MEnd time);

INSERT INTO YY VALUES 
('CT-001','VA','01:10:00','01:20:00'),
('CT-001','CO','01:30:00','01:40:00'),
('CT-001','NO','01:50:00','01:55:00'),
--
('CT-002','2VB','01:03:00','01:05:00'),
('CT-002','2VA','01:10:00','01:20:00'),
('CT-002','2CO','01:30:00','01:40:00'),
('CT-002','2NO','01:50:00','01:55:00'),
('CT-002','2NH','02:00:00','02:10:00');

SELECT *
FROM YY;


MNo
MType
MStart
MEnd


CT-001
VA
01:10:00.0000000
01:20:00.0000000

CT-001
CO
01:30:00.0000000
01:40:00.0000000

CT-001
NO
01:50:00.0000000
01:55:00.0000000

CT-002
2VB
01:03:00.0000000
01:05:00.0000000

CT-002
2VA
01:10:00.0000000
01:20:00.0000000

CT-002
2CO
01:30:00.0000000
01:40:00.0000000

CT-002
2NO
01:50:00.0000000
01:55:00.0000000

CT-002
2NH
02:00:00.0000000
02:10:00.0000000


SELECT FF.MNo,
CASE WHEN FF.TStart >= QQ.MStart AND FF.TEnd <= QQ.MEnd 
AND FF.MARK1='S' AND FF.MARK2A='E' THEN 'Run' 
ELSE 'PreRun' END AS TType,
FF.TStart,FF.TEnd
FROM (
SELECT *,
LEAD(PP.TStart,1,NULL) OVER (PARTITION BY PP.MNo ORDER BY PP.TStart) AS TEnd,
LEAD(PP.MARK2,1,NULL) OVER (PARTITION BY PP.MNo ORDER BY PP.TStart) AS MARK2A
FROM (
SELECT MNo,MStart AS TStart,'S' AS MARK1,'' AS MARK2
FROM XX
UNION ALL
SELECT MNo,MEnd AS TStart,'' AS MARK1,'E' AS MARK2
FROM XX
--
UNION ALL
SELECT MNo,MStart AS TStart,'S' AS MARK1,'' AS MARK2
FROM YY
UNION ALL
SELECT MNo,MEnd AS TStart,'' AS MARK1,'E' AS MARK2
FROM YY
) AS PP) AS FF
LEFT JOIN XX AS QQ ON QQ.MNo = FF.MNo
WHERE FF.TEnd IS NOT NULL
ORDER BY FF.MNo,FF.TStart


MNo
TType
TStart
TEnd


CT-001
PreRun
01:00:00.0000000
01:10:00.0000000

CT-001
Run
01:10:00.0000000
01:20:00.0000000

CT-001
PreRun
01:20:00.0000000
01:30:00.0000000

CT-001
Run
01:30:00.0000000
01:40:00.0000000

CT-001
PreRun
01:40:00.0000000
01:50:00.0000000

CT-001
Run
01:50:00.0000000
01:55:00.0000000

CT-001
PreRun
01:55:00.0000000
02:00:00.0000000

CT-002
PreRun
01:03:00.0000000
01:05:00.0000000

CT-002
PreRun
01:05:00.0000000
01:10:00.0000000

CT-002
PreRun
01:10:00.0000000
01:15:00.0000000

CT-002
Run
01:15:00.0000000
01:20:00.0000000

CT-002
PreRun
01:20:00.0000000
01:30:00.0000000

CT-002
Run
01:30:00.0000000
01:40:00.0000000

CT-002
PreRun
01:40:00.0000000
01:50:00.0000000

CT-002
Run
01:50:00.0000000
01:52:00.0000000

CT-002
PreRun
01:52:00.0000000
01:55:00.0000000

CT-002
PreRun
01:55:00.0000000
02:00:00.0000000

CT-002
PreRun
02:00:00.0000000
02:10:00.0000000

MNo	MType	MStart	MEnd
CT-001	Run	01:00:00.0000000	02:00:00.0000000
CT-002	Run	01:15:00.0000000	01:52:00.0000000

MNo	MType	MStart	MEnd
CT-001	VA	01:10:00.0000000	01:20:00.0000000
CT-001	CO	01:30:00.0000000	01:40:00.0000000
CT-001	NO	01:50:00.0000000	01:55:00.0000000
CT-002	2VB	01:03:00.0000000	01:05:00.0000000
CT-002	2VA	01:10:00.0000000	01:20:00.0000000
CT-002	2CO	01:30:00.0000000	01:40:00.0000000
CT-002	2NO	01:50:00.0000000	01:55:00.0000000
CT-002	2NH	02:00:00.0000000	02:10:00.0000000

MNo	TType	TStart	TEnd
CT-001	PreRun	01:00:00.0000000	01:10:00.0000000
CT-001	Run	01:10:00.0000000	01:20:00.0000000
CT-001	PreRun	01:20:00.0000000	01:30:00.0000000
CT-001	Run	01:30:00.0000000	01:40:00.0000000
CT-001	PreRun	01:40:00.0000000	01:50:00.0000000
CT-001	Run	01:50:00.0000000	01:55:00.0000000
CT-001	PreRun	01:55:00.0000000	02:00:00.0000000
CT-002	PreRun	01:03:00.0000000	01:05:00.0000000
CT-002	PreRun	01:05:00.0000000	01:10:00.0000000
CT-002	PreRun	01:10:00.0000000	01:15:00.0000000
CT-002	Run	01:15:00.0000000	01:20:00.0000000
CT-002	PreRun	01:20:00.0000000	01:30:00.0000000
CT-002	Run	01:30:00.0000000	01:40:00.0000000
CT-002	PreRun	01:40:00.0000000	01:50:00.0000000
CT-002	Run	01:50:00.0000000	01:52:00.0000000
CT-002	PreRun	01:52:00.0000000	01:55:00.0000000
CT-002	PreRun	01:55:00.0000000	02:00:00.0000000
CT-002	PreRun	02:00:00.0000000	02:10:00.0000000