MSSQL 날짜, 주차 데이터 없을 경우 MASTER..SPT_VALUES을 이용한 데이터 만들기

MASTER..SPT_VALUES의 데이블 데이터 템플릿으로 생각됩니다.

일정 기간의 날짜 또는 주차 등의 데이터를 만들경우 유용하게 사용 할 수 있습니다.

MASTER..SPT_VALUES의 TYPE이 'P'인 데이터를 이용하여 날짜, 주차를 구하는 쿼리입니다.

SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P'

1. 시작일 종료일로 날짜 데이터 쿼리

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '2021-07-01'), 23)
 FROM MASTER..SPT_VALUES
WHERE TYPE = 'P'
   AND NUMBER <= DATEDIFF(D, '2021-07-01', '2021-07-31')

 

2. 시작일 종료일로 주차 데이터 쿼리

SELECT DATEPART(YEAR, EDATE) YEAR,
       DATEPART(WEEK, EDATE) WEEK,
       CASE WHEN MONTH(SDATE) <> MONTH(EDATE) AND DAY(EDATE) < 4 THEN MONTH(SDATE) ELSE MONTH(EDATE) END MONTH,
       CONVERT(VARCHAR, SDATE, 23) SDATE,
       CONVERT(VARCHAR, EDATE, 23) EDATE
  FROM (SELECT NUMBER,
               DATEADD(DAY, (1 + NUMBER) * 7 - DATEPART(WEEKDAY, CONVERT(DATE, '2021-07-01')) - 6, CONVERT(DATE, '2021-07-01')) SDATE,
               DATEADD(DAY, (1 + NUMBER) * 7 - DATEPART(WEEKDAY, CONVERT(DATE, '2021-07-01')), CONVERT(DATE, '2021-07-01')) EDATE
          FROM MASTER..SPT_VALUES
         WHERE TYPE = 'P' 
           AND NUMBER < CEILING(DATEDIFF(DD, 
                                         DATEADD(DD, 1 - DATEPART(DW, '2021-07-01'), '2021-07-01'), 
                                         DATEADD(DD, 7 - DATEPART(DW, '2021-07-31'), '2021-07-31')
		                                ) / 7.0
		                        )
		) A