;WITH
c
AS
(
SELECT a.Name, b.NewDate
FROM Joining a
LEFT JOIN
( SELECT DATEADD(DAY, -1, [Holiday Date]) NewDate
FROM Holiday
WHERE DATEADD(DAY, -1, [Holiday Date]) NOT IN (SELECT [Holiday Date] FROM Holiday)
) b ON a.[Joining Date] > b.NewDate
),
d
AS
(
SELECT c.Name, MAX(c.NewDate) NewDate FROM c
GROUP BY c.Name
)
SELECT j.Name,
CASE WHEN h.[Holiday Date] IS NULL THEN j.[Joining Date] ELSE d.NewDate END
FROM Joining j
LEFT JOIN Holiday h ON j.[Joining Date] = h.[Holiday Date]
INNER JOIN d ON j.Name = d.Name