By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Daily(
att_date DATE,
emp_code INT,
emp_name VARCHAR(50),
in_time DATETIME,
out_time DATETIME
);
CREATE TABLE Trnevents(
emp_readr_id int,
DT DATETIME
);
INSERT INTO Daily VALUES ('2018-10-21',9999,'Test','2018-10-21 08:00:00.000','2018-10-22 06:00:00.000');
INSERT INTO Trnevents VALUES (9999,'2018-10-21 08:00:00.000');
INSERT INTO Trnevents VALUES (9999,'2018-10-22 06:00:00.000');
SELECT emp_readr_id,
emp_name,
att_date,
MAX(CASE WHEN RN = 1 THEN time END) 'CHECK1',
MAX(CASE WHEN RN = 2 THEN time END) 'CHECK2',
MAX(CASE WHEN RN = 3 THEN time END) 'CHECK3',
MAX(CASE WHEN RN = 4 THEN time END) 'CHECK4'
FROM (
SELECT emp_readr_id,
emp_name,
att_date ,
ROW_NUMBER() OVER(PARTITION BY att_date ORDER BY DT) rn,
CONVERT(VARCHAR(10),DT,108) time
| emp_readr_id | emp_name | att_date | CHECK1 | CHECK2 | CHECK3 | CHECK4 |
|---|---|---|---|---|---|---|
| 9999 | Test | 21/10/2018 00:00:00 | 08:00:00 | 06:00:00 | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.