IF OBJECT_ID('DimDate') IS NOT NULL
DROP TABLE DimDate
GO
CREATE TABLE DimDate
(--IDX INT
--,
DDATE date
,IFULLDATE int
,IYEAR INT
,IMONTH INT
,IDAY INT
,IQUARTER INT
,IWEEK INT
,IWEEKDAY INT
,VWEEKDAY VARCHAR(10)
,HOLIDAY_YN INT
,HOLIDAY_NAME VARCHAR(100)
)
GO
INSERT INTO DimDate (
-- IDX
--,
DDATE
,IFULLDATE
,IYEAR
,IMONTH
,IDAY
,IQUARTER
,IWEEK
,IWEEKDAY
,VWEEKDAY
)
SELECT top 2191 -- ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX -- 2015년 말까지
CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1
, cast(CONVERT(VARCHAR(10), CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, 112) as int)
, DATEPART(YEAR, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, DATEPART(MONTH, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, DATEPART(DAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, DATEPART(QUARTER, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, DATEPART(WEEK, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
, CASE DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
WHEN 1 THEN '일'
WHEN 2 THEN '월'
WHEN 3 THEN '화'
WHEN 4 THEN '수'
WHEN 5 THEN '목'
WHEN 6 THEN '금'
WHEN 7 THEN '토'
ELSE NULL END
FROM SYSOBJECTS A
, SYSOBJECTS B
--, SYSOBJECTS C
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20090101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090125'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090126'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20090301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20090502'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20090505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20090606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20090815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091002'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석,개천절' WHERE IFULLDATE = '20091003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20091225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20100101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100213'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100214'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20100301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20100505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20100521'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20100606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20100815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100921'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100922'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20101003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20101225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20110101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110202'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110203'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110204'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20110301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20110505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20110510'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20110606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20110815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110911'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110912'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110913'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20111003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20111225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20120101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120122'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120123'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120124'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20120301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20120505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20120528'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20120606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20120815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120929'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120930'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20121001'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20121003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20121225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20130101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130210'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130211'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20130301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20130505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20130517'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20130606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20130815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130918'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130919'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130920'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20131003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20131225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20140101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140130'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140131'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140201'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20140301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20140505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20140506'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20140606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20140815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140907'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140908'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140909'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20141003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20141225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20150101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150218'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150219'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150220'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20150301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20150505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20150525'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20150606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20150815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150926'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150927'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150928'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20151003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20151225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20160101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160207'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160208'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20160301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20160505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20160514'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20160606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20160815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160914'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160915'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160916'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20161003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20161225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20170101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170128'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170129'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20170301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20170503'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20170505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20170606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20170815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절,추석' WHERE IFULLDATE = '20171003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171005'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20171225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20180101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180216'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180217'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20180301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20180505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20180522'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20180606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20180815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180924'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180925'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20181003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20181225'
UPDATE DimDate
SET HOLIDAY_YN = 1
WHERE VWEEKDAY IN ('토','일')
UPDATE DimDate
SET HOLIDAY_YN = 0
, HOLIDAY_NAME = ''
WHERE HOLIDAY_NAME IS NULL
DELETE DimDate WHERE IFULLDATE > '20181225'
GO
SELECT * FROM DimDate