Can someone please help. Sorry I have re-edited the question!

I need to write a select statement which says if the date range between dbo.activity.OpenDate and dbo.activity.CloseDate is less then 3 days Then 1 else 0. However I cannot include a non-working day. Therefore if it is a weekend or BH then it will change the calculation. All the non working days and format are in dbo.dates . Both tables are below.

Thank you

Thanks you friends:) .

dbo. dates

dbo. activity

expected output

A series of '1's or '0's if the criteria is met. The '1's and '0's can then be summed readily in the reporting medium...

CodePudding user response：

We could use a left anti-join approach here:

SELECT DISTINCT a.[open date], a.[close date],
       CASE WHEN d.date IS NULL THEN 0 ELSE 1 END AS flag
FROM activity a
LEFT JOIN dates d
    ON d.date BETWEEN a.[open date] AND a.[close date];

CodePudding user response：

select [open date], [close date],
   case when exists (select * from dates d
                     where d.[date] between a.[open date] and a.[close date])
        then 1 else 0 end as NonWorkingDayExists
from activity a;

EDIT: Rereading your revised question:

SELECT 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  OpenDate, CloseDate;

However, your dates makes me think those sun, sat might mean weekends which then unnecessary store like that. You might instead get it checking date:

SELECT activityId, OpenDate, CloseDate, 
    CASE WHEN SUM(CASE WHEN wd < 2 OR holiday.[date] IS NOT NULL THEN 1 END) < 3 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;

I added DBFiddle demo here.