/*
Declare @startDate date, @interval int
SET @startDate = '2011-05-01'
SET @interval = 3
*/
DECLARE @tblCalendar TABLE (calendarDate date NOT NULL, Holiday int NOT NULL)
Declare @rCnt int, @cCnt int, @flag int
SET @rCnt = 0
SET @cCnt = 0
If @interval >= 0
Begin
SET @flag = 1
INSERT INTO @tblCalendar SELECT calendarDate, Holiday FROM Calendar with(nolock) WHERE calendarDate > @startDate AND calendarDate < DateAdd(dd, @interval*2, @startDate);
End
Else
Begin
SET @flag = -1
INSERT INTO @tblCalendar SELECT calendarDate, Holiday FROM Calendar with(nolock) WHERE calendarDate >= DateAdd(dd, @interval*2, @startDate) AND calendarDate < @startDate ;
End
while abs(@interval) > @rCnt
Begin
--select @cCnt
SET @cCnt = @cCnt 1
if (datepart(dw, dateAdd(dd, @cCnt*@flag, @startDate)) = 1 OR datepart(dw, dateAdd(dd, @cCnt*@flag, @startDate)) = 7)
OR exists(SELECT CalendarDate FROM @tblCalendar WHERE CalendarDate = dateAdd(dd, @cCnt*@flag, @startDate) AND Holiday = 1)
Begin
--select dateAdd(dd, @cCnt, @startDate)
SET @interval = @interval 1
End
SET @rCnt = @rCnt 1
End
--select dateAdd(dd, @cCnt*@flag, @startDate) AS [최종]
RETURN dateAdd(dd, @cCnt*@flag, @startDate)