Calculate duration between two dates by excluding weekends in sqlserver

Question

I have table with name "fact_inc" with columns ticketId,status,opendate,closedate.
My requirement is to calculate weekday duration.
1. When status is inprogress then find difference between opendate minus getdate with out weekends.
Condition: incase if ticketid opened on Saturday or sunday then opendate should be consider as monday, then need duration between monday and getdate.
Ex: ticket id1 having opendate 2023-06-16 00:00:00(yyyy-mm-dd hhmmss), status is inprogress , result duration should be 4 days excluding weekends ,here im considering getdate as 2023-06-22 00:00:00.

Ex2 ticket id2 having opendate 2023-06-17 00:00:00(yyyy-mm-dd hhmmss), status is inprogress , then we need to calculate duration between monday( 2023-06-19 00:00:00) and today( 2023-06-22 00:00:00), result duration should be 3 days excluding weekends ,here im considering getdate as 2023-06-22 00:00:00.

Answer 1

You can try this. There can be some corner cases (eg. what if timestamp is not midnight, or the weekend days of 1 and 7 could be configured differently on the server.
Idea: use generate_series to create each day, and count how many of them fall on a weekday.
The last day is not counted.

https://onecompiler.com/sqlserver/3zca6asya

with range
as (select cast('2023-06-17' as date) start,
cast('2023-06-22' as date) stop
)
select count(*) as Duration_of_Weekdays
from generate_series(
0,
(select datediff(day, start, stop) - 1 from range)
)
where datepart(
weekday,
dateadd(day, value, (select start from range))
) not in ( 1, 7 )