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
tsql
Business problem - understand process fallout using analytics data.
Here is what we have done so far:
- Build a dictionary table with every possible process step
- Find each process "start"
- Find the last step for each start
- 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