언능지울꺼

TEMPSQL

/*테스트파일*/

DROP TABLE IF EXISTS KD_ODAT_BRCH_TIME_01
;

SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','KD_ODAT_BRCH_TIME_01 생성중...' ) AS ''
;

CREATE TABLE KD_ODAT_BRCH_TIME_01 AS 
SELECT  

   ODAT_BRCH_CD																	/*지사*/
	,MAX(ODAT_YEAR)     AS ODAT_YEAR
	,ODAT_CHK_YMD																	/*년월일*/
	,ODAT_SPLY_TP																	/*냉난방구분*/
	,ODAT_MDUM_TP																	/*냉온수구분*/
	,ODAT_PRCE_TP																	/*요금구분*/
	,MAX(ODAT_WDAY    )	AS ODAT_WDAY												/*요일*/
	,MAX(ODAT_HOLY_YN )	AS ODAT_HOLY_YN 											/*휴일여부*/
	/*=========================사용량===========================     ===온도==*/
	,SUM( CASE WHEN ODAT_H01 < 10 THEN ODAT_H01 END) AS ODAT_H01     ,ODAT_T01    
	,SUM( CASE WHEN ODAT_H02 < 10 THEN ODAT_H02 END) AS ODAT_H02     ,ODAT_T02
	,SUM( CASE WHEN ODAT_H03 < 10 THEN ODAT_H03 END) AS ODAT_H03     ,ODAT_T03
	,SUM( CASE WHEN ODAT_H04 < 10 THEN ODAT_H04 END) AS ODAT_H04     ,ODAT_T04
	,SUM( CASE WHEN ODAT_H05 < 10 THEN ODAT_H05 END) AS ODAT_H05     ,ODAT_T05
	,SUM( CASE WHEN ODAT_H06 < 10 THEN ODAT_H06 END) AS ODAT_H06     ,ODAT_T06
	,SUM( CASE WHEN ODAT_H07 < 10 THEN ODAT_H07 END) AS ODAT_H07     ,ODAT_T07
	,SUM( CASE WHEN ODAT_H08 < 10 THEN ODAT_H08 END) AS ODAT_H08     ,ODAT_T08
	,SUM( CASE WHEN ODAT_H09 < 10 THEN ODAT_H09 END) AS ODAT_H09     ,ODAT_T09
	,SUM( CASE WHEN ODAT_H10 < 10 THEN ODAT_H10 END) AS ODAT_H10     ,ODAT_T10
	,SUM( CASE WHEN ODAT_H11 < 10 THEN ODAT_H11 END) AS ODAT_H11     ,ODAT_T11
	,SUM( CASE WHEN ODAT_H12 < 10 THEN ODAT_H12 END) AS ODAT_H12     ,ODAT_T12
	,SUM( CASE WHEN ODAT_H13 < 10 THEN ODAT_H13 END) AS ODAT_H13     ,ODAT_T13
	,SUM( CASE WHEN ODAT_H14 < 10 THEN ODAT_H14 END) AS ODAT_H14     ,ODAT_T14
	,SUM( CASE WHEN ODAT_H15 < 10 THEN ODAT_H15 END) AS ODAT_H15     ,ODAT_T15
	,SUM( CASE WHEN ODAT_H16 < 10 THEN ODAT_H16 END) AS ODAT_H16     ,ODAT_T16
	,SUM( CASE WHEN ODAT_H17 < 10 THEN ODAT_H17 END) AS ODAT_H17     ,ODAT_T17
	,SUM( CASE WHEN ODAT_H18 < 10 THEN ODAT_H18 END) AS ODAT_H18     ,ODAT_T18
	,SUM( CASE WHEN ODAT_H19 < 10 THEN ODAT_H19 END) AS ODAT_H19     ,ODAT_T19
	,SUM( CASE WHEN ODAT_H20 < 10 THEN ODAT_H20 END) AS ODAT_H20     ,ODAT_T20
	,SUM( CASE WHEN ODAT_H21 < 10 THEN ODAT_H21 END) AS ODAT_H21     ,ODAT_T21
	,SUM( CASE WHEN ODAT_H22 < 10 THEN ODAT_H22 END) AS ODAT_H22     ,ODAT_T22
	,SUM( CASE WHEN ODAT_H23 < 10 THEN ODAT_H23 END) AS ODAT_H23     ,ODAT_T23
	,SUM( CASE WHEN ODAT_H24 < 10 THEN ODAT_H24 END) AS ODAT_H24     ,ODAT_T24
	,COUNT(*) AS CNT 																/*수용가수*/

