Print Story MNS style
Music
By gzt (Tue Jul 08, 2008 at 06:38:13 AM EST) gzt, graph (all tags)
In light of my recent failure, I've felt like throwing up for the past couple days, so I may stop eating.

Inside: a super fun happy graph puzzle.



Suppose you have a few thousand employees and you have the following two lists: a list of their ID#s and the ID#s of who they report to and a list of who is a Vice President. Everybody reports to one and only one person and everybody eventually traces up to $FEARLESS_LEADER. VPs may report to VPs.

My question is: what's the best way to convince people that the way they're thinking about slicing up stuff by VP is stupid and not well-defined? I mean, I know how to make a recursive algorithm to do the numbers here, my main interest in school outside of the purest of maths was algorithms and graph theory aka this.

< Hang spring cleaning | ATTENTION blixco FAN INFIDELS >
MNS style | 37 comments (37 topical, 0 hidden) | Trackback
Get to your doctor for a medical marijuana by georgeha (4.00 / 2) #1 Tue Jul 08, 2008 at 06:48:42 AM EST
prescription, it will help your appetite. It should help any UNIX work you do, too.


Pot is so retro. by ObviousTroll (4.00 / 1) #2 Tue Jul 08, 2008 at 06:53:26 AM EST
These days, doctors are prescribing Fuckitol - the antistress medication for the modern age.

I haven't punched out a single supervisor since my doctor put me on Fuckitol!


--
Has anybody seen my clue? I know I had it when I came in here.

[ Parent ]
I prefer Damnitol by georgeha (2.00 / 0) #3 Tue Jul 08, 2008 at 06:55:34 AM EST
I like stress.


[ Parent ]
I thought that would upset me but... by ObviousTroll (2.00 / 0) #6 Tue Jul 08, 2008 at 07:06:56 AM EST
I can't seem to care.

Besides, you've got so much stress in your life I don't think you'd recognize stress-free living if you fell into a hot tub full of money.

--
Has anybody seen my clue? I know I had it when I came in here.

[ Parent ]
I think Fuckitol is only available by wiredog (2.00 / 0) #8 Tue Jul 08, 2008 at 07:23:50 AM EST
To military vets who have had recovered from cases of the fukkets.

Earth First!
(We can strip mine the rest later.)

[ Parent ]
I believe you are looking for . . . by slozo (2.00 / 0) #37 Wed Jul 09, 2008 at 02:45:54 AM EST
. . . this:



[ Parent ]
Do VPs have ID#s? by debacle (2.00 / 0) #4 Tue Jul 08, 2008 at 07:00:39 AM EST
Also "the following two lists" does not correlate with the three lists you presented.

I don't see a problem with:

Emp ID | Superior Emp ID | Is VP?

That's a fairly simple and straightforward data structure.


IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

right, well, clarification by gzt (4.00 / 1) #5 Tue Jul 08, 2008 at 07:06:07 AM EST
list one:
ID# , Reports to ID#

list two:
ID# (only VPs)

I mean, yeah, this can be made one list:
ID#, Reports to ID#, Is VP?

One of the easy questions they want to ask is, "How many people are in this VP's organization?" That's a simple recursive algorithm. But they want to do other stuff. And that's not going to be well-defined because it's only going to be easy to do if each person only goes up through one VP. But they don't. VPs can report to other VPs and their organizations would roll up to theirs. I'm struggling to explain this to them.

[ Parent ]
Still doesn't seem to be a problem. by debacle (2.00 / 0) #7 Tue Jul 08, 2008 at 07:21:36 AM EST


IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
well, let me put it like this by gzt (2.00 / 0) #10 Tue Jul 08, 2008 at 07:35:29 AM EST
For our purposes, this is only useful if we have it as a field in a database. Anything else, well, it's just not going to be very friendly to slice and dice properly and make reports. What we really have is:
ID | Reports to ID | Is VP? | (Bunch of important data that we want to have split by each VP's organization)

Well, if each person, on the path you trace up from them to MASTER OF THE UNIVERSE only encountered one VP, you could just add a field in there (let's call it Reports to VP). But they don't. There are places in the company where one VP has an organization of 200 people, and it's split between 2 different VPs. And they want to be able to report on all three organizations like this. Well, we're in a database, one field can't have two values. Sure, you can get that data, there are simple algorithms for that, but with the resources we have (and that's the big one!), it's not going to be good for us unless, essentially, we can have something as a field in the database.

[ Parent ]
I think I get it now. by nightflameblue (2.00 / 0) #14 Tue Jul 08, 2008 at 07:48:10 AM EST
You could have a person reporting to more than one VP? Or multiple VPs in charge of one department/area?

Gonna have to create new tables if that's the case.

Something like:

  • VP Groups (ID, Name)
  • VP Group Members (Tie to Group ID, Tie to Individual ID)

Then you'll have to add a field to the main employee chart to cover if they are an admin member of a VP group. Everyone else reports to a VP group instead of a VP directly.

Unless I'm totally misunderstanding the entire situation. But you're saying a single field can't contain more than one value, that's when you gotta break out new tables and cross-reference. It's the only way.

[ Parent ]
yeah, you got it, here's the problem: by gzt (2.00 / 0) #16 Tue Jul 08, 2008 at 08:10:42 AM EST
Suppose one has this chain of command in a certain part of the company:
CEO > VP A > VP B > VP C > Managers etc etc etc.

They would want a report for VP A, a report for VP B, and a report for VP C of all the people stuff. VP C rolls up into VP B, of course, and VP B rolls up into VP A. It's a simple algorithm to count the number of people, sure, but we want more than that, and the only real way to do this is by having something in the database that we can slice by. So we'd need a table of some sort. There won't be a table. We could try to hack one together, but, no, we won't.

[ Parent ]
Ah, a forced coding hack. by nightflameblue (2.00 / 0) #18 Tue Jul 08, 2008 at 08:21:23 AM EST
My favorite.

Hopefully you won't be around when the question is raised who cooked up this stupid hack.

[ Parent ]
simple algorithms? by debacle (2.00 / 0) #15 Tue Jul 08, 2008 at 08:07:37 AM EST
Why not just do an outside join on VPs? You could do all of this in a single query.

IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
Well by gzt (2.00 / 0) #19 Tue Jul 08, 2008 at 08:34:33 AM EST
Here's the thing:
Five VPs report to VP Bob. Each of those VPs has five managers beneath them with 10 employees each. So they want to generate six reports, one for VP, about the people beneath them. Dick the Data Entry Guy needs to pop up on VP Bob's report and the report for the VP beneath him. And multiply that by 500, so it all has to be automatic.

[ Parent ]
Strange thing is by debacle (2.00 / 0) #20 Tue Jul 08, 2008 at 09:59:27 AM EST
SQL can do automatic type stuff.

Amazing, I know.


IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
Well, strange thing. by gzt (2.00 / 0) #21 Tue Jul 08, 2008 at 10:11:32 AM EST
Everybody says it seems easy and that you can easily write a query to do it, but, uh, nobody can give up the goods. I mean, I'd like to see you write up some mock SQL to do what I'm after. You have a very simple data structure. One table people, four fields, ID, reportsto, IsVP, and, say, salary, and what you want is a list of VPs with the sum of the salaries of everybody beneath them in the organization (not necessarily directly reporting to them, they can be 5 levels down from them). Then again, I'd hate to be asking you to do my job for me, so you only need to tell me when and if you get a solution. I admit, I'm not a SQL guy, I'm not a programmer, so you could be right.

[ Parent ]
A few simple queries by debacle (2.00 / 0) #22 Tue Jul 08, 2008 at 10:34:38 AM EST
I'm not excellent at SQL, but basically when you've got your list of VPs, you iterate over each of them recursively adding up the totals for each. (I have no idea how to do recursion in SQL).

It would look like this:

$vps = mysql('SELECT * FROM people WHERE IsVP = 1');

foreach $vps as $vp:
  $salary_total = 0;
  foreach get_all_children($vp) as $child:
    $salary_total = $salary_total + $child['salary'];
  endforeach;
  print('Salary total: ' . $salary_total);
endforeach;

function get_children($employee) {
  return mysql('SELECT * FROM people WHERE reportsto = ?', ($employee));
}

function get_all_children($employee) {
  $children = get_children($employee);
  if $children:
    foreach $children as $child:
      $children = $children + get_all_children($child); // some sort of array concatenation operation.
    endforeach;
  endif;

  return $children;
}

I am confident that all of that can be done in a single, more elegant SQL query.


IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
I don't think standard SQL supports hiearchies by lm (2.00 / 0) #24 Tue Jul 08, 2008 at 10:51:48 AM EST
I know for certain that Microsoft has passable extensions to support hierarchical data. Other vendors probably do as well.

But with recursion, you also have to be certain that there are no loops in your data. Otherwise, you're query may never finish.


There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
I would hope (to god) by debacle (2.00 / 0) #26 Tue Jul 08, 2008 at 11:04:25 AM EST
That there was no infinite loop in an employment hierarchy.

If I were my boss's boss's boss...that'd be fucked up.

It really wouldn't be a hierarchy so much as a recursive function on a dataset, which I know SQL can do.


IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
one would hope, but ... by lm (2.00 / 0) #27 Tue Jul 08, 2008 at 11:09:04 AM EST
If the application doesn't enforce the business rules, somewhere, at some point in time, someone is going to put the data in wrong.

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
Catastrophic failure is a moral lesson by debacle (2.00 / 0) #29 Tue Jul 08, 2008 at 11:30:34 AM EST
Don't enforce data protections during output, handle it on input.

IF YOU HAVE TWO FIRLES THOROWNF MONEY ART SUOCIDE GIRLS STRIPPER HPW CAN YPUS :OSE?!?!?!?(elcevisides).

[ Parent ]
Do you have Crystal Reports? by lm (2.00 / 0) #23 Tue Jul 08, 2008 at 10:42:57 AM EST
Report -> Hierarchical Grouping Options

If the DBMS you're using supports recursion, you could write a stored procedure to do what you need. Some DBMSs will have hierarchical querying options. If you're running MS SQL, the online help will have an example of expanding a hierarchy. Presumably the same is true of Oracle and the like.


There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
No. We have no tools. by gzt (2.00 / 0) #25 Tue Jul 08, 2008 at 10:53:25 AM EST
This would be a different story if we were able to use any sort of real tools. We dump from peoplesoft into spreadsheets. PeopleSoft Query is very limited. No outer joins, no "HAVING" clauses, etc. So we usually dump into Excel plus VBA or use Access (ugh).

[ Parent ]
You have Access? by lm (2.00 / 0) #28 Tue Jul 08, 2008 at 11:24:54 AM EST
Recursive Joins to Query Data Hierarchies in Microsoft Access

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
Access? by ad hoc (4.00 / 1) #30 Tue Jul 08, 2008 at 11:54:10 AM EST
I thought this was a database problem.
--
The three things that make a diamond also make a waffle.
[ Parent ]
Like it or not, Access is a DBMS by lm (2.00 / 0) #32 Tue Jul 08, 2008 at 01:02:16 PM EST
Not a particularly good DBMS, mind you, but a DBMS nontheless. And this isn't really a database problem so much as a data presentation problem. A DBMS is meant for storage and retrieval, not presentation.

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
well... by gzt (2.00 / 0) #33 Tue Jul 08, 2008 at 01:05:44 PM EST
This is a retrieval problem of sorts. Get me $INFORMATION by Vice President! The precursor problem (Get me $INFORMATION by Division!) is purely on the database end, since that's straight data retrieval. Well, we do some stuff to that information, but that's neither here nor there. The point is, the hitch is the database part, the dumping-data-into-our-laps part.

[ Parent ]
That link should do you, then by lm (2.00 / 0) #36 Tue Jul 08, 2008 at 01:21:44 PM EST
Presumably you know how to connect Access to the database. That link explains how to get Access to arrange the data into a hierarchy.

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
Sounds like by ad hoc (2.00 / 0) #34 Tue Jul 08, 2008 at 01:18:13 PM EST
a retrieval/query problem. Presentation is immaterial. Constructing the query or retrieval code seems to be the problem.
--
The three things that make a diamond also make a waffle.
[ Parent ]
you're right by lm (2.00 / 0) #35 Tue Jul 08, 2008 at 01:20:16 PM EST
Nothing furTher

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
Good grief. by nightflameblue (2.00 / 0) #31 Tue Jul 08, 2008 at 12:57:30 PM EST
MAKE THE WORLD WITH NO TOOLS AND DO IT YESTERDAY!

I feel your pain.

[ Parent ]
Does a VP only report to one other VP? by nightflameblue (2.00 / 0) #9 Tue Jul 08, 2008 at 07:29:18 AM EST
Or can it be many. If it's VP->Other VP->Other VP->Big kahuna it's easy. If it's:
VP  ->Other VP->Other VP->Big Kahuna
    | ->Other VP-|
    | ->Other VP-|
    | ->Other VP-|

Well then, that gets somewhat more complicated.

Org charts were once one of my specialties. But it's been a while. But any organizational structure should be chartable. If it isn't, that's a pretty good sign that somebody fucked up on a who-reports-what-to-who level somewhere. I've been in businesses where the very idea that you can't chart out the structure changes is enough to make them rethink the entire thing. I'm guessing that's not the case here.

[ Parent ]
It's chartable. by gzt (2.00 / 0) #12 Tue Jul 08, 2008 at 07:40:45 AM EST
Like I said, it can all be written out as a directed graph emanating from MASTER OF THE UNIVERSE. Merely counting people is easy, then, it's a trivial algorithm. The problem is that we want to do our current reporting (which splits down to divisions, which is slightly tricky but it is a field in the database and roughly reflects the major splits) and drill that down to VP level. The problem is that that is not well-defined.

[ Parent ]
But there is only one fearless leader, right? by lm (2.00 / 0) #11 Tue Jul 08, 2008 at 07:39:28 AM EST
Which means that this reduces to the easy case of each person only going up through one VP. Just instead of VPs in general, your top level set consists of those individuals that directly report to the fearless leader.

There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
That's not what they want. by gzt (2.00 / 0) #13 Tue Jul 08, 2008 at 07:41:38 AM EST
They already have that, essentially.

[ Parent ]
Then how is the easy case the easy case? [nt] by lm (2.00 / 0) #17 Tue Jul 08, 2008 at 08:18:20 AM EST


There is no more degenerate kind of state than that in which the richest are supposed to be the best.
Cicero, The Republic
[ Parent ]
MNS style | 37 comments (37 topical, 0 hidden) | Trackback