Aggregate path counts using HierarchyID

Posted by austincav on Stack Overflow See other posts from Stack Overflow or by austincav
Published on 2010-03-03T00:59:56Z Indexed on 2010/03/18 23:31 UTC
Read the original article Hit count: 244

Filed under:

Business problem - understand process fallout using analytics data.

Here is what we have done so far:

  1. Build a dictionary table with every possible process step
  2. Find each process "start"
  3. Find the last step for each start
  4. Join dictionary table to last step to find path to final step

In the final report output we end up with a list of paths for each start to each final step:

User   Fallout Step HierarchyID.ToString()
A      1/1/1
B      1/1/1/1/1
C      1/1/1/1
D      1/1/1
E      1/1

What this means is that five users (A-E) started the process. Assume only User B finished, the other four did not. Since this is a simple example (without branching) we want the output to look as follows:

Step   Unique Users
1      5
2      5
3      4
4      2
5      1

The easiest solution I could think of is to take each hierarchyID.ToString(), parse that out into a set of subpaths, JOIN back to the dictionary table, and output using GROUP BY.

Given the volume of data, I'd like to use the built-in HierarchyID functions, e.g. IsAncestorOf.

Any ideas or thoughts how I could write this? Maybe a recursive CTE?

© Stack Overflow or respective owner

Related posts about tsql