팀별 예산 테이블
조직코드 | 사업비예산 |
A0001 | 100000 |
A0002 | 120000 |
A0003 | 130000 |
A9999 | 10000 |
B0001 | 110000 |
B0002 | 130000 |
B9999 | 20000 |
A9999, B9999는 각팀의 상위 부서의 예산 임.
그러나 실질적으로 가용 가능한 예산을 팀별로 다시 확인 하면
조직코드 | 사업비예산 | 상위부서 | 실제사용가능예산 |
A0001 | 100000 | 10000 | 103333.3333 |
A0002 | 120000 | 10000 | 123333.3333 |
A0003 | 130000 | 10000 | 133333.3333 |
B0001 | 110000 | 20000 | 120000 |
B0002 | 130000 | 20000 | 140000 |
A9999, B9999 부서에 배정된 예산을 하위 팀의 갯수로(실제로는 갯수로 나누는 팀과 정해진 비율로 나누는 경우 2가지 임)
나누어 팀별 예산에 합하여 가용예산을 다시 계산 하여야 합니다.
물론 조직 테이블에
A0001 A9999
A0002 A9999 이런 식으로 되어 있기는 합니다.
이걸 한번에 계산을 하려 하니 팀코드와 부서코드가 다른 행에 있어 쉽지가 않네요 그렇다고 SUM, UNION ALL로 하면 조직이 변경 되었을때 엄청난 수작업이 발생 할 듯 하여
고수님들의 지도 부탁드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | WITH t_budget AS ( SELECT 'A0001' cd, 100000 bg FROM dual UNION ALL SELECT 'A0002' , 120000 FROM dual UNION ALL SELECT 'A0003' , 130000 FROM dual UNION ALL SELECT 'A9999' , 10000 FROM dual UNION ALL SELECT 'B0001' , 110000 FROM dual UNION ALL SELECT 'B0002' , 130000 FROM dual UNION ALL SELECT 'B9999' , 20000 FROM dual ) , t_org AS ( SELECT 'A0001' cd, 'A9999' p_cd FROM dual UNION ALL SELECT 'A0002' , 'A9999' FROM dual UNION ALL SELECT 'A0003' , 'A9999' FROM dual UNION ALL SELECT 'A9999' , '' FROM dual UNION ALL SELECT 'B0001' , 'B9999' FROM dual UNION ALL SELECT 'B0002' , 'B9999' FROM dual UNION ALL SELECT 'B9999' , '' FROM dual ) SELECT p_cd , cd , p_bg , bg , bg + ROUND(p_bg / cnt, 4) t_bg FROM ( SELECT a.p_cd, a.cd, b.bg , SUM (NVL2(a.p_cd, 0, b.bg)) OVER(PARTITION BY NVL(a.p_cd, a.cd)) p_bg , COUNT (*) OVER(PARTITION BY NVL(a.p_cd, a.cd)) - 1 cnt FROM t_org a LEFT OUTER JOIN t_budget b ON a.cd = b.cd ) WHERE p_cd IS NOT NULL ORDER BY p_cd, cd ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | with t_amt as ( select 'A0001' as code, 100000 as amt union all select 'A0002' as code, 120000 as amt union all select 'A0003' as code, 130000 as amt union all select 'A9999' as code, 10000 as amt union all select 'B0001' as code, 110000 as amt union all select 'B0002' as code, 130000 as amt union all select 'B9999' as code, 20000 as amt ) ,t_code as ( select 'A0001' as code, 'A9999' as upcode union all select 'A0002' as code, 'A9999' as upcode union all select 'A0003' as code, 'A9999' as upcode union all select 'A9999' as code, null as upcode union all select 'B0001' as code, 'B9999' as upcode union all select 'B0002' as code, 'B9999' as upcode union all select 'B9999' as code, null as upcode ) select t1.code, t1.upcode , t2.amt, t3.amt, t1.ct , t2.amt + t3.amt / t1.ct from ( select t1.code, t1.upcode , count (1) over(partition by upcode) as ct from t_code t1 where upcode is not null ) t1 left outer join t_amt t2 on t1.code = t2.code left outer join t_amt t3 on t1.upcode = t3.code |
상위 코드값 테이블이 있다는 가정으로 만들어봤습니다.