|
■예 4_2_8 || SELECT문
SELECT DECODE(CMP_NAME,NULL,'합계',CMP_NAME) AS 업체명,
SUM(CASE WHEN :IN_YYYY||'01' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_01,
SUM(CASE WHEN :IN_YYYY||'02' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_02,
SUM(CASE WHEN :IN_YYYY||'03' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_03,
SUM(CASE WHEN :IN_YYYY||'04' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_04,
SUM(CASE WHEN :IN_YYYY||'05' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_05,
SUM(CASE WHEN :IN_YYYY||'06' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_06,
SUM(CASE WHEN :IN_YYYY||'07' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_07,
SUM(CASE WHEN :IN_YYYY||'08' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_08,
SUM(CASE WHEN :IN_YYYY||'09' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_09,
SUM(CASE WHEN :IN_YYYY||'10' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_10,
SUM(CASE WHEN :IN_YYYY||'11' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_11,
SUM(CASE WHEN :IN_YYYY||'12' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END) AS MM_12,
--가로 합계.
(SUM(CASE WHEN :IN_YYYY||'01' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'02' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'03' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'04' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'05' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'06' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'07' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'08' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'09' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'10' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'11' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)+
SUM(CASE WHEN :IN_YYYY||'12' BETWEEN TO_CHAR(PUT_START_DAY,'YYYYMM') AND TO_CHAR(PUT_END_DAY,'YYYYMM') THEN 1 ELSE 0 END)) AS TOT
FROM
INSA_COMPANY T1,--업체
INSA T2, --인사마스터
INSA_EMP_PJT T3 --사원별프로젝트
WHERE 1=1____
AND ((T3.PUT_START_DAY>=TO_DATE(:IN_YYYY||'0101','YYYYMMDD') AND T3.PUT_START_DAY <TO_DATE(:IN_YYYY||'1231','YYYYMMDD')+1)
OR
(T3.PUT_END_DAY>=TO_DATE(:IN_YYYY||'0101','YYYYMMDD') AND T3.PUT_END_DAY <TO_DATE(:IN_YYYY||'1231','YYYYMMDD')+1))
AND T2.SABUN = T3.SABUN
AND T1.CMP_REG_NO = T2.CMP_REG_NO
GROUP BY ROLLUP(T1.CMP_NAME)
ORDER BY T1.CMP_NAME;
|
INFO
실전 DB모델링과 SQL과정
