달력을 DB에 저장해서 사용해야 하는 할 필요가 있을 때 달력 생성 - MSSQL

달력을 DB에 저장해 놓고 사용해야 하는 경우를 위해서 만듬.  MSSQL

CALENDAR_TABLE.SQL

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CALENDAR_TABLE') DROP TABLE CALENDAR_TABLE;

CREATE TABLE [dbo].[CALENDAR_TABLE](

[CALN_DATE] [nvarchar](8) COLLATE KOREAN_WANSUNG_CS_AS NOT NULL,

[CALN_WEEK] [nvarchar](6) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[CALN_YEAR] [nvarchar](4) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[CALN_MONTH] [nvarchar](6) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[WDAY_CODE] [nvarchar](1) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[HOGB_CODE] [nvarchar](12) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[LWSD_DATE] [nvarchar](8) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[LYSWSD_DATE] [nvarchar](8) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[LYSMSD_DATE] [nvarchar](8) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[LMSD_DATE] [nvarchar](8) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[HOLI_NAME] [nvarchar](100) COLLATE KOREAN_WANSUNG_CS_AS NULL,

[INTF_DTTM] [datetime] NOT NULL,

CONSTRAINT [PK_CALENDAR_TABLE] PRIMARY KEY CLUSTERED ([CALN_DATE] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY];

ALTER TABLE [dbo].[CALENDAR_TABLE] ADD  DEFAULT (getdate()) FOR [INTF_DTTM];

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'달력', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'달력일', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'CALN_DATE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'주차', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'CALN_WEEK';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'년도', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'CALN_YEAR';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'년월', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'CALN_MONTH';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'요일코드', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'WDAY_CODE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'휴일구분코드', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'HOGB_CODE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'전주동요일', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'LWSD_DATE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'전년동주동요일', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'LYSWSD_DATE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'전년동월동일', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'LYSMSD_DATE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'전월동일', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'LMSD_DATE';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'휴일명', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'HOLI_NAME';

EXEC sys.sp_addextendedproperty @name=N'MS_DESCRIPTION', @value=N'인터페이스일시', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CALENDAR_TABLE', @level2type=N'COLUMN',@level2name=N'INTF_DTTM';

SELECT OBJECT_NAME(SC.OBJECT_ID) AS [TABLE_NAME]

, SC.NAME AS [COLUMN_NAME]

, SEP.VALUE AS [DESCRIPTION]

  FROM SYS.COLUMNS SC

   LEFT OUTER JOIN SYS.extended_properties SEP 

ON SEP.MAJOR_ID = SC.OBJECT_ID

   AND SEP.MINOR_ID = SC.COLUMN_ID 

   AND UPPER(SEP.NAME) =  'MS_DESCRIPTION'

WHERE OBJECTPROPERTY(SC.OBJECT_ID, 'IsMsShipped') = 0

   AND OBJECT_NAME(SC.OBJECT_ID) = 'CALENDAR_TABLE'

  ORDER BY OBJECT_NAME(SC.OBJECT_ID), SC.COLUMN_ID ;

-- 2006년 1월 1일 ~ 2030년 12월 31일까지 입력

INSERT INTO CALENDAR_TABLE (CALN_DATE)

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20060101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20060101', '20061231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20070101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20070101', '20071231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20080101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20080101', '20081231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20090101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20090101', '20091231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20100101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20100101', '20101231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20110101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20110101', '20111231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20120101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20120101', '20121231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20130101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20130101', '20131231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20140101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20140101', '20141231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20150101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20150101', '20151231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20160101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20160101', '20161231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20170101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20170101', '20171231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20180101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20180101', '20181231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20190101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20190101', '20191231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20200101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20200101', '20201231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20210101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20210101', '20211231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20220101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20220101', '20221231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20230101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20230101', '20231231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20240101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20240101', '20241231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20250101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20250101', '20251231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20260101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20260101', '20261231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20270101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20270101', '20271231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20280101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20280101', '20281231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20290101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20290101', '20291231') UNION ALL

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20300101'), 112) AS CALN_DATE FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20300101', '20301231')

;

--주차 / 요일코드 / 년도 / 년월 /전주동요일 / 전년동월동일 / 전월동일 UPDATE

UPDATE CALENDAR_TABLE

   SET CALENDAR_TABLE.CALN_WEEK = TMP.CALN_WEEK, -- 주자(YYYYWW)

CALENDAR_TABLE.CALN_YEAR = TMP.CALN_YEAR, -- 년도(YYYY)

CALENDAR_TABLE.CALN_MONTH = TMP.CALN_MONTH, -- 년월(YYYYMM)

