[ 수량 비율로 금액 배분 후 끝전 처리를 쿼리로 만들기 ]
1) cloth_cd별로 가지고 있는 수량 비율을 구해 금액을 나눈다( ratio_to_report 사용 )
cloth_cd의 rownumber와 마지막 로우를 찾기 위해 count(cloth_cd)를 구해 놓는다
round(d.othr_amt8 * round(ratio_to_report(a.out_qty) over(partition by a.cloth_cd), 6)) out_amt,
row_number() over(partition by a.cloth_cd order by a.out_qty) last_row,
count(a.cloth_cd) over(partition by a.cloth_cd) row_cnt
2) cloth_cd별로 마지막 row를 찾아 끝전을 계산하고 마지막 row에 반영한다
(case when last_row = row_cnt then
j.out_amt + (j.othr_amt8 - sum(j.out_amt) over(partition by j.cloth_cd order by j.out_qty))
else
j.out_amt
end ) out_amt
ㄱ) cloth_cd별 합계 : j.othr_amt8
ㄴ) cloth_cd별 누적 합계를 구한다
sum(j.out_amt) over(partition by j.cloth_cd order by j.out_qty)
ㄷ) 끝전을 구한다
j.othr_amt8 - sum(j.out_amt) over(partition by j.cloth_cd order by j.out_qty
3) 쿼리 예제
select substr(u.cloth_cd,1,1) brand,
sum(u.out_amt) out_amt,
u.revert_dept revert_dept,
u.cust_cd cust_cd,
u.busi_no busi_no
from (
select j.cloth_cd cloth_cd,
j.out_qty out_qty,
(case when last_row = row_cnt then
j.out_amt + (j.othr_amt8 - sum(j.out_amt) over(partition by j.cloth_cd order by j.out_qty))
else
j.out_amt
end ) out_amt,
// cloth_cd별로 rownumber 마지막 자릿수와 cloth_cd 최대 자릿수를 구해서
끝전 차이 부분을 마지막 row에 더해준다(cloth_cd 마지막 row에 끝전 반영)
j.revert_dept revert_dept,
j.cust_cd cust_cd,
j.busi_no busi_no
from (
select a.cloth_cd cloth_cd,
a.out_qty out_qty,
a.cust_cd cust_cd,
a.busi_no busi_no,
d.revert_dept revert_dept,
d.othr_amt8 othr_amt8,
-- cloth_cd별 out_qty(수량)이 가지고 있는 비율을 구함(소수 6자리까지)
round(d.othr_amt8 * round(ratio_to_report(a.out_qty) over(partition by a.cloth_cd), 6)) out_amt,
row_number() over(partition by a.cloth_cd order by a.out_qty) last_row,
count(a.cloth_cd) over(partition by a.cloth_cd) row_cnt
from (
select substr(p.tag_style_no,1,4) cloth_cd,
nvl(sum(o.out_qty),0) out_qty,
c.cust_code cust_cd,
c.business_no busi_no
from ta_wg020 o,ta_pp020 p, ta_wg021 c
where o.slip_date between ls_yymm||'01'
and to_char(last_day(to_date(ls_yymm, 'yyyymm')), 'yyyymmdd')
and o.expense_proc_gb = '2'
and o.acc_proc_type = '9'
and p.style_no = o.style_no
and p.season_year = o.season_year
and p.season_year >= '2000'
and p.season >= 'A'
and p.prod_gb in ('2','3','4','5')
and o.slip_date = c.slip_date
and o.part_div = c.part_div
and o.part_no = c.part_no
and o.part_type = c.part_type
and o.slip_no = c.slip_no
and o.slip_seq = c.slip_seq
group by substr(p.tag_style_no,1,4),
c.cust_code, c.business_no
) a, ( select x.cloth_cd cloth_cd,
max(x.revert_dept) revert_dept,
sum(x.othr_qty8) othr_qty8,
sum(x.othr_amt8) othr_amt8
from (
select cloth_cd cloth_cd,
'1000' revert_dept,
nvl(sum(othr_qty8), 0) othr_qty8,
nvl(sum(othr_amt8), 0) othr_amt8
from tm_sw22209
where comp_cd = parm_comp
and head_div = parm_head
and yymm = ls_yymm
and make_div = '2'
and cloth_cd not in ( select distinct cloth_cd
from tb_sw22210
where comp_cd = parm_comp
and out_div = '2'
and out_div_sb = '1'
and yymm = ls_yymm
and etc_div = '8')
group by cloth_cd
having nvl(sum(othr_amt8), 0) <> 0
union all
select cloth_cd cloth_cd,
revert_dept revert_dept,
nvl(sum(qty), 0) othr_qty8,
nvl(sum(amt), 0) othr_amt8
from tb_sw22210
where comp_cd = parm_comp
and out_div = '2'
and out_div_sb = '1'
and yymm = ls_yymm
and etc_div = '8'
group by cloth_cd,
revert_dept
having nvl(sum(amt), 0) <> 0
) x
group by x.cloth_cd ) d
where a.cloth_cd = d.cloth_cd
order by a.cloth_cd,
a.out_qty
) j
) u
group by substr(u.cloth_cd,1,1),
u.revert_dept,
u.cust_cd,
u.busi_no
order by substr(u.cloth_cd,1,1),
u.revert_dept,
u.cust_cd,
u.busi_no