[MSSQL] 일별통계 데이터 작성시 spt_values 활용

 

일별통계 쿼리 작성시 데이터가 없어 데이터가 빠지는 현상이 있다.

 

이럴땐 spt_values 을 활용하여 LEFT OUTER JOIN 을 걸어 데이터 추출이 가능하다

 

DECLARE @S_DATE smalldatetime,@E_DATE smalldatetime
SET @S_DATE = CONVERT(smalldatetime, '2019-02-01') -- 특정월 시작일자
SET @E_DATE = DATEADD(day, -1,DATEADD(MONTH, 1,DATENAME(YEAR,@S_DATE) + DATENAME(month,@S_DATE)+'01')) -- 특정월 종료일자

SELECT CONVERT(CHAR(10), DATEADD(d, NUMBER, @S_DATE),120) AS DT
FROM MASTER..SPT_VALUES WITH(NOLOCK)
WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, @S_DATE), 120) <= @E_DATE

 

 

LEFT OUTER JOIN 으로 일별 통계 쿼리 작성

-- 일별 GROUP BY

	DECLARE @S_DATE smalldatetime,@E_DATE smalldatetime
	SET @S_DATE = CONVERT(smalldatetime, CONCAT(@regDate , '-01')) 
	SET @E_DATE = DATEADD(day, -1,DATEADD(MONTH, 1,DATENAME(YEAR,@S_DATE) + DATENAME(month,@S_DATE)+'01')) -- 특정월 종료일자

	SELECT DT.DT							AS regDate
	, ISNULL(SUM(openCnt),0)				AS openCnt
	, ISNULL(SUM(chargeCnt),0)			AS chargeCnt
	, ISNULL(SUM(freeCnt),0)				AS freeCnt
	, ISNULL(SUM(openAmount),0)  			AS openAmount 
	, ISNULL(SUM(chargeAmount),0)			AS chargeAmount
	, ISNULL(SUM(voiceAmount),0) 			AS voiceAmount
	, ISNULL(SUM(etcAmount),0)			AS etcAmount
	, ISNULL(SUM(freeAmount),0)			AS freeAmount
	, ISNULL(SUM(etcAmount),0)			AS etcAmount
	, ISNULL(SUM(turnDiscount),0)			AS turnDiscount
	, ISNULL(SUM(autoDiscount),0)			AS autoDiscount
	, ISNULL(SUM(promotionDiscount),0)	AS promotionDiscount
	, ISNULL(SUM(totalAmount),0)			AS totalAmount
	FROM  (
		SELECT CONVERT(CHAR(10), DATEADD(d, NUMBER, @S_DATE),120) AS DT
		FROM MASTER..SPT_VALUES WITH(NOLOCK)
		WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, @S_DATE), 120) <= @E_DATE
	) AS DT 
	LEFT OUTER JOIN #tbl AS tbl
	ON DT.DT = tbl.regDate
	GROUP BY DT.DT
	ORDER BY DT.DT ASC

 

출처 : https://aspdotnet.tistory.com/2320