In SQL, how do I filter a parent with its child records if either satisfies a condition

I have the following table as example. I want to retrieve only the records where the ScheduleDate is smaller than 2022-01-03 (Jan 3, 2022) for either the parent and child records but I want the whole family only (parents and children together). With this set of records, the results should return the records 1 to 9 only. ScheduleId 12 does not satisfy the condition and has relationship with 10 and 13. 10 has a relationship with 11.

DROP TABLE IF EXISTS #MySchedule;
CREATE TABLE #MySchedule (ScheduleId INT, ParentScheduleId INT, ScheduleDate DATETIME2);

INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (1,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (2,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (3,2,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (4,2,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (5,4,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (6,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (7,6,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (8,6,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (9,8,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (10,NULL,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (11,10,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (12,10,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (13,12,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (14,NULL,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (15,NULL,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (16,NULL,'2022-01-04');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (17,16,'2022-01-04');

Answer

I must say that your problem definition is a bit ambiguous. In writhing this solution I had to make the assumption that you expect to see a family where there are no children who have a ScheduleDate greater than 2022-01-03. If that is not the solution you looking for please clarify your problem definition. Here we go 😀

DROP TABLE IF EXISTS #MySchedule;
CREATE TABLE #MySchedule (ScheduleId INT, ParentScheduleId INT, ScheduleDate DATETIME2);

INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (1,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (2,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (3,2,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (4,2,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (5,4,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (6,NULL,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (7,6,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (8,6,'2022-01-01');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (9,8,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (10,NULL,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (11,10,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (12,10,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (13,12,'2022-01-02');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (14,NULL,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (15,NULL,'2022-01-03');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (16,NULL,'2022-01-04');
INSERT #MySchedule (ScheduleId, ParentScheduleId, ScheduleDate) VALUES  (17,16,'2022-01-04');

DECLARE @date DATETIME2 = '2022-01-03';

SELECT COUNT(1) FROM #MySchedule;

WITH src AS (
    /* get all ultimate parents */
    SELECT ScheduleId,
           [ParentScheduleId],
           ScheduleDate,
           ScheduleId [ultimate_ParentScheduleId]
    FROM #MySchedule shd
    WHERE shd.ParentScheduleId  IS NULL
        UNION ALL
    SELECT shd.ScheduleId,
           shd.ParentScheduleId,
           shd.ScheduleDate,
           src.ultimate_ParentScheduleId
    FROM #MySchedule shd
        JOIN src
            ON shd.ParentScheduleId = src.ScheduleId
)
SELECT
    src.ultimate_ParentScheduleId,
    src.ParentScheduleId,
    src.ScheduleId,
    src.ScheduleDate
FROM src
    JOIN (
        SELECT src.ultimate_ParentScheduleId
        FROM src
        GROUP BY src.ultimate_ParentScheduleId
        HAVING MAX(src.ScheduleDate) < @date
    ) gp
        ON gp.ultimate_ParentScheduleId = src.ultimate_ParentScheduleId
ORDER BY src.ultimate_ParentScheduleId, COALESCE(src.ParentScheduleId, src.ScheduleId), src.ScheduleId
;