Re: Full outer join equivalence with aggregate window function

I will explain the rollup issue of sales hierarchy here. Suppose we have four levels in sales hiearchy, namely company -> division -> rep -> member, and we are tracking performance from the rep level up with KPI's of member count and member gain. Assume we have divisions d1 and d2 (divisions won't change over time), and reps under d1 over time as:
at t1: r1, r2, r3 with member counts 10, 10, 10 at t2: r2, r3, r4 with member counts 20, 20, 20 without the full join I mentioned previously, we will have KPI's: at t2: r2, r3, r4 with member gains 10, 10, 20 (by taking zero for null previous period value), notice that r1 is missing let's see what happens to division d1 at t2: d1's member counts are 30 (10+10+10) for t1 and 60 (20+20+20) for t2 d1's true member gain is 60 - 30 = 30
however, rolling up from rep to division gives a member gain as 40 (10+10+20)
Obviously, this naturally results from the missing of r1 at t2

My thinking is to add back r1 at t2 (member_cnt is null, prev_member_cnt is not null) so that member gain has full addability rolling up the sales hierarchy, r1 will be excluded from the final query result by a flag such as "is_virtual", it is present for sole purpose of correct roll up. Of course, there probably are other ways to solve this.

I hope this clerifies things. Received on Sun Jan 29 2006 - 19:33:59 CST