Please, help with my problem.

I have 2 tables:

• calendar with workdays and holidays;
• table with calculate.

I need to calc **startDate ** = [calcDt 5 workdays (excluding weekends and holidays)]. See example.

My calendar:

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);

My table with calculate:

CREATE TABLE #Data(
     calcDate DATE
    ,startDate DATE
);

INSERT INTO #Data(
    calcDate
)
VALUES
     ('2022-01-14')
    ,('2022-01-14')
    ,('2022-01-14')
    ,('2022-01-15')
    ,('2022-01-16')
    ,('2022-01-17')
    ,('2022-01-18');

I need to calculate startDate = calcDate 5 workdays, example:

calcDate      startDate
2022-01-14    2022-01-20  
2022-01-14    2022-01-20
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

CodePudding user response：

An UPDATE with a CROSS APPLY to the calendar will work.

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;

Demo on db<>fiddle here