By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #Calendar(
id DATE
,isWorkDay BIT
,isHoliday BIT
);
INSERT INTO #Calendar(
id
,isWorkDay
,isHoliday
)
VALUES
('2022-01-14',1,0)
,('2022-01-15',0,0)
,('2022-01-16',0,0)
,('2022-01-17',1,0)
,('2022-01-18',1,0)
,('2022-01-19',1,0)
,('2022-01-20',1,0)
,('2022-01-21',1,0)
,('2022-01-22',0,0)
,('2022-01-23',0,0)
,('2022-01-24',1,0)
,('2022-01-25',1,0)
,('2022-01-26',1,0)
,('2022-01-27',1,0)
,('2022-01-28',1,0);
15 rows affected
CREATE TABLE #Data(
calcDate DATE
,startDate DATE
);
INSERT INTO #Data(
calcDate
)
VALUES
('2022-01-14')
,('2022-01-15')
,('2022-01-16')
,('2022-01-17')
,('2022-01-18')
5 rows affected
UPDATE t
SET startDate = a.startDate
FROM #Data t
CROSS APPLY (
SELECT MAX(id) AS startDate
FROM (
SELECT TOP 5 cal.id
FROM #Calendar cal
WHERE cal.id BETWEEN t.calcDate
AND DATEADD(month, 1, t.calcDate)
AND cal.isWorkDay = 1
AND cal.isHoliday = 0
ORDER BY cal.id
) q
) a;
5 rows affected
SELECT *
FROM #Data
| calcDate | startDate |
|---|---|
| 2022-01-14 | 2022-01-20 |
| 2022-01-15 | 2022-01-21 |
| 2022-01-16 | 2022-01-21 |
| 2022-01-17 | 2022-01-21 |
| 2022-01-18 | 2022-01-24 |