시스템 테이블 사용 (MASTER.DBO.SPT_VALUES)

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20120115'), 112) DATE
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P'
AND NUMBER <= DATEDIFF(D, '20130115', '20130122')

 

RECURSIVE 를 사용

WITH A AS (
 SELECT LVL = 1
   , CAST('20130115' AS DATETIME) DT
 UNION ALL
 SELECT LVL + 1
   , DATEADD(D, 1, DT)
   FROM A
  WHERE DT < CAST('20130122' AS DATETIME)
)
SELECT LVL
  , DT
  , YEAR = DATEPART(YEAR, DT)
  , MONTH = DATEPART(MONTH, DT)
  , DAY = DATEPART(DAY, DT)
  , WEEK_OF_YEAR = DATEPART(WK, DT)
  , WEEK_OF_MONTH = DATEPART(WK, DT) - DATEPART(WK, LEFT(CONVERT(VARCHAR, DT, 112), 6) + '01') + 1
  , DAY = DATEPART(DW, DT)
  , 요일 = DATENAME(W, DT)
  , 분기 = DATEPART(Q, DT)
  , 반기 = CASE WHEN DATEPART(MONTH, DT) BETWEEN 1 AND 6 THEN '상반기' ELSE '하반기' END
  FROM A
OPTION (MAXRECURSION 0) 

 

휴일테이블의 휴일과 토/일요일 일자만 조회

 휴일테이블
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('99990101', '신정');
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('9999016', '테스트');
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('20130117', '창립기념일');

WITH A AS (
 SELECT CAST('20130115' AS DATETIME) DT
 UNION ALL
 SELECT DATEADD(D, 1, DT)
   FROM A
  WHERE DT < CAST('20130122' AS DATETIME)
), B AS (
 SELECT CAST('20130115' AS DATETIME) DT
   , LVL = 1
 UNION ALL
 SELECT DATEADD(YEAR, 1, DT)
   , LVL + 1
   FROM B
  WHERE LVL <= DATEDIFF(YEAR, '20130115', '20130122')
), C AS (
 SELECT HOLI_DATE
   , HOLI_DESC
      FROM (
   SELECT SUBSTRING(CONVERT(VARCHAR, B.DT, 112), 1, 4) + SUBSTRING(A.HOLI_DATE, 5, 4) HOLI_DATE
     , A.HOLI_DESC
     FROM PIS_HOLIDAY A
     , B
    WHERE A.HOLI_DATE LIKE '9999%'
   ) A
  WHERE HOLI_DATE BETWEEN '20130115' AND '20130122'
)
SELECT HOLI_DATE
  , MAX(CASE WHEN GUBUN = 1 THEN HOLI_DESC ELSE DATENAME(W, HOLI_DATE) END) HOLI_DESC
  FROM (
 SELECT 1 GUBUN
   , HOLI_DATE
   , HOLI_DESC
   FROM PIS_HOLIDAY
     WHERE HOLI_DATE BETWEEN '20130115' AND '20130122'
 UNION ALL
 SELECT 1
   , HOLI_DATE
   , HOLI_DESC
   FROM C
 UNION ALL
 SELECT 2
   , CONVERT(VARCHAR, DT, 112)
   , NULL
   FROM A
  WHERE DATEPART(DW, DT) IN (1, 7)
  ) A
 GROUP BY HOLI_DATE
OPTION (MAXRECURSION 0) 

 

 

 

+ Recent posts

티스토리툴바