DATEPART, DATEADD, EOMONTH 함수를 사용하여 월간 집계 UPSERT용 SP 만들기

2023.01.24 - [개발/Windows] - 배치 파일(.bat)과 작업 스케쥴러를 사용하여 To-Do 데이터 월간별 집계하기

배치 파일(.bat)과 작업 스케쥴러를 사용하여 To-Do 데이터 월간별 집계하기

매월 1일 오전 12시에 실행되는 SP입니다. 해당 연도와 월의 데이터가 존재하지 않으면 INSERT를 하고 존재하면 UPDATE를 실행합니다.

 

CREATE PROCEDURE [dbo].[sp_ROUTINE_Ups_ToDoMontlyStat]
(
	@YYYY				int = 0
,	@MM				int = 0
)
AS
BEGIN

	DECLARE @_Date		datetime
	DECLARE @_ToDo_D_CNT	int
	DECLARE @_ToDo_C_CNT	int
	DECLARE @_ToDo_Y_CNT	int
	DECLARE @_ToDo_N_CNT	int

	-- 해당 연도-월의 총 일 수
	SET @_Date = CONCAT(@YYYY, '-', FORMAT(@MM,'D2') , '-', '01')
	--SELECT @_ToDo_D_CNT = DATEPART(day, DATEADD(day, -1, DATEADD(month, 1,  @_Date)))
        SELECT @_ToDo_D_CNT = DATEPART(day, EOMONTH(@_Date))
    
	-- 해당 연도-월 총 기록 수
	SELECT @_ToDo_C_CNT = COUNT(*)
	  FROM dbo.ToDo
	 WHERE YEAR(DoneDate) = @YYYY
	   AND MONTH(DoneDate) = @MM
	 GROUP BY YEAR(DoneDate), MONTH(DoneDate)

	 -- 해당 연도-월 총 달성 수
	 SELECT @_ToDo_Y_CNT = COUNT(*)
	  FROM dbo.ToDo
	 WHERE YEAR(DoneDate) = @YYYY
	   AND MONTH(DoneDate) = @MM
	   AND (Task1YN = 1 and Task2YN = 1 and Task3YN = 1)
	 GROUP BY YEAR(DoneDate), MONTH(DoneDate)
	
	    IF NOT EXISTS ( SELECT ToDo_YYYY
		               FROM dbo.ToDoMontlyStat
			      WHERE ToDo_YYYY = @YYYY
				AND ToDo_MM = @MM)  
		BEGIN
		
			INSERT INTO dbo.ToDoMontlyStat
			(
				ToDo_YYYY
			,	ToDo_MM
			,	ToDo_D_CNT
			,	ToDo_C_CNT
			,   ToDo_Y_CNT
			,	ToDo_N_CNT
			,	CreateDate
			)
			VALUES
			(
				@YYYY		
			,	@MM		
			,	@_ToDo_D_CNT		
			,	@_ToDo_C_CNT			
			,	@_ToDo_Y_CNT		
			,	@_ToDo_C_CNT - 	@_ToDo_Y_CNT
			,	GETDATE()
			)

		END
		ELSE 
		BEGIN

			UPDATE dbo.ToDoMontlyStat
			   SET ToDo_YYYY = @YYYY
				,  ToDo_MM = @MM
				,  ToDo_D_CNT = @_ToDo_D_CNT
				,  ToDo_C_CNT = @_ToDo_C_CNT
				,  ToDo_Y_CNT = @_ToDo_Y_CNT
				,  ToDo_N_CNT = @_ToDo_C_CNT - 	@_ToDo_Y_CNT
				,  UpdateDate = GETDATE()
			 WHERE ToDo_YYYY = @YYYY
			   AND ToDo_MM = @MM
		
		END
	
END
GO

사용한 함수

FORMAT

FORMAT(@MM,'D2') @MM이 데이터 타입이 int로 10자리 이하인 경우에는 1자리로 되면 datetime으로 변환할 때 오류가 날 줄 알고 0을 채웠는데, 정수로 넣어도 @_Date에 올바르게 datetime으로 변환됩니다.

 

DATEPART

지정한 날짜의 부분을 정수로 반환해 주는 함수입니다.

DATEPART ( datepart , date )

datepart 부분이 정수를 반환할 date 인수의 특정 부분입니다. 약어는 사용할 수 있지만 사용자 정의 변수 항목은 허용하지 않습니다.

 

사용예시)

year, quater, month, dayofyear, day, week, weekday, hour, minute, millisecond, microsecond, nanosecond, tzoffset, iso_week

 

date 인자는 데이터 타입이 date, datetime, datetimeoffset, datetime2, smalldatetime, time를 확인하며 계산 식이나, 문자열 리터럴, 사용자 정의 변수를 허용합니다.

 

DATEADD

지정한 날짜의 부분을 원하는 정수로 계산하여 수정된 날짜/시간 값을 반환하는 함수입니다. 반환 값의 데이터 형식은 제공된 인수에 따라 달라질 수 있습니다. 지정한 날짜의 데이터 형식이 문자열 리터럴로 표시한 날짜인 경우 datetime 값을 반환합니다. 

DATEADD (datepart , number , date )

SELECT DATEADD(month, 1, '20230830');
SELECT DATEADD(month, 1, '2023-08-31');
-- 2023-09-30 00:00:00.000 datetime 반환

DATEPART(day, DATEADD(day, -1, DATEADD(month, 1,  @_Date)))

 

@_Date = '2023-03-01'

 

STEP 1. DATEADD(month, 1,  @_Date)

result : 2023-04-01 00:00:00.000

 

STEP 2. DATEADD(day, -1, STEP 1.result)

result :2023-03-31 00:00:00.000

 

STEP 3.  DATEPART(day, STEP 2.result)

result : 31

EOMONTH

지정한 날짜가 포함된 달의 마지막 날을 반환하는 함수입니다. 선택 사항으로 추가적으로 계산할  월을 정수로 입력하면 해당하는 월의 마지막 날을 반환합니다. 

EOMONTH ( start_date [, month_to_add ] )

DATEPART(day, EOMONTH(@_Date))

@_Date = '2023-03-01'

 

STEP 1. EOMONTH(@_Date)

result : 2023-03-31

 

STEP 2. DATEPART(day, EOMONTH(STEP 1.result))

result : 31

 

결과

exec sp_ROUTINE_Ups_ToDoMontlyStat 2023,1

작업 스케줄러로 SP를 실행하는 웹 페이지에서 SP 호출 파라미터는 현재에서 한 달 전의 값으로 전달하여 사용합니다.

 

사용예시)

2023년 2월 1일 오전 12시 작업 :  sp_ROUTINE_Ups_ToDoMontlyStat 2023,1

 

참고사이트
https://learn.microsoft.com/ko-kr/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ko-kr/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ko-kr/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver16