Recursive query to determine worfklow with dependencies
Posted on 2013-11-14
I have table with widgets and a table with tasks. Each widget has one of each of the tasks, none get left out.
The tables look something like so:
Workflow_ID INT **Identity
Task_ID CHAR(5) **FK to tblTask
Parent_Task_ID CHAR(5) **FK to tblTask
The widgets each have a deadline of when they need to be completed. Several of the tasks can run in parallel, but cannot start if their parent task has not been completed. (i.e. has no value in Completion_Date_Actual)
I need a query that can backwards calculate the "maximum path", or the maximum length of time the longest line of tasks line of tasks. So, if we knew in advance that the longest path was 29 days, given a due date of 6/30/13, the query would return 6/29/13.
Thanks in advance,