Full outer join equivalence with aggregate window function

From: Dino Hsu <dino_hsu_1019_at_yahoo.com>
Date: 29 Jan 2006 01:46:38 -0800
Message-ID: <1138527998.412871.134900@g43g2000cwa.googlegroups.com>

Dear all,

A sales performance table is like:
create table t1 (period number, rep_id number, member_cnt number, constraint pk_t1 primary key (period, rep_id)); where member_cnt is the ending number of members for a specific sales representative at a specific period. (the sales representative owns the members)

The followint SQL uses aggregate window function lag() to get the member counts and member gains (the difference of current period member count and previous period one) for periods n1 to n2 for all representatives:
select

    period,
    rep_id,
    member_cnt,
    prev_member_cnt,
    member_gain
from

    (select

        period,
        rep_id,
        member_cnt,
        lag(member_cnt,1) over (partition by rep_id order by period)
prev_member_cnt,
        member_cnt - nvl(lag(member_cnt,1) over (partition by rep_id

order by period),0) member_gain

    from t1
    where period between n1-1 and n2
    )
where period between n1 and n2;

However, representatives change over time, new representatives join the company while old ones left the company at each period. For example, if there are representatives r1, r2, r3 in period 1, and r2, r3, r4 in period 2, applying the above SQL to periods 1 to 2 results in: Period 1: r1, r2, r3
Period 2: r2, r3, r4
What I need is
Period 2: r1, r2, r3, r4
Although r1 is out in period 2, I need the prev_member_cnt so that I can roll up levels of sales hierarchy consistently, this is similar to a full outer join across periods 1 and 2. Anyone any ideas? Thanks in advance.

If my point is not clear enough, please let me know, thanks. Received on Sun Jan 29 2006 - 03:46:38 CST