Im trying to show how many users are each level versus how many total users have completed a level. I need the Unique User column to be a subtraction of two columns except at the Appr level I just need it to be a repeat of the user column because it's the highest level,
| CPNT_ID | Users | Unique Users |
|---|---|---|
| Trainee | 44662 | 11563 (which is 44662-33099) |
| SvcTech | 33099 | 12420 (33099-20679) |
| CrewChief | 20679 | 5079 (20679-15600) |
| SvcCoord | 15600 | 6010 (15600-9590) |
| Appr | 9590 | 9590 (this is the highest so no subtraction) |
select
cpnt.cpnt_id
,count ( distinct pc.stud_id ) users
from
pa_stud_program sp,
pa_program p,
pa_student s,
pa_stud_cpnt pc,
ps_program_type pt,
pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
and pc.compl_dte is not null
and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
and s.jp_id in ('1801','1805','1810','1811')
and s.EMP_STAT_ID = 'Active'
AND cpnt.CPNT_TYP_ID = p.CPNT_TYP_ID
AND cpnt.CPNT_ID = p.CPNT_ID
AND cpnt.REV_DTE = p.REV_DTE
AND pc.STUD_ID = sp.STUD_ID
AND sp.stud_id = s.STUD_ID
AND pc.CPNT_ID = sp.CPNT_ID
AND pc.CPNT_TYP_ID = sp.CPNT_TYP_ID
AND pc.REV_DTE = sp.REV_DTE
AND pc.seq_num = sp.seq_num
AND pt.PROGRAM_TYPE_ID = p.PROGRAM_TYPE
/** and s.PERSON_ID_EXTERNAL in [UserSearch]*/
group by cpnt.cpnt_id
order by users desc
Looks like a candidate for the lag analytic function.
SQL> with test as
2 -- sample data
3 (select 'Trainee' cpnt_id, 44662 users from dual union all
4 select 'SvcTech' , 33099 from dual union all
5 select 'CrewChief' , 20679 from dual union all
6 select 'SvcCoord' , 15600 from dual union all
7 select 'Appr' , 9590 from dual
8 )
9 -- subquery return "previous" CPNT_ID value which is then concatenated to its "pair"
10 -- joined on USERS value (as there's no other, at least not in your example)
11 select a.cpnt_id,
12 a.users,
13 a.cpnt_id || case when b.l_cpnt_id is not null then ' - '|| b.l_cpnt_id end
14 unique_users
15 from test a join (select cpnt_id,
16 users,
17 lag(cpnt_id) over (order by users) l_cpnt_id
18 from test
19 ) b on a.users = b.users
20 order by a.users desc;
CPNT_ID USERS UNIQUE_USERS
--------- ---------- ---------------------
Trainee 44662 Trainee - SvcTech
SvcTech 33099 SvcTech - CrewChief
CrewChief 20679 CrewChief - SvcCoord
SvcCoord 15600 SvcCoord - Appr
Appr 9590 Appr
SQL>
[EDIT, after you changed the requirement]
That's even simpler:
<snip>
11 select a.cpnt_id,
12 a.users,
13 a.users - nvl(b.l_users, 0) as unique_users
14 from test a join (select cpnt_id,
15 users,
16 lag(users) over (order by users) l_users
17 from test
18 ) b on a.users = b.users
19 order by a.users desc;
CPNT_ID USERS UNIQUE_USERS
--------- ---------- ------------
Trainee 44662 11563
SvcTech 33099 12420
CrewChief 20679 5079
SvcCoord 15600 6010
Appr 9590 9590
SQL>
Implementing that into your query: use a CTE:
with your_query as
(select
cpnt.cpnt_id
,count ( distinct pc.stud_id ) users
from
pa_stud_program sp,
pa_program p,
pa_student s,
pa_stud_cpnt pc,
ps_program_type pt,
pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
and pc.compl_dte is not null
and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
and s.jp_id in ('1801','1805','1810','1811')
and s.EMP_STAT_ID = 'Active'
AND cpnt.CPNT_TYP_ID = p.CPNT_TYP_ID
AND cpnt.CPNT_ID = p.CPNT_ID
AND cpnt.REV_DTE = p.REV_DTE
AND pc.STUD_ID = sp.STUD_ID
AND sp.stud_id = s.STUD_ID
AND pc.CPNT_ID = sp.CPNT_ID
AND pc.CPNT_TYP_ID = sp.CPNT_TYP_ID
AND pc.REV_DTE = sp.REV_DTE
AND pc.seq_num = sp.seq_num
AND pt.PROGRAM_TYPE_ID = p.PROGRAM_TYPE
group by cpnt.cpnt_id
)
select a.cpnt_id,
a.users,
a.users - nvl(b.l_users, 0) as unique_users
from your_query a join (select cpnt_id,
users,
lag(users) over (order by users) l_users
from your_query
) b on a.users = b.users
order by a.users desc;