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.
dbo. dates
| date | non-working day |
|---|---|
| 01/01/2022 | sat |
| 12/05/2022 | sun |
| 08/09/2022 | sun |
| 03/04/2022 | bank holiday |
dbo. activity
| open date | close date |
|---|---|
| 12/03/2022 | 03/07/2022 |
| 30/05/2022 | 01/09/2022 |
| 01/09/2022 | 01/10/2022 |
| 02/04/2022 | 01/05/2022 |
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...
If I understand you correct, you want to assign a 1 if there are less than 3 business days (not just any calendar days) between open and close dates. Assuming I got that right, could you not subtract the count of non-working days that fall between open and close dates from the diff to make sure it accounts for non-working days?
select a.opendate,
a.closedate,
case when datediff(day, a.opendate, a.closedate)-count(b.nonworkingday) <3 then 1 else 0 end as flag
from activity a
left join dates b on b.date between a.opendate and a.closedate
group by a.opendate, a.closedate