Re: Full outer join equivalence with aggregate window function

Dino Hsu wrote:
> 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.

Translucent at best. What are the business rules? If someone leaves but the transaction was in the current period is it included? Without the business rules we are just playing guessing games.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)