MS-SQL에서 연장 근무 가능시간 계산하기 (주간)

특정날짜를 입력 받아  주간 기준으로 연장근무 가능시간 계산하는 로직을 공유합니다.

1. 주간 연장근무 가능시간 계산 하기

     1)  계산식 (모든항목 분으로 환산하여 계산)

          - @가능시간 = @주간최대근무시간 - @근무시간  + @휴가시간 - @연장근무시간

     2) 주간최대근무시간 (법정근로시간 52시간 기준)

          - @주간최대근무시간 = 52 * 60  

     3) 근무시간 (weekday를 이용하여 계산)

          - datepart(weekday)는 일요일 시작으로 순서대로 1(일),2(월),3(화),4(수),5(목),6(금),7(토) 숫자값을 출력 한다.

          -  @근무시간 = 8 * (@weekday -1) * 60

      4) 휴가시간

      5) 연장근무시간

2. 쿼리 예시 

CREATE FUNCTION [dbo].[GET_AVAILABLE_TIME]
(				 
				@work_date				nvarchar(10)		
)
RETURNS INT
AS
BEGIN

	-------------------------------------------------------------------------------------------
	--	주간 OT 집계
	
	DECLARE		@week_day				int = DATEPART(WEEKDAY, @work_date)
	,			@week_start				date		-- 주간시작일자
	,			@week_end				date		-- 주간종료일자
	,			@standard_work_hour		int = 8		-- 일근수시간
	,			@week_max_minute		int = 3120	-- 주간최대근무시간(52시간)
	,			@work_minute			int = 0		-- 주간누적근수시간(분환산)
	,			@use_vacation_minute	int = 0		-- 주간연차(휴가)사용시간(분환산)
	,			@use_overtime_minute	int = 0		-- 주간연장근무시간(분환산)	
	,			@available_time			int = 0		-- 가용시간

	SET			@week_start = DATEADD(DAY, 2 - @week_day, @work_date)
	SET			@week_end = DATEADD(DAY, 6, @week_start)
	SET			@work_minute = (@standard_work_hour*(@week_day-1)*60)

	-------------------------------------------------------------------------------------------
	-- 휴가사용시간
	SELECT 		
		@use_vacation_minute = ISNULL(SUM([휴가시간컬럼]),0)
	FROM [휴가사용테이블명]
	WHERE 		
		[사용날짜] BETWEEN @week_start AND @week_end	

	-------------------------------------------------------------------------------------------
	-- 연장근무시간
	SELECT
		@use_overtime_minute = ISNULL(SUM([연장근무사용컬럼]),0)
	FROM [연장근무사용테이블명]
	WHERE		
		[사용날짜] BETWEEN @week_start AND @week_end
	
	SET @available_time = @week_max_minute - @work_minute + @use_vacation_minute - @use_overtime_minute		
	RETURN @available_time
END
GO

실행 쿼리
SELECT [dbo].[GET_AVAILABLE_TIME]('2023-12-11')