¡á¿¹ 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;
|