By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET DATEFORMAT dmy;
CREATE TABLE Shift
(
Empnum varchar(50),
punchdate date,
shift decimal(5,1)
);
CREATE TABLE Leave
(
Empnum varchar(50),
LeaveDate date,
leaveFN decimal(5,1),
LeaveAF decimal(5,1)
);
INSERT INTO Shift (Empnum, punchdate, shift)
VALUES('Test1', '01/09/2020', 1),
('Test1', '03/09/2020', 0.5),
('Test2', '01/09/2020', 1),
('Test2', '02/09/2020', 1)
INSERT INTO Leave(Empnum, LeaveDate, leaveFN, LeaveAF)
VALUES('Test1' , '02/09/2020', 0.5, 0.5),
('Test1', '03/09/2020', 0, 0.5)
;WITH CTE AS
(
SELECT punchdate AS CurrentDate
FROM Shift
UNION ALL
SELECT LeaveDate As CurrentDate
FROM Leave
), Emps AS
(
| Empnum | CurrentDate | shift | leaveFN | leaveAF |
|---|---|---|---|---|
| Test1 | 2020-09-01 | 1.0 | 0.0 | 0.0 |
| Test2 | 2020-09-01 | 1.0 | 0.0 | 0.0 |
| Test1 | 2020-09-02 | 0.0 | 0.5 | 0.5 |
| Test2 | 2020-09-02 | 1.0 | 0.0 | 0.0 |
| Test1 | 2020-09-03 | 0.5 | 0.0 | 0.5 |
| Test2 | 2020-09-03 | 0.0 | 0.0 | 0.0 |