Process Duration in days, excluding weekends and holidays

MJL

I have a use case that requires me to calculate the duration of a process, in business days, for each day that the process is active.

I have a table that identifies the ID and the begin and end date of instances of processes (~600,000 rows). I have a date dimension table that contains one row for each date from the beginning date of the earliest process instance to tomorrow and an indicator noting whether the date is a business day or not.

I've joined these tables together so that I have a row for each day between the start and end dates of the process. The resulting table is ~ 10,000,000 rows

I now want to calculate the process duration, in business days, for each of those rows. My data is in SQL Server.

Does anyone have a solution for this?

Turribeach

This SO article should help:

https://stackoverflow.com/questions/252519/count-work-days-between-two-dates