select     
b.P_code,    
b.O_code,    
c.qty b_code,    
b.GB_code,    
max ( decode (a.WEEK_1, b.WEEK, b.qty)) ww01,    
max ( decode (a.WEEK_1+1, b.WEEK, b.qty)) ww02,    
max ( decode (a.WEEK_1+2, b.WEEK, b.qty)) ww03,    
max ( decode (a.WEEK_1+3, b.WEEK, b.qty)) ww04,    
max ( decode (a.WEEK_1+4, b.WEEK, b.qty)) ww05    
from     
(    
select min(to_char(to_date(mc_date,'yyyymmdd'),'yyyyww')) WEEK_1    
from CD_DAY    
where year||month= '201701'   
order by WEEK_1    
)a,(    
    select    
            P_code,    
            O_code O_code,    
            '1TEST' GB_code,    
            WEEK,    
            round(sum(c_qty),0) as qty    
    from rap_table_T    
        where P_day <> '201701'   
        and substr(P_day,1,6) = '201701' 
and open_date = (select max(open_date) from rap_table_T WHERE SUBSTR(P_day,1,6) ='201701')  
group by P_code, O_code, WEEK                                   
union    
    select    
            P_code,    
            O_code O_code,    
            '2TEST' GB_code,    
            WEEK,    
            round(sum(a_qty),0) as qty    
    from rap_table_T    
        where P_day <> '201701'     
            and substr(P_day,1,6) = '201701' 
and open_date = (select max(open_date) from rap_table_T WHERE SUBSTR(P_day,1,6) ='201701')  
group by P_code, O_code, WEEK          
union    
select    
P_code,    
O_code O_code,    
'3TEST'GB_code,    
WEEK,    
NVL(ROUND(LEAST(n_a_qty / NULLIF(n_c_qty, 0), 1) * 100, 0), 0) as qty    
from(    
select    
P_code,    
O_code,    
WEEK,    
nvl(sum(c_qty) over(partition by O_code,substr(WEEK,1,4) order by WEEK),0) n_c_qty,    
nvl(sum(a_qty) over(partition by O_code,substr(WEEK,1,4) order by WEEK),0) n_a_qty    
from(    
    select    
            P_code,    
            O_code,    
            WEEK,    
            nvl(sum(c_qty),0) c_qty,    
            nvl(sum(a_qty),0) a_qty    
            from rap_table_T    
            where substr(P_day,1,6) = '201701' 
and open_date = (select max(open_date) from rap_table_T WHERE SUBSTR(P_day,1,6) ='201701')  
group by P_code, O_code, WEEK    
            order by WEEK           
)    
)    
)b,(    
     select     
             O_code O_code,    
             'b_code' b_code,    
             '2TEST'GB_code,    
             sum(a_qty) as qty    
     from rap_table_T    
     where b_code='STAT' 
        and substr(P_day,1,6) = '201701' 
and open_date = (select max(open_date) from rap_table_T WHERE SUBSTR(P_day,1,6) ='201701')  
group by O_code          
     )c    
where b.O_code = c.O_code (+)    
and b.GB_code = c.GB_code(+)    
group by b.P_code, b.O_code,b.GB_code, c.qty   

데이터입니다

T O TAL을 구하려고하는데 일반 판매 율이 해당 주차에 100이거나, 100이.상은 제외하고

100이하 만 따로 1TEST SUM 2TEST SUM해서

판매 율을 구하고싶은데..

그냥 각 TEST1마다 전부 계산식넣어서 

<>100 을 제외하면 될 것 같은데 너무 하드코딩이라

다른방법이 혹시 있나.. 글을 남깁니다

좀 처럼 아이디어가 나질않네요..

도움부탁드려요