MSSQL - 함수 - WorkDay(영업일) 계산

 
-- select [MFUNC_GetWorkDay](getdate(), -3)
-- select [MFUNC_GetWorkDay]('20231111', 3)
CREATE FUNCTION [MFUNC_GetWorkDay]
(
@startDate date,
@interval int
)
RETURNS INT
AS
BEGIN
--Declare @startDate date, @interval int
--SET @startDate = '20230607' --getdate()
--SET @interval = -3
DECLARE @holidayCalendar TABLE (CalendarDate date NULL)
DECLARE @AddDate date
if @interval is null set @interval = 0
-- 휴일만 등록
insert into @holidayCalendar (CalendarDate)
values ('20230101'), ('20230121'), ('20230122'), ('20230123'), ('20230124')
, ('20230301')
, ('20230505'), ('20230527'), ('20230529')
, ('20230606')
, ('20230815')
, ('20230928'), ('20230929'), ('20230930')
, ('20231003'), ('20231009')
, ('20231225')
, ('20240101')
, ('20240209'), ('20240210'), ('20240211'), ('20240212')
, ('20240301')
, ('20240410')
, ('20240505'), ('20240506'), ('20240515')
, ('20240606')
, ('20240815')
, ('20240916'), ('20240917'), ('20240918')
, ('20241003'), ('20241009')
, ('20241225')
--select * from @holidayCalendar
Declare @rCnt int, @cCnt int, @flag int
SET @rCnt = 0
SET @cCnt = 0
If @interval >= 0
set @flag = 1
else
set @flag = -1
set @AddDate = @startDate
while abs(@interval) > @rCnt
begin
set @cCnt = @cCnt + 1
set @AddDate = DateAdd(day,  @cCnt * @flag, @startDate)
if (datepart(dw, @AddDate) = 1) 
OR (datepart(dw, @AddDate) = 7)
OR exists(SELECT CalendarDate FROM @holidayCalendar WHERE CalendarDate = @AddDate)
begin
set @interval = @interval + (1 * @flag)
end
set @rCnt = @rCnt + 1
end
RETURN CONVERT(VARCHAR(10), @AddDate, 112)
END