style24.doc/산출물/4.구축/데이터처리/영업일(TB_BIZDAY).sql

INSERT INTO TB_BIZDAY
SELECT DATE_FORMAT(DT,'%Y%m%d') AS BIZDAY
     , CASE DAYOFWEEK(DT)
            WHEN 1 THEN '일'
            WHEN 2 THEN '월'
            WHEN 3 THEN '화'
            WHEN 4 THEN '수'
            WHEN 5 THEN '목'
            WHEN 6 THEN '금'
            WHEN 7 THEN '토'
       END                      AS WEEK_DAY
     , CASE WHEN DATE_FORMAT(DT,'%Y%m') IN ('0101','0301','0505','0606','0815','1009','1225') OR DAYOFWEEK(DT) IN (1,7) THEN
                'N'
            ELSE
                'Y'
       END                      AS BIZDAY_YN
     , CASE WHEN DATE_FORMAT(DT,'%Y%m') IN ('0101','0301','0505','0606','0815','1009','1225') THEN
                '공휴일'
            WHEN DAYOFWEEK(DT) IN (1,7) THEN
                '주말'
            ELSE
                NULL
       END                      AS RESTDAY_DESC
     , 1                        AS REG_NO
     , NOW()                    AS REG_DT
     , 1                        AS UPD_NO
     , NOW()                    AS UPD_DT
FROM   (
        SELECT ADDDATE('1970-01-01',T4.I*10000 + T3.I*1000 + T2.I*100 + T1.I*10 + T0.I) AS DT
        FROM   (
                SELECT 0       AS I
                UNION SELECT 1 AS I
                UNION SELECT 2 AS I
                UNION SELECT 3 AS I
                UNION SELECT 4 AS I
                UNION SELECT 5 AS I
                UNION SELECT 6 AS I
                UNION SELECT 7 AS I
                UNION SELECT 8 AS I
                UNION SELECT 9 AS I
               ) T0
             , (
                SELECT 0       AS I
                UNION SELECT 1 AS I
                UNION SELECT 2 AS I
                UNION SELECT 3 AS I
                UNION SELECT 4 AS I
                UNION SELECT 5 AS I
                UNION SELECT 6 AS I
                UNION SELECT 7 AS I
                UNION SELECT 8 AS I
                UNION SELECT 9 AS I
               ) T1
             , (
                SELECT 0       AS I
                UNION SELECT 1 AS I
                UNION SELECT 2 AS I
                UNION SELECT 3 AS I
                UNION SELECT 4 AS I
                UNION SELECT 5 AS I
                UNION SELECT 6 AS I
                UNION SELECT 7 AS I
                UNION SELECT 8 AS I
                UNION SELECT 9 AS I
               ) T2
             , (
                SELECT 0       AS I
                UNION SELECT 1 AS I
                UNION SELECT 2 AS I
                UNION SELECT 3 AS I
                UNION SELECT 4 AS I
                UNION SELECT 5 AS I
                UNION SELECT 6 AS I
                UNION SELECT 7 AS I
                UNION SELECT 8 AS I
                UNION SELECT 9 AS I
               ) T3
             , (
                SELECT 0       AS I
                UNION SELECT 1 AS I
                UNION SELECT 2 AS I
                UNION SELECT 3 AS I
                UNION SELECT 4 AS I
                UNION SELECT 5 AS I
                UNION SELECT 6 AS I
                UNION SELECT 7 AS I
                UNION SELECT 8 AS I
                UNION SELECT 9 AS I
               ) T4
       ) V
WHERE  DT BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d') AND '2040-12-31'
-- ORDER  BY DT DESC
;