[MS-SQL] 특정 기간에 날짜(일, 월, 시간) 생성하는 쿼리를 작성하는 방법은??(MASTER..SPT_VALUES 시스템 테이블에 답이 있다.)

떠돌이개발자Nov 8, 2023

특정 기간 동안 통계 자료를 뽑을 때 , 혹은 차트 도표의 자료의 데이터를 추출할 때 월별 혹은 일자별로 뽑아야하는 경우가 있습니다. 모든 데이터가 존재하면 문제가 되지 않지만, 특정 날짜가 누락되거나, 특정 월의 데이터가 없을 경우, 통계 쿼리 결과가 그 월 혹은 일은 빠지기 때문에 차트에 표기하거나 도표에 보여줄때 데이터가 없는경우 표기하지않는 경우가 생깁니다.

이럴 때는 어떻게 해야할까요?

특정 기간에 날짜(일, 월, 시간) 생성하는 쿼리를 작성하는 방법

매년 1월부터 12월까지 달력정보를 특정 테이블에 INSERT 한 후 가져오는 방법도 있으나 이 방법은 매년 달력 일 정보를 INSERT 해줘야하는 불편함이 생깁니다. 뭐 미리 5년치 INSERT 하신다면 상관없으려나… 그것보다는 특정 기간 동안의 일 또는 월을 모두 생성하는 쿼리를 만들면 수월해집니다.

MS-SQL에서 제공하는 MASTER..SPT_VALUES 시스템 테이블을 이용하는 방법입니다.

다음 쿼리는 임의 기간 동안의 모든 날짜를 만들어줍니다.

declare 	@P_DATE_FR			VARCHAR(8) = ''
declare		@P_DATE_TO			VARCHAR(8) = ''	

set @P_DATE_FR ='20210701'
set @P_DATE_TO ='20211130'
 
SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, @P_DATE_FR), 112) AS 'MAKE_DATE' 
FROM MASTER..SPT_VALUES 
WHERE TYPE = 'P' 
AND NUMBER <= DATEDIFF(D, @P_DATE_FR, @P_DATE_TO)

[실행결과]

다음은 특정기간 동안의 년월 데이터를 생성하는 예제입니다.

declare 	@P_DATE_FR			VARCHAR(8) = ''
declare		@P_DATE_TO			VARCHAR(8) = ''	

set @P_DATE_FR ='20210701'
set @P_DATE_TO ='20211121'
 
--조회기간의 년월 데이터 생성(월별)
SELECT SUBSTRING(CONVERT(VARCHAR, DATEADD(M, NUMBER, @P_DATE_FR), 112),1,6) AS 'DATE' 
FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(M, @P_DATE_FR, @P_DATE_TO)

[실행결과]

다음 예제는 날짜별 24시간을 표기하는 방법입니다.

DECLARE @start_dt DATETIME = '2021-07-12 00:00:00.000';
DECLARE @end_dt DATETIME = '2021-07-13 00:00:00.000';
 
WITH    AllDays
AS ( SELECT   @start_dt AS create_date, 1 AS time_level
    UNION ALL
    SELECT   DATEADD(hour, 1, create_date), time_level + 1
    FROM     AllDays
    WHERE    create_date < @end_dt )
 
 
SELECT create_date, time_level
FROM   AllDays OPTION (MAXRECURSION 0)

[실행결과

기존 통계 프로시저의 내용을 최대한 활용하기 위해 위와 같은 방법을 사용해보았습니다.

declare 	@P_DATE_FR			VARCHAR(8) = ''
declare		@P_DATE_TO			VARCHAR(8) = ''	
 
set @P_DATE_FR ='20210701'
set @P_DATE_TO ='20211121' 

DECLARE @TBL_MONTH TABLE (ACTION_TYPE VARCHAR(20), ACTION_MONTH VARCHAR(6))
  

INSERT INTO @TBL_MONTH(ACTION_TYPE, ACTION_MONTH)
SELECT 'SYSTEM', SUBSTRING(CONVERT(VARCHAR, DATEADD(M, NUMBER, @P_DATE_FR), 112),1,6)  
FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(M, @P_DATE_FR, @P_DATE_TO)

select * from @TBL_MONTH

[실행결과]