Process Duration in days, excluding weekends and holidays
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?
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,126 Neuron
This SO article should help:
https://stackoverflow.com/questions/252519/count-work-days-between-two-dates