SQL Server - 무작위 날짜 데이터 생성 후 비어있는 날 채우기 Create a table with random date then Group by Date with no exception

랜덤한 데이터 만들기

2020년 안에서 무작위 날짜를 1000개 만든다. 

DECLARE @Id int
DECLARE @FromDate DATE
DECLARE @ToDate DATE

Set @Id = 1
SET @FromDate = '2020-01-01'
SET @ToDate = '2020-12-31'

While(@Id <=1000)
Begin
	DECLARE @Days INT = DATEDIFF(day, @FromDate, @ToDate)
	DECLARE @Random INT = ROUND(((@Days)*RAND()),0)
	
    Insert into dbo.tbSalesTransactions values(@Id, DATEADD(day, @Random, @FromDate), 
	CAST(@Id as nvarchar(10))+'th Sales transaction.')
	
    Print @Id
	Set @Id = @Id + 1
End

 

 

 

5월을 지운다

Delete from tbSalesTransactions
where date like '2020-05-%'  

 

 

그리고 월별 건수를 뽑는다. 

select 2020 as Year , DATEPART(MONTH, date) as Month, COUNT(*) AS Cnt from tbSalesTransactions
group by datepart(month, date)
Order by Month

 

 

결과

5월이 빠진 데이터가 완성이 되었다.

하지만 5월이 0으로 표시가 되게 해야 한다.

데이터가 없는 날짜에 결과 값이 표시가 되어야 한다. 그러려면 날짜 테이블을 만들어 LEFT OUTER JOIN을 해서 해결해야 한다.

 

 

날짜 테이블 만들기 

CREATE TABLE tbl2020Date(Id int primary key, Date Date)

DECLARE @DATE DATE, @ID INT
SET @DATE = '2019-12-31'
SET @ID = 1

WHILE (@ID <=365)
BEGIN
INSERT INTO tbl2020Date values(@ID, DATEADD(DAY,1,@DATE))
SET @ID = @ID +1
SET @DATE = DATEADD(DAY,1,@DATE)
END

2020년 모든 날짜가 들어 있는 테이블을 만듬.(임시 테이블로 만들걸)

 

 

 

만든 날짜 테이블과 기존 테이블과 JOIN으로 1월부터 12월까지 월별 건수 집계 확인하기

SELECT 2020 as YEAR,
	DATEPART(MONTH, A.Date) as Month,
	COUNT(B.ID) AS CNT
FROM tbl2020Date as A
LEFT OUTER JOIN tbSalesTransactions as B
ON A.Date = B.date
GROUP BY DATEPART(MONTH, A.Date)
ORDER BY Month

 

 

 

결과 확인

왼쪽) 최종 결과 화면, 오른쪽) 5월이 빠져있는 결과

5월이 들어가 있고 CNT는 0으로 표시 되었다