SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE activity (activityId INT IDENTITY NOT NULL PRIMARY KEY,
OpenDate DATE NOT NULL,
CloseDate DATE NOT NULL);
INSERT INTO activity(OpenDate, CloseDate)
VALUES('20220312', '20220703'),
    ('20220530', '20220901'),
    ('20220901', '20221001'),
    ('20220402', '20220501');

CREATE TABLE dates ([date] DATE NOT NULL, NonWorkingDay VARCHAR(50));
INSERT INTO dates([date], NonWorkingDay)
VALUES('20220101', 'sat'),
    ('20220512', 'sun'),
    ('20220908', 'sun'),
    ('20220403', 'Bank Holiday');
8 rows affected

SELECT activityId, OpenDate, CloseDate, CASE WHEN COUNT([holiday].[date])<3 THEN 1 ELSE 0 END AS nwc
FROM activity a
     CROSS APPLY(SELECT TOP(DATEDIFF(DAY, a.OpenDate, a.CloseDate)+1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
                 FROM master.sys.all_columns t1
                      CROSS JOIN master.sys.all_columns t2) t(N)
     CROSS APPLY(SELECT DATEADD(DAY, t.N-1, a.OpenDate)) theDates(dt)
     OUTER APPLY(SELECT [date] FROM dbo.dates AS d WHERE d.date=theDates.dt) holiday([date])
GROUP BY activityId, OpenDate, CloseDate;


activityId
OpenDate
CloseDate
nwc


1
2022-03-12
2022-07-03
1

2
2022-05-30
2022-09-01
1

3
2022-09-01
2022-10-01
1

4
2022-04-02
2022-05-01
1


Warning: Null value is eliminated by an aggregate or other SET operation.


SELECT activityId, OpenDate, CloseDate, 
	CASE WHEN SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END) < 25 THEN 1 ELSE 0 END AS nonworking
FROM activity a
CROSS APPLY (
SELECT TOP (DATEDIFF(DAY, a.opendate, a.closedate)+1)
			ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
			FROM Master.sys.All_Columns t1
			CROSS JOIN Master.sys.All_Columns t2) t(N)
CROSS APPLY (SELECT DATEADD(DAY,t.N-1, a.OpenDate)) theDates(dt)
CROSS APPLY (SELECT (DATEPART(WEEKDAY, theDates.dt)+ @@DATEFIRST)%7) weeks(wd)
OUTER APPLY (SELECT [date] FROM dbo.dates AS d WHERE d.date = theDates.dt) holiday([date])
GROUP BY activityId, OpenDate, CloseDate;


activityId
OpenDate
CloseDate
nonworking


1
2022-03-12
2022-07-03
0

2
2022-05-30
2022-09-01
0

3
2022-09-01
2022-10-01
1

4
2022-04-02
2022-05-01
1


Warning: Null value is eliminated by an aggregate or other SET operation.
activityId	OpenDate	CloseDate	nwc
1	2022-03-12	2022-07-03	1
2	2022-05-30	2022-09-01	1
3	2022-09-01	2022-10-01	1
4	2022-04-02	2022-05-01	1