프로시져이용한 달력 Table만들기

DROP PROCEDURE IF EXISTS PRC_FillCALENDAR;

DROP TABLE IF EXISTS TB_BIZDAY;

CREATE TABLE IF NOT EXISTS `TB_BIZDAY` (

  `TDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `SDATE` datetime NOT NULL,

  `BDATE` datetime NOT NULL,

  `WEEK` int(20) NOT NULL,

  `DTYPE` varchar(2) DEFAULT NULL,

  `DTYPE2` varchar(2) DEFAULT NULL,

  `DSTR` varchar(30) DEFAULT NULL,

  `REGDATE` datetime DEFAULT NULL,

  `REG_ID` varchar(30) DEFAULT NULL,

  `REG_IP` varchar(30) DEFAULT NULL,

  `EDITDATE` datetime DEFAULT NULL,

  `EDIT_ID` varchar(30) DEFAULT NULL,

  `EDIT_IP` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`TDATE`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

DELIMITER $$

CREATE PROCEDURE PRC_FillCALENDAR(s_date VARCHAR(10), e_date VARCHAR(10))

BEGIN

DECLARE crt_tdate DATETIME;

DECLARE crt_sdate DATETIME;

DECLARE crt_bdate DATETIME;

DECLARE chkweek INT;

DECLARE chkedate DATETIME;

SET crt_tdate = STR_TO_DATE(s_date,'%Y-%m-%d');

SET chkedate = STR_TO_DATE(e_date,'%Y-%m-%d');

WHILE crt_tdate <= chkedate DO

SET chkweek = DAYOFWEEK(crt_tdate);

IF chkweek = 7 THEN

SET crt_sdate = ADDDATE(crt_tdate, INTERVAL 2 DAY); -- 토요일 +2

SET crt_bdate = ADDDATE(crt_tdate, INTERVAL 6 DAY); -- 토요일 +6

ELSEIF chkweek = 6 THEN

SET crt_sdate = ADDDATE(crt_tdate, INTERVAL 3 DAY); -- 금요일 +3

SET crt_bdate = ADDDATE(crt_tdate, INTERVAL 7 DAY); -- 나머지는 +7

ELSEIF chkweek = 1 THEN

SET crt_sdate = ADDDATE(crt_tdate, INTERVAL 1 DAY); -- 나머지는 +1

SET crt_bdate = ADDDATE(crt_tdate, INTERVAL 5 DAY); -- 일요일 +5

ELSE

SET crt_sdate = ADDDATE(crt_tdate, INTERVAL 1 DAY); -- 나머지는 +1

SET crt_bdate = ADDDATE(crt_tdate, INTERVAL 7 DAY); -- 나머지는 +7

END IF;

INSERT IGNORE INTO TB_BIZDAY (TDATE,SDATE,BDATE,WEEK) VALUES (crt_tdate, crt_sdate, crt_bdate, DAYOFWEEK(crt_tdate));

SET crt_tdate = ADDDATE(crt_tdate, INTERVAL 1 DAY);

END WHILE;

END$$

DELIMITER ;

CALL PRC_FillCALENDAR('2015-01-01', '2026-12-31');

UPDATE TB_BIZDAY SET DSTR = '신정', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0101';

UPDATE TB_BIZDAY SET DSTR = '삼일절', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0301';

UPDATE TB_BIZDAY SET DSTR = '근로자의날', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0501';

UPDATE TB_BIZDAY SET DSTR = '어린이날', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0505';

UPDATE TB_BIZDAY SET DSTR = '현충일', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0606';

UPDATE TB_BIZDAY SET DSTR = '광복절', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '0815';

UPDATE TB_BIZDAY SET DSTR = '개천절', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '1003';

UPDATE TB_BIZDAY SET DSTR = '한글날', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '1009';

UPDATE TB_BIZDAY SET DSTR = '성탄절', DTYPE = 1 WHERE DATE_FORMAT(TDATE,'%m%d') = '1225';

SELECT 

  DATE_FORMAT(TDATE,'%Y%m%d') YMD, 

  DATE_FORMAT(TDATE,'%m') MM, 

  DATE_FORMAT(TDATE,'%c') M, 

  DATE_FORMAT(TDATE,'%d') DD, 

  DATE_FORMAT(TDATE,'%e') D,

  WEEK, DTYPE, DTYPE2, DSTR,

  PERIOD_ADD('201603',-1) PRE,

  PERIOD_ADD('201603',1) NXT,

  CASE WHEN DATE_FORMAT(TDATE, '%Y%m') != '201603' THEN 'N' ELSE 'Y' END IS_USE,

  CASE WHEN TDATE > now() AND TDATE < DATE_ADD(now(), INTERVAL +1 MONTH) THEN 'Y' ELSE 'N' END IS_USE2

FROM TB_BIZDAY

WHERE TDATE >= DATE_ADD('20160301', INTERVAL (DAYOFWEEK('20160301')-1) * -1 DAY)

AND TDATE <= DATE_ADD(LAST_DAY('20160301'), INTERVAL (7 - DAYOFWEEK(LAST_DAY('20160301'))) DAY);