CALENDAR_TABLE.WDAY_CODE = TMP.WDAY_CODE, -- 요일코드(1~7, 일~토)

CALENDAR_TABLE.LWSD_DATE = TMP.LWSD_DATE, -- 전주동요일(일주일 전)

CALENDAR_TABLE.LYSMSD_DATE = TMP.LYSMSD_DATE, -- 전년동월동일

CALENDAR_TABLE.LMSD_DATE = TMP.LMSD_DATE -- 전월동일

  FROM ( SELECT CASE WHEN DATEPART(ISO_WEEK, CAST(WC.CALN_DATE AS DATETIME) ) > 50 AND MONTH(CAST(WC.CALN_DATE AS DATETIME)) = 1  THEN CAST( YEAR( CAST(WC.CALN_DATE AS DATETIME) ) -1 AS CHAR(4) ) + REPLICATE('0', 2 - LEN(DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)))) + CAST( DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)) AS VARCHAR(2))

WHEN DATEPART(ISO_WEEK, CAST(WC.CALN_DATE AS DATETIME) ) = 1  AND MONTH(CAST(WC.CALN_DATE AS DATETIME)) = 12 THEN CAST( YEAR( CAST(WC.CALN_DATE AS DATETIME) ) +1 AS CHAR(4) ) + REPLICATE('0', 2 - LEN(DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)))) + CAST( DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)) AS VARCHAR(2))

ELSE CAST( YEAR( CAST(WC.CALN_DATE AS DATETIME) ) AS CHAR(4) ) + REPLICATE('0', 2 - LEN(DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)))) + CAST( DATEPART(ISO_WEEK,CAST(WC.CALN_DATE AS DATETIME)) AS VARCHAR(2))

END AS CALN_WEEK

, SUBSTRING(WC.CALN_DATE,1,4) AS CALN_YEAR

, SUBSTRING(WC.CALN_DATE,1,6) AS CALN_MONTH

, DATEPART(DW, CAST(WC.CALN_DATE AS DATETIME) ) AS WDAY_CODE

, CONVERT(CHAR,DATEADD(DAY,  -7,CAST(WC.CALN_DATE AS DATETIME)),112) AS LWSD_DATE

, CONVERT(CHAR,DATEADD(YEAR, -1,CAST(WC.CALN_DATE AS DATETIME)),112) AS LYSMSD_DATE

, CONVERT(CHAR,DATEADD(MONTH,-1,CAST(WC.CALN_DATE AS DATETIME)),112) AS LMSD_DATE

, WC.CALN_DATE

  FROM CALENDAR_TABLE AS WC

) AS TMP

 WHERE CALENDAR_TABLE.CALN_DATE = TMP.CALN_DATE

;

--전년동주동요일 UPDATE

UPDATE CALENDAR_TABLE

   SET CALENDAR_TABLE.LYSWSD_DATE = TMP.LYSWSD_DATE -- 전년동주동요일

  FROM ( SELECT (SELECT WC2.CALN_DATE  AS LYSWSD_DATE 

                       FROM CALENDAR_TABLE AS WC2 

  WHERE WC2.CALN_WEEK = CAST( SUBSTRING(WC.CALN_WEEK,1,4)-1 AS CHAR(4) ) + SUBSTRING(WC.CALN_WEEK,5,2) AND WC.WDAY_CODE = WC2.WDAY_CODE) AS LYSWSD_DATE

, WC.CALN_DATE

  FROM CALENDAR_TABLE AS WC

) AS TMP

 WHERE CALENDAR_TABLE.CALN_DATE = TMP.CALN_DATE

;

--53주차가 있는 해의 경우에는 '전년 동주 동요일'의 주차가 52주차가 마지막이므로 NULL 자료가 발생

--그런 경우 해당년의 1주차 정보로 대체한다.

UPDATE CALENDAR_TABLE

   SET CALENDAR_TABLE.LYSWSD_DATE = TMP.LYSWSD_DATE

  FROM ( SELECT CALN_DATE

,CASE WHEN LYSWSD_DATE IS NULL

  THEN LEAD(LYSWSD_DATE,7,NULL) OVER (ORDER BY CALN_DATE)

  ELSE LYSWSD_DATE

  END AS LYSWSD_DATE

  FROM CALENDAR_TABLE

) AS TMP

 WHERE CALENDAR_TABLE.CALN_DATE = TMP.CALN_DATE ;

--최초 1년 자료 삭제

DELETE FROM CALENDAR_TABLE WHERE CALN_DATE <='20061231';

--참고날짜

SELECT *

  FROM CALENDAR_TABLE

 WHERE CALN_DATE >= '20091228';