0

Calculate duration between two dates by excluding weekends in sqlserver

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.

21st Jun 2023, 4:41 PM
Vijay
Vijay - avatar
1 ответ
+ 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 )
21st Jun 2023, 8:03 PM
Tibor Santa
Tibor Santa - avatar