By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH Person(person_id)
AS
(
SELECT 10001 UNION ALL
SELECT 10002 UNION ALL
SELECT 10003 UNION ALL
SELECT 10004
),
Dates(date_type,dat)
AS
(
SELECT 'PUBLIC_HOLIDAY','2020-04-10 00:00:00.000' UNION ALL
SELECT 'PUBLIC_HOLIDAY','2020-04-13 00:00:00.000'
),
Absence(person_id,dat,absence_type)
AS
(
SELECT 10001,'2020-04-10 00:00:00.000','HOLIDAY' UNION ALL
SELECT 10001,'2020-04-13 00:00:00.000','HOLIDAY' UNION ALL
SELECT 10002,'2020-04-10 00:00:00.000','HOLIDAY' UNION ALL
SELECT 10003,'2020-04-13 00:00:00.000','HOLIDAY'
)
SELECT Person.person_id,Dates.dat,ISNULL(Absence.dat, 'Not Bokked')
FROM Dates
CROSS JOIN Person
LEFT JOIN Absence ON Person.person_id = Absence.person_id AND Dates.dat = Absence.dat
WHERE Dates.date_type = 'PUBLIC_HOLIDAY'
| person_id | dat | (No column name) |
|---|---|---|
| 10001 | 2020-04-10 00:00:00.000 | 2020-04-10 00:00:00.000 |
| 10002 | 2020-04-10 00:00:00.000 | 2020-04-10 00:00:00.000 |
| 10003 | 2020-04-10 00:00:00.000 | Not Bokked |
| 10004 | 2020-04-10 00:00:00.000 | Not Bokked |
| 10001 | 2020-04-13 00:00:00.000 | 2020-04-13 00:00:00.000 |
| 10002 | 2020-04-13 00:00:00.000 | Not Bokked |
| 10003 | 2020-04-13 00:00:00.000 | 2020-04-13 00:00:00.000 |
| 10004 | 2020-04-13 00:00:00.000 | Not Bokked |