FROM KD_ODAT
GROUP BY ODAT_BRCH_CD , ODAT_CHK_YMD , ODAT_SPLY_TP , ODAT_MDUM_TP , ODAT_PRCE_TP
;

SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','KD_ODAT_TIME 생성 ...' ) AS '';
CREATE TABLE KD_ODAT_TIME AS 
SELECT
  ODAT_BRCH_CD
 ,ODAT_YEAR
 ,ODAT_CHK_YMD
 ,ODAT_SPLY_TP
 ,ODAT_MDUM_TP
 ,ODAT_PRCE_TP
 ,ODAT_WDAY
 ,ODAT_HOLY_YN 
 ,'00'     AS ODAT_HOUR
 ,ODAT_H01 AS ODAT_USE
 ,ODAT_T01 AS ODAT_TMPR
 ,CNT
FROM KD_ODAT_BRCH_TIME_01
WHERE 1 = 2
;


SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 01 HOUR...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'01',ODAT_H01,ODAT_T01,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 02 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'02',ODAT_H02,ODAT_T02,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 03 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'03',ODAT_H03,ODAT_T03,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 04 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'04',ODAT_H04,ODAT_T04,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 05 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'05',ODAT_H05,ODAT_T05,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 06 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'06',ODAT_H06,ODAT_T06,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 07 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'07',ODAT_H07,ODAT_T07,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 08 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'08',ODAT_H08,ODAT_T08,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 09 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'09',ODAT_H09,ODAT_T09,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 10 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'10',ODAT_H10,ODAT_T10,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 11 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'11',ODAT_H11,ODAT_T11,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 12 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'12',ODAT_H12,ODAT_T12,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 13 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'13',ODAT_H13,ODAT_T13,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 14 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'14',ODAT_H14,ODAT_T14,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 15 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'15',ODAT_H15,ODAT_T15,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 16 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'16',ODAT_H16,ODAT_T16,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 17 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'17',ODAT_H17,ODAT_T17,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 18 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'18',ODAT_H18,ODAT_T18,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 19 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'19',ODAT_H19,ODAT_T19,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 20 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'20',ODAT_H20,ODAT_T20,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 21 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'21',ODAT_H21,ODAT_T21,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 22 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'22',ODAT_H22,ODAT_T22,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 23 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'23',ODAT_H23,ODAT_T23,CNT FROM KD_ODAT_BRCH_TIME_01;SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','생성 24 HOUR ...' ) AS '';
INSERT KD_ODAT_TIME SELECT ODAT_BRCH_CD,ODAT_YEAR,ODAT_CHK_YMD,ODAT_SPLY_TP,ODAT_MDUM_TP,ODAT_PRCE_TP,ODAT_WDAY,ODAT_HOLY_YN,'24',ODAT_H24,ODAT_T24,CNT FROM KD_ODAT_BRCH_TIME_01;


SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','인덱스 생성(IDX_KD_ODAT_TIME)' ) AS '';

ALTER TABLE KD_ODAT_TIME ADD INDEX IDX_KD_ODAT_TIME (ODAT_BRCH_CD , ODAT_YEAR);

SELECT CONCAT( SYSDATE() ,':KD_ODAT_TIME.SQL> ','완료' ) AS '';

/*DROP TABLE IF EXISTS KD_ODAT_BRCH_TIME_01
;
*/