데이터베이스 질문과 답변
| FUNCTION | SEC1 | SE2 | MA |
| 용접 | 0.9 | 0.1 | 0.2 |
| 미장 | |||
| 전기 |
안녕하세요
위에 테이블같은 결과가 나와야 하는데 잘안돼네요.
문의 드립니다. 이 프로젝트의 경우 20202월에 끝났으며 섹션2는 2월까지만 집계를 해야하는데
문제는 3월에도 4월에도 5월에도 끝났지만 잔여업무를 보는 사람들이 입력을 하게 되어서
끝나는 2월까지만 섹션2의 합계 리소스를 더해서 보여주고 3월4월5월에 입력한 리소스합은 MA라는 섹션명으로 보여줘야 하는데
어떻게 해야할지 질문드립니다.
아래 같이 데이타가 들어가 있습니다.
WITH PROJECT_RSC AS (
SELECT '202001' AS YYYYMM, 'SEC1' AS PHASE,'용접' AS FUNCTION,'0.3' AS RATE FROM DUAL UNION ALL
SELECT '202001' , 'SEC1' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202001' , 'SEC1' ,'전기','0.3' FROM DUAL UNION ALL
SELECT '202001' , 'SEC1' ,'용접','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC1' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC1' ,'전기','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC1' ,'용접','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC2' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC2' ,'전기','0.3' FROM DUAL UNION ALL
SELECT '202002' , 'SEC2' ,'용접','0.3' FROM DUAL UNION ALL
SELECT '202003' , 'SEC2' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202003' , 'SEC2' ,'전기','0.1' FROM DUAL UNION ALL
SELECT '202004' , 'SEC2' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202004' , 'SEC2' ,'전기','0.2' FROM DUAL UNION ALL
SELECT '202005' , 'SEC2' ,'미장','0.3' FROM DUAL UNION ALL
SELECT '202005' , 'SEC2' ,'전기','0.1' FROM DUAL UNION ALL
),
PJT_TMP AS (
SELECT '202002' AS END_DATE,'완료' as CUR_STATUS FROM DUAL
)
select sum(decode(phase,'sec1',rate) "SEC1"
,sum(decode(phase,'sec2',rate) "SEC2"
from PROJECT_RSC
GROUP BY FUNCTION
WITH project_rsc AS
(
SELECT '202001' yyyymm, 'SEC1' phase, '용접' function, 0.3 rate FROM dual
UNION ALL SELECT '202001', 'SEC1', '미장', 0.3 FROM dual
UNION ALL SELECT '202001', 'SEC1', '전기', 0.3 FROM dual
UNION ALL SELECT '202001', 'SEC1', '용접', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC1', '미장', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC1', '전기', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC1', '용접', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC2', '미장', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC2', '전기', 0.3 FROM dual
UNION ALL SELECT '202002', 'SEC2', '용접', 0.3 FROM dual
UNION ALL SELECT '202003', 'SEC2', '미장', 0.3 FROM dual
UNION ALL SELECT '202003', 'SEC2', '전기', 0.1 FROM dual
UNION ALL SELECT '202004', 'SEC2', '미장', 0.3 FROM dual
UNION ALL SELECT '202004', 'SEC2', '전기', 0.2 FROM dual
UNION ALL SELECT '202005', 'SEC2', '미장', 0.3 FROM dual
UNION ALL SELECT '202005', 'SEC2', '전기', 0.1 FROM dual
)
, pjt_tmp AS
(
SELECT '202002' end_date, '완료' cur_status FROM dual
)
SELECT a.function
, SUM(DECODE(a.phase, 'SEC1', a.rate)) sec1
, SUM(CASE WHEN a.phase = 'SEC2' AND a.yyyymm <= b.end_date THEN a.rate END) sec2
, SUM(CASE WHEN a.phase = 'SEC2' AND a.yyyymm > b.end_date THEN a.rate END) ma
FROM project_rsc a
, pjt_tmp b
GROUP BY a.function
;
마농님 감사합니다 왜 저생각을 못했을까 반성합니다 좋은하루 되세요
) 버튼을 클릭하여 작성 하시면 됩니다.