CREATE TABLE HCDT_ACTIVITYCOSTSUM
(YYMM_YM VARCHAR2(6),
SITE_CD VARCHAR2(6),
DEPT_CD VARCHAR2(6),
ACTIVITY_CD VARCHAR2(6),
CHG_TY VARCHAR2(20),
BUDGET_CD VARCHAR2(8),
AMT_AM NUMBER,
ACCOUNT_CD VARCHAR2(6));
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63021000',891400,'630210');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031104',145000,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031201',504400,'630312');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63039901',498000,'630399');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031105',130974,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031106',172671,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031117',199509,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031118',415049,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031301',454371,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031302',546924,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031801',560342,'630318');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032303',114270,'630323');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032401',297352,'630324');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES ('199802','111','A00100','112101','C5','63032505',107242,'630325');
CREATE TABLE HCDT_OUTPUTQTY
(YYMM_YM VARCHAR2(6),
SITE_CD VARCHAR2(6),
DEPT_CD VARCHAR2(6),
ACTIVITY_CD VARCHAR2(6),
DRIVER_CD VARCHAR2(6),
QTY_QN NUMBER,
UNIT VARCHAR2(20));
INSERT INTO HCDT_OUTPUTQTY VALUES ('199802','111','A00100','112101','112101',900,NULL);
REPORT 형식 맞추기
건
다음 테이블은 활동에대한 금액을 가지고 있다.
DESC HCDT_ACTIVITYCOSTSUM
열 이름 널? 유형
------------------------------ -------- ----
YYMM_YM NOT NULL VARCHAR2(6) <-- 년월
SITE_CD NOT NULL VARCHAR2(6) <-- 사업장
DEPT_CD NOT NULL VARCHAR2(6) <-- 부서
ACTIVITY_CD NOT NULL VARCHAR2(6) <-- 활동
CHG_TY NOT NULL VARCHAR2(20) <-- 구분
BUDGET_CD NOT NULL VARCHAR2(8) <-- 항목
AMT_AM NUMBER <-- 활동금액
ACCOUNT_CD NOT NULL VARCHAR2(6) <-- 계정
YYMM_Y SITE_C DEPT_C ACTIVI CHG_TY BUDGET_C AMT_AM ACCOUN
------ ------ ------ ------ --------------- -------- ---------- ------
199802 111 A00100 112101 C1 63021000 79140000 630210
199802 111 A00100 112101 C1 63031104 1750000 630311
199802 111 A00100 112101 C1 63031201 500400 630312
199802 111 A00100 112101 C1 63039901 498000 630399
199802 111 A00100 112101 C5 63031105 13081974 630311
199802 111 A00100 112101 C5 63031106 15172671 630311
199802 111 A00100 112101 C5 63031117 3199509 630311
199802 111 A00100 112101 C5 63031118 15049 630311
199802 111 A00100 112101 C5 63031301 4454371 630313
199802 111 A00100 112101 C5 63031302 5866924 630313
199802 111 A00100 112101 C5 63031801 56980342 630318
199802 111 A00100 112101 C5 63032303 1197270 630323
199802 111 A00100 112101 C5 63032401 29721352 630324
199802 111 A00100 112101 C5 63032505 1073242 630325
다음 테이블은 활동에대한 활동량을 가지고 있다.
DESC HCDT_OUTPUTQTY
열 이름 널? 유형
------------------------------ -------- ----
YYMM_YM NOT NULL VARCHAR2(6) <-- 년월
SITE_CD NOT NULL VARCHAR2(6) <-- 사업장
DEPT_CD NOT NULL VARCHAR2(6) <-- 부서
ACTIVITY_CD NOT NULL VARCHAR2(6) <-- 활동
DRIVER_CD NOT NULL VARCHAR2(6) <-- 활동량
QTY_QN NUMBER <-- 단위
UNIT VARCHAR2(20)
YYMM_Y SITE_C DEPT_C ACTIVI DRIVER QTY_QN UNIT
------ ------ ------ ------ ------ ---------- --------------------
199802 111 A00100 112101 112101 900
문제.
활동/산출물/활동원가,활동량,활동단가구분 에 따라 주어진 기간 사이의
해당값 들을 월별로 보여주고 제일 앞에 합계를 보여준다.
ACTIVI DRIVER G_KEY G_DESC 합계 M M+1 M+2 M+3 M+4 M+5 M+6 M+7 +8 M+9 M+10 M+11
------ ------ ------ -------- ------- ------ ------- ------ ------ ------ ------ ------ ------ ----- ------ ------ ------
112101 112101 1 활동원가 5037504 5037504 112101 112101 2 활동량 900 900
112101 112101 3 활동단가 5597 5597
활동2 산출물2 1 활동원가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동2 산출물2 2 활동량 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동2 산출물2 3 활동단가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 1 활동원가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 2 활동량 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 3 활동단가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
와 같은 형식의 결과를 나오게 하면된다.
이때 활동단가 = 활동원가/활동량 이다
합계란은 1번째 값부터 12번째 값의 합이며
합계 활동단가 = 합계활동원가/합계활동량 이다
첫월과 마지막월이 12개월을 초과할 시는 첫월을 기준으로
12개월만큼의 DATA만을 이용하여 결과를 보여준다.
결과의 이탤릭체 부분은 가상 데이타 이다.
생각.
한눈에 결과를 봐도 구현하는데 가장 문제가 될듯한 부분은 합계를 가로가 아닌
세로축에 보여 주어야 한다는 것이다.
이미 월별로 값을 COLUMN으로 나열하는 기법이나,구분자를 두어 값을 ROW형식으로
보여주는 기법은 앞 주제에서 다루었다.
이번에는 월별 값을 ROW형식에서 COLUMN형식으로 바꿔주고,동시에 구분에 따라
활동원가/활동량/활동단가 를 ROW형식으로 보여주는 기법이 모두 필요하다.
합계를 세로축에 보여주는 것도 가로축에 합계를 보여주는 기법을 약간만
변형하면된다.
해법.
단계1.먼저 활동원가를 가져오기 위하여 HCDT_ACTIVITYCOSTSUM 테이블에서 CHG_TY
를 배제하고 SUM을 한다.
SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
결과는 아래와 같다.
YYMM_Y SITE_C DEPT_C ACTIVI AMT_AM
------ ------ ------ ------ ----------
199802 111 A00100 112101 212651104
이때
LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),11),'YYYYMM'))
부분은 시작월을 기준으로 12개월을 초과할 경우 12개월분 DATA만을
읽어오도록 하기위한 문장이다.
단계2.단계1의 결과를 이용해 HCDT_OUTPUTQTY 와 JOIN을 걸어 원가/량/단가를 모두 구한다.
SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER AMT_AM QTY_QN UNIT_COST
------ ------ ------ ---------- ---------- ----------
199802 112101 112101 212651104 900 236279.004
단계3.단계2의 결과를 이용해 합계를 추가해 준다.
방법은 복제 테이블을 이용해 합계를 구하는 방식 그대로 이다.
단지 합계라는 월이 하나 더 생긴것 같은 결과가 될 것이다.
SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER AMT_AM QTY_QN UNIT_COST
------ ------ ------ ---------- ---------- ----------
199802 112101 112101 212651104 900 236279.004
합계 112101 112101 212651104 900 236279.004
단계4.이번단계는 이제까지의 결과를 이용해 활동원가,활동량,활동단가를
ROW형식으로 바꿔주는 일이다.
앞의 주제에서도 다룬적이 있듯이 3개의 ROW를 가진 복제용 TABLE과의
CARTESIAN PRODUCT를 이용한다.
SELECT YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO G_KEY,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST)) AMT_AM
FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'
),11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
) G1,
( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
GROUP BY
YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER G_KEY G_DESC AMT_AM
------ ------ ------ ---------- -------- ----------
199802 112101 112101 1 활동원가 212651104
199802 112101 112101 2 활동량 900
199802 112101 112101 3 활동단가 236279.004
합계 112101 112101 1 활동원가 212651104
합계 112101 112101 2 활동량 900
합계 112101 112101 3 활동단가 236279.004
단계5.마지막으로 할일은 결과를 보여주기위한 형식으로 FORMAT을 맞추는 일이다.
최종문장은 아래와 같다.
SELECT A.ACTIVITY_CD,
A.DRIVER_CD,
G_KEY,
G_DESC,
SUM(DECODE(A.YYMM_YM,'합계',A.AMT_AM)) 합계,
SUM(DECODE(A.YYMM_YM,'199801',A.AMT_AM)) M,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
1),'YYYYMM'),A.AMT_AM)) M01,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
2),'YYYYMM'),A.AMT_AM)) M02,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
3),'YYYYMM'),A.AMT_AM)) M03,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
4),'YYYYMM'),A.AMT_AM)) M04,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
5),'YYYYMM'),A.AMT_AM)) M05,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
6),'YYYYMM'),A.AMT_AM)) M06,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
7),'YYYYMM'),A.AMT_AM)) M07,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
8),'YYYYMM'),A.AMT_AM)) M08,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
9),'YYYYMM'),A.AMT_AM)) M09,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
10),'YYYYMM'),A.AMT_AM)) M10,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
11),'YYYYMM'),A.AMT_AM)) M11
FROM ( SELECT YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO G_KEY,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST)) AMT_AM
FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
) G1,
( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
GROUP BY
YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')
) A
GROUP BY
A.ACTIVITY_CD,
A.DRIVER_CD,
G_KEY,
G_DESC
뒷풀이.
위의 풀이에서는 시작월을 '199801'
마지막월을 '199810' 으로하였다.
하지만 두개의 월과 사업장,부서를 PARAMETER화 하면 유연한 QUERY로 구성할 수 있다.
(YYMM_YM VARCHAR2(6),
SITE_CD VARCHAR2(6),
DEPT_CD VARCHAR2(6),
ACTIVITY_CD VARCHAR2(6),
CHG_TY VARCHAR2(20),
BUDGET_CD VARCHAR2(8),
AMT_AM NUMBER,
ACCOUNT_CD VARCHAR2(6));
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63021000',891400,'630210');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031104',145000,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63031201',504400,'630312');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C1','63039901',498000,'630399');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031105',130974,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031106',172671,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031117',199509,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031118',415049,'630311');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031301',454371,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031302',546924,'630313');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63031801',560342,'630318');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032303',114270,'630323');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES
'199802','111','A00100','112101','C5','63032401',297352,'630324');
INSERT INTO HCDT_ACTIVITYCOSTSUM VALUES ('199802','111','A00100','112101','C5','63032505',107242,'630325');
CREATE TABLE HCDT_OUTPUTQTY
(YYMM_YM VARCHAR2(6),
SITE_CD VARCHAR2(6),
DEPT_CD VARCHAR2(6),
ACTIVITY_CD VARCHAR2(6),
DRIVER_CD VARCHAR2(6),
QTY_QN NUMBER,
UNIT VARCHAR2(20));
INSERT INTO HCDT_OUTPUTQTY VALUES ('199802','111','A00100','112101','112101',900,NULL);
REPORT 형식 맞추기
건
다음 테이블은 활동에대한 금액을 가지고 있다.
DESC HCDT_ACTIVITYCOSTSUM
열 이름 널? 유형
------------------------------ -------- ----
YYMM_YM NOT NULL VARCHAR2(6) <-- 년월
SITE_CD NOT NULL VARCHAR2(6) <-- 사업장
DEPT_CD NOT NULL VARCHAR2(6) <-- 부서
ACTIVITY_CD NOT NULL VARCHAR2(6) <-- 활동
CHG_TY NOT NULL VARCHAR2(20) <-- 구분
BUDGET_CD NOT NULL VARCHAR2(8) <-- 항목
AMT_AM NUMBER <-- 활동금액
ACCOUNT_CD NOT NULL VARCHAR2(6) <-- 계정
YYMM_Y SITE_C DEPT_C ACTIVI CHG_TY BUDGET_C AMT_AM ACCOUN
------ ------ ------ ------ --------------- -------- ---------- ------
199802 111 A00100 112101 C1 63021000 79140000 630210
199802 111 A00100 112101 C1 63031104 1750000 630311
199802 111 A00100 112101 C1 63031201 500400 630312
199802 111 A00100 112101 C1 63039901 498000 630399
199802 111 A00100 112101 C5 63031105 13081974 630311
199802 111 A00100 112101 C5 63031106 15172671 630311
199802 111 A00100 112101 C5 63031117 3199509 630311
199802 111 A00100 112101 C5 63031118 15049 630311
199802 111 A00100 112101 C5 63031301 4454371 630313
199802 111 A00100 112101 C5 63031302 5866924 630313
199802 111 A00100 112101 C5 63031801 56980342 630318
199802 111 A00100 112101 C5 63032303 1197270 630323
199802 111 A00100 112101 C5 63032401 29721352 630324
199802 111 A00100 112101 C5 63032505 1073242 630325
다음 테이블은 활동에대한 활동량을 가지고 있다.
DESC HCDT_OUTPUTQTY
열 이름 널? 유형
------------------------------ -------- ----
YYMM_YM NOT NULL VARCHAR2(6) <-- 년월
SITE_CD NOT NULL VARCHAR2(6) <-- 사업장
DEPT_CD NOT NULL VARCHAR2(6) <-- 부서
ACTIVITY_CD NOT NULL VARCHAR2(6) <-- 활동
DRIVER_CD NOT NULL VARCHAR2(6) <-- 활동량
QTY_QN NUMBER <-- 단위
UNIT VARCHAR2(20)
YYMM_Y SITE_C DEPT_C ACTIVI DRIVER QTY_QN UNIT
------ ------ ------ ------ ------ ---------- --------------------
199802 111 A00100 112101 112101 900
문제.
활동/산출물/활동원가,활동량,활동단가구분 에 따라 주어진 기간 사이의
해당값 들을 월별로 보여주고 제일 앞에 합계를 보여준다.
ACTIVI DRIVER G_KEY G_DESC 합계 M M+1 M+2 M+3 M+4 M+5 M+6 M+7 +8 M+9 M+10 M+11
------ ------ ------ -------- ------- ------ ------- ------ ------ ------ ------ ------ ------ ----- ------ ------ ------
112101 112101 1 활동원가 5037504 5037504 112101 112101 2 활동량 900 900
112101 112101 3 활동단가 5597 5597
활동2 산출물2 1 활동원가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동2 산출물2 2 활동량 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동2 산출물2 3 활동단가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 1 활동원가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 2 활동량 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
활동3 산출물3 3 활동단가 000000 000000 000000 000000 000000 000000 000000 000000 000000 00000 000000 000000 000000
와 같은 형식의 결과를 나오게 하면된다.
이때 활동단가 = 활동원가/활동량 이다
합계란은 1번째 값부터 12번째 값의 합이며
합계 활동단가 = 합계활동원가/합계활동량 이다
첫월과 마지막월이 12개월을 초과할 시는 첫월을 기준으로
12개월만큼의 DATA만을 이용하여 결과를 보여준다.
결과의 이탤릭체 부분은 가상 데이타 이다.
생각.
한눈에 결과를 봐도 구현하는데 가장 문제가 될듯한 부분은 합계를 가로가 아닌
세로축에 보여 주어야 한다는 것이다.
이미 월별로 값을 COLUMN으로 나열하는 기법이나,구분자를 두어 값을 ROW형식으로
보여주는 기법은 앞 주제에서 다루었다.
이번에는 월별 값을 ROW형식에서 COLUMN형식으로 바꿔주고,동시에 구분에 따라
활동원가/활동량/활동단가 를 ROW형식으로 보여주는 기법이 모두 필요하다.
합계를 세로축에 보여주는 것도 가로축에 합계를 보여주는 기법을 약간만
변형하면된다.
해법.
단계1.먼저 활동원가를 가져오기 위하여 HCDT_ACTIVITYCOSTSUM 테이블에서 CHG_TY
를 배제하고 SUM을 한다.
SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
결과는 아래와 같다.
YYMM_Y SITE_C DEPT_C ACTIVI AMT_AM
------ ------ ------ ------ ----------
199802 111 A00100 112101 212651104
이때
LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),11),'YYYYMM'))
부분은 시작월을 기준으로 12개월을 초과할 경우 12개월분 DATA만을
읽어오도록 하기위한 문장이다.
단계2.단계1의 결과를 이용해 HCDT_OUTPUTQTY 와 JOIN을 걸어 원가/량/단가를 모두 구한다.
SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER AMT_AM QTY_QN UNIT_COST
------ ------ ------ ---------- ---------- ----------
199802 112101 112101 212651104 900 236279.004
단계3.단계2의 결과를 이용해 합계를 추가해 준다.
방법은 복제 테이블을 이용해 합계를 구하는 방식 그대로 이다.
단지 합계라는 월이 하나 더 생긴것 같은 결과가 될 것이다.
SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM')
,11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER AMT_AM QTY_QN UNIT_COST
------ ------ ------ ---------- ---------- ----------
199802 112101 112101 212651104 900 236279.004
합계 112101 112101 212651104 900 236279.004
단계4.이번단계는 이제까지의 결과를 이용해 활동원가,활동량,활동단가를
ROW형식으로 바꿔주는 일이다.
앞의 주제에서도 다룬적이 있듯이 3개의 ROW를 가진 복제용 TABLE과의
CARTESIAN PRODUCT를 이용한다.
SELECT YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO G_KEY,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST)) AMT_AM
FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'
),11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
) G1,
( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
GROUP BY
YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')
결과는 아래와 같다.
YYMM_Y ACTIVI DRIVER G_KEY G_DESC AMT_AM
------ ------ ------ ---------- -------- ----------
199802 112101 112101 1 활동원가 212651104
199802 112101 112101 2 활동량 900
199802 112101 112101 3 활동단가 236279.004
합계 112101 112101 1 활동원가 212651104
합계 112101 112101 2 활동량 900
합계 112101 112101 3 활동단가 236279.004
단계5.마지막으로 할일은 결과를 보여주기위한 형식으로 FORMAT을 맞추는 일이다.
최종문장은 아래와 같다.
SELECT A.ACTIVITY_CD,
A.DRIVER_CD,
G_KEY,
G_DESC,
SUM(DECODE(A.YYMM_YM,'합계',A.AMT_AM)) 합계,
SUM(DECODE(A.YYMM_YM,'199801',A.AMT_AM)) M,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
1),'YYYYMM'),A.AMT_AM)) M01,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
2),'YYYYMM'),A.AMT_AM)) M02,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
3),'YYYYMM'),A.AMT_AM)) M03,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
4),'YYYYMM'),A.AMT_AM)) M04,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
5),'YYYYMM'),A.AMT_AM)) M05,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
6),'YYYYMM'),A.AMT_AM)) M06,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
7),'YYYYMM'),A.AMT_AM)) M07,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
8),'YYYYMM'),A.AMT_AM)) M08,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
9),'YYYYMM'),A.AMT_AM)) M09,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
10),'YYYYMM'),A.AMT_AM)) M10,
SUM(DECODE(A.YYMM_YM,TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
11),'YYYYMM'),A.AMT_AM)) M11
FROM ( SELECT YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO G_KEY,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가') G_DESC,
SUM(DECODE(RNO,1,AMT_AM,2,QTY_QN,3,UNIT_COST)) AMT_AM
FROM ( SELECT DECODE(NO,1,YYMM_YM,2,'합계') YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
SUM(AMT_AM) AMT_AM,
SUM(QTY_QN) QTY_QN,
SUM(AMT_AM)/SUM(QTY_QN) UNIT_COST
FROM (SELECT C.YYMM_YM,
C.ACTIVITY_CD,
C.DRIVER_CD,
A.AMT_AM,
C.QTY_QN,
DECODE(NVL(C.QTY_QN,0),0,0,A.AMT_AM/C.QTY_QN) UNIT_COST
FROM ( SELECT YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD,
SUM(AMT_AM) AMT_AM
FROM HCDT_ACTIVITYCOSTSUM
WHERE YYMM_YM BETWEEN '199801'
AND LEAST('199810',
TO_CHAR(ADD_MONTHS(TO_DATE('199801','YYYYMM'),
11),'YYYYMM'))
AND SITE_CD = '111'
AND DEPT_CD = 'A00100'
GROUP BY
YYMM_YM,
SITE_CD,
DEPT_CD,
ACTIVITY_CD
) A,
HCDT_OUTPUTQTY C
WHERE C.YYMM_YM = A.YYMM_YM
AND C.SITE_CD = A.SITE_CD
AND C.DEPT_CD = A.DEPT_CD
AND C.ACTIVITY_CD = A.ACTIVITY_CD
AND C.DRIVER_CD > ' '
) F1,
(SELECT ROWNUM NO FROM USER_TABLES WHERE ROWNUM < 3) F2
GROUP BY
DECODE(NO,1,YYMM_YM,2,'합계'),
ACTIVITY_CD,
DRIVER_CD
) G1,
( SELECT ROWNUM RNO FROM USER_TABLES WHERE ROWNUM <= 3) G2
GROUP BY
YYMM_YM,
ACTIVITY_CD,
DRIVER_CD,
RNO,
DECODE(RNO,1,'활동원가',2,'활동량',3,'활동단가')
) A
GROUP BY
A.ACTIVITY_CD,
A.DRIVER_CD,
G_KEY,
G_DESC
뒷풀이.
위의 풀이에서는 시작월을 '199801'
마지막월을 '199810' 으로하였다.
하지만 두개의 월과 사업장,부서를 PARAMETER화 하면 유연한 QUERY로 구성할 수 있다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 34 | 반올림 DATA의 오차보정 | 운영자 | 2002.09.18 | 3585 |
| 33 | 복제를이용한 합계계에서 비율계산 | 운영자 | 2002.09.18 | 3669 |
| 32 | DATA변환 | 운영자 | 2002.09.18 | 2750 |
| 31 | 중간값구하기 | 운영자 | 2002.09.18 | 3377 |
| 30 | 한번 읽은 테이블로 백분율 구하기 | 운영자 | 2002.09.18 | 4085 |
| 29 | 빠진이빨찾기 III | 운영자 | 2002.09.18 | 3359 |
| 28 | 빠진이빨찾기 II | 운영자 | 2002.09.18 | 3067 |
| 27 | 빠진이빨찾기 | 운영자 | 2002.09.18 | 3122 |
| 26 | 순환참조에서의 상위코드로집계 | 운영자 | 2002.09.18 | 3226 |
| 25 | CONNECT BY의 SORT와 JOIN | 운영자 | 2002.09.18 | 2811 |
| 24 | REPORT 양식맞추기4 | 운영자 | 2002.09.18 | 2597 |
| 23 | REPORT 양식 맞추기 III | 운영자 | 2002.09.18 | 3015 |
| 22 | REPORT 양식 맞추기 II | 운영자 | 2002.09.18 | 2646 |
| » | REPORT 양식 맞추기 | 운영자 | 2002.09.18 | 31359 |
| 20 | 1:1 JOIN / 1:M JOIN | 운영자 | 2002.09.18 | 3000 |
| 19 | TUNING-01 | 운영자 | 2002.09.18 | 2739 |
| 18 | 소계와 합계 | 운영자 | 2002.09.18 | 3489 |
| 17 | 중복DATA 찾아내기 | 운영자 | 2002.09.17 | 2817 |
| 16 | GROUP별 DATA 분류 | 운영자 | 2002.09.17 | 2982 |
| 15 | PARAMETER값에 따라 변경이 일어나는 문장 | 운영자 | 2002.09.17 | 15808 |