I have a reasonably large datatable structured something like this:

 StaffNo    Grade    Direct   Boss2    Boss3    Boss4    Boss5    Boss6
 -------    -----    -----    -----    -----    -----    -----    -----
 10001        1      10002    10002    10057    10094    10043    10099
 10002        2      10057    NULL     10057    10094    10043    10099
 10003        1      10004    10004    10057    10094    10043    10099  
 10004        2      10057    NULL     10057    10094    10043    10099  
 10057        3      10094    NULL     NULL     10094    10043    10099

etc....

i.e. a unique id , their level (grade) in the hierarchy, a record of their bosses ID and the IDs of the supervisors above. (The 2,3,4, etc refers to the boss at that particular grade).

The system relies on a strict hierarchy - if you are my boss (/parent) then your boss must be my grandparent.

Unfortunately this rule is not enforced within the data model and the data ultimately comes from other systems which don't even know about the rule, let alone observe it. So you and I may share the same boss, but our bosses boss won't be the same.

note:

• I cannot change the data model
• I cannot fix the data at source.

So (for the moment) I have to fix the data once it's in place. Once a fortnight someone will do something which breaks the model and I'll need to modify the procs slightly to resolve. Not ideal, but I'm stuck with this for the next six months.

Anyway, specific queries are easy to produce but I find it hard to keep track of the bigger picutre. The application which sits on this runs without complaint regardless but navigating around the system becoming extraordinarily confusing. So my question is:

• Can anyone recommend a tool (or technique) for generating some kind of "broken tree" diagram in this sort of circumstances?

I don't want something that will fix things for me, or attempt statistical analysis but at least something that will give a visual indication of how broken it is at any one time.

Note : At the moment this is in a SQL Server database but I'm open to ideas utilising C#, Perl or Python.