[MSSQL] 시작일자 종료일자 사이 모든 일자를 조회하는 방법

쿼리문을 작성하다 보면 테이블에 데이터는 없지만 시작일자와 종료일자 사이의 일자(날짜)를 포함하여 조회해야 할 때가 있다. 날짜만 들어있는 테이블을 생성해서 조인하여 사용해도 되지만, 해당 기간의 데이터를 동적 뷰로 생성하여 일시적으로 사용할 수도 있다. 날짜 뷰를 생성하기 위해서는 재귀 쿼리(WITH CTE) 또는 master..spt_values 시스템 테이블을 사용하면 된다.

 

MSSQL 기간내 모든 일자(날짜) 조회

 

■ 재귀 쿼리(WITH CTE)로 날짜 뷰 만들기

WITH DateRange(Dates) AS 
(
    SELECT CONVERT(DATE, '2021-12-01') --시작일자
     UNION ALL
    SELECT DATEADD(d, 1, Dates)
      FROM DateRange 
     WHERE Dates < CONVERT(DATE, '2021-12-07') --종료일자 
)

SELECT *
  FROM DateRange
OPTION (MAXRECURSION 0)

 

 

재귀 쿼리를 사용하여 12.01~12.07까지 날짜를 가진 가상 테이블(DateRange)을 만들 수 있다. 차후 이 가상 테이블과 실제 테이블을 아우터 조인하여 테이블에 존재하지 않는 날짜까지 조회를 할 수 있다.

 

OPTION (MAXRECURSION 0) 구문은 재귀 쿼리의 반복 횟수를 설정하는 부분이다. "0"은 무한 반복이며, 이 구문을 빼면(생략) 최대 100회까지만 반복하여 데이터가 생성된다.

 

■ 예제 1

DECLARE @StartDate VARCHAR(10)
DECLARE @EndDate VARCHAR(10)

SET @StartDate = '2021-12-01'
SET @EndDate   = '2021-12-07';

WITH DateRange(Dates) AS 
(
    SELECT CONVERT(DATE, @StartDate)
     UNION ALL
    SELECT DATEADD(d, 1, Dates)
      FROM DateRange 
     WHERE Dates < CONVERT(DATE, @EndDate)    
), 
Attendance AS 
(
    SELECT CONVERT(DATE, '2021-12-01') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-02') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-06') AS AttDate, '출근' AS AttStat
)

SELECT a.Dates 
     , b.AttStat
  FROM DateRange AS a
  LEFT OUTER JOIN Attendance AS b
    ON a.Dates = b.AttDate
OPTION (MAXRECURSION 0)

 

 

Attendance(근태) 테이블과 DateRange(날짜 뷰)를 아우터 조인하여 Attendance 테이블에 존재하지 않는 일자(날짜)까지 포함하여 데이터를 조회할 수 있다.

 

■ 예제 2

DECLARE @StartDate VARCHAR(10)
DECLARE @EndDate VARCHAR(10)

SET @StartDate = '2021-12-01'
SET @EndDate   = '2021-12-07';

WITH DateRange(Dates) AS 
(
    SELECT CONVERT(DATE, @StartDate)
     UNION ALL
    SELECT DATEADD(d, 1, Dates)
      FROM DateRange 
     WHERE Dates < CONVERT(DATE, @EndDate)    
), 
Attendance AS 
(
    SELECT CONVERT(DATE, '2021-12-01') AS SDate
         , CONVERT(DATE, '2021-12-03') AS EDate
         , '출근' AS AttStat 
     UNION ALL
    SELECT CONVERT(DATE, '2021-12-06') AS SDate
         , CONVERT(DATE, '2021-12-07') AS EDate
         , '출근' AS AttStat 
)

SELECT a.Dates 
     , b.EDate
     , b.AttStat
  FROM DateRange AS a
  LEFT OUTER JOIN Attendance AS b
    ON a.Dates BETWEEN b.SDate AND b.EDate
OPTION (MAXRECURSION 0)

 

 

데이터가 시작일자(SDate)와 종료일자(EDate) 형태로 생성되어 있으면, BETWEEN을 사용하여 아우터 조인을 하면 해당 구간의 데이터를 포함하여 조회할 수 있다.

 

 

■ master..spt_value 테이블로 날짜 뷰 만들기

SELECT DATEADD(d, number, CONVERT(DATE, '2021-12-01')) Dates
  FROM master..spt_values 
 WHERE type = 'P' 
   AND number <= DATEDIFF(d, CONVERT(DATE, '2021-12-01'), CONVERT(DATE, '2021-12-07'))

 

 

master..spt_values 시스템 테이블을 사용하면 재귀 쿼리(WITH CTE)를 사용하여 날짜 뷰를 만든 것처럼 날짜 뷰를 쉽게 만들 수 있다. 재귀 쿼리가 아니기 때문에 OPTION (MAXRECURSION 0) 구문은 사용하지 않아도 된다.

 

■ 예제 1

DECLARE @StartDate VARCHAR(10)
DECLARE @EndDate VARCHAR(10)

SET @StartDate = '2021-12-01'
SET @EndDate   = '2021-12-07';

WITH DateRange(Dates) AS 
(
    SELECT DATEADD(d, number, CONVERT(DATE, @StartDate)) Dates
      FROM master..spt_values 
     WHERE type = 'P' 
       AND number <= DATEDIFF(d, CONVERT(DATE,@StartDate),CONVERT(DATE, @EndDate))   
), 
Attendance AS 
(
    SELECT CONVERT(DATE, '2021-12-01') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-02') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-06') AS AttDate, '출근' AS AttStat
)

SELECT a.Dates 
     , b.AttStat
  FROM DateRange AS a
  LEFT OUTER JOIN Attendance AS b
    ON a.Dates = b.AttDate

 

 

재귀 쿼리를 사용하여 만든 쿼리와 동일한 결과를 얻을 수 있다.

 

■ FROM 절 인라인 뷰(Inline View)로 사용

WITH Attendance AS 
(
    SELECT CONVERT(DATE, '2021-12-01') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-02') AS AttDate, '출근' AS AttStat UNION ALL
    SELECT CONVERT(DATE, '2021-12-06') AS AttDate, '출근' AS AttStat
)

SELECT a.Dates 
     , b.AttStat
  FROM (
         SELECT DATEADD(d, number, CONVERT(DATE, '2021-12-01')) Dates
           FROM master..spt_values 
          WHERE type = 'P' 
            AND number <= DATEDIFF(d, CONVERT(DATE, '2021-12-01')
                                    , CONVERT(DATE, '2021-12-07'))
       ) AS a
  LEFT OUTER JOIN Attendance AS b
    ON a.Dates = b.AttDate

 

 

재귀 쿼리가 아니기 때문에 WITH 구문을 사용하지 않고 FROM 절에 인라인 뷰로 사용하여 쿼리를 작성할 수 있다.

 

참고 : https://gent.tistory.com/451