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?
