SQL 이 여러가지 장점에도 불구하고 기본적으로 SQL에서는 DateAdd 라는 간단한 함수만 지원이 되는 관계로 주말을 제외한 영업일 구하는게 그리 쉬운 문제는 아니더라는 점이다. 특정일자로 부터 n 영업일 이후의 날짜를 구하는 함수를 만들어 봤다. 엑셀에서는 "=WORKDAY(DATE(2011,4,0.4),8)" 처럼 간단히 영업일이 구해진다.
자 이제 dateAdd 만 지원하는 쿼리에서는 어떻게 해야 할까... SQL 2008 기준입니다. date 와 같은 생소한 데이타 타입이 있습니다. 이전 버젼에서의 호환성 여부는 체크하지 못했습니다.
그나마 다행인게 datepart 라는 함수를 이용해서 요일정보는 읽어 올수 있다. 일요일이 1, 토요일이 7 이다.
먼저 지정한 날짜와 n 영업일을 위한 변수를 준비하고 일요일, 토요일을 건너 뛰도록 간단한 if 문을 넣어 원하는 영업일을 계산 할 수 있도록 하자. 지정한 영업일을 세기 위한 변수와 반복 회수를 정하는 변수를 만들어 주고 지정한 영업일보다 작은 동안 돌 수 있도록 반복문으로 감싸 주면 끝...그런데 지금 만든 것은 단순히 토요일, 일요일만 제외 시키는 것이므로 꿀같은 공휴일은 계산에서 빠진 상태다. 쩝... 어떤 방법을 이용하더라도 공휴일을 따로 계산해낼 방법이 없으므로 공휴일이 표시되는 달력 테이블이 하나 필요하다. 날짜와 공휴일 여부만 체크하도록 추가했다.
CREATE TABLE [dbo].[Calendar](
[CalendarDate] [date] NOT NULL,
[Holiday] [int] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[CalendarDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
날짜 채우기 위해서 while 문을 살짝
declare @cnt int
SET @cnt = -300
while @cnt < 1000
Begin
INSERT INTO Calendar VALUES (DateAdd(dd, @cnt, getdate()), 0)
SET @cnt = @cnt 1
End
날짜를 채웠으니 휴일을 정의해 줘야 겠죠. 귀찮지만 내년 달력이 나오는 시점에 맞춰서 또 뭔가를 해야 겠군요...ㅋㅋㅋ
update Calendar SET holiday = 1
WHERE CalendarDate IN (
'2011-01-01'
,'2011-02-02'
,'2011-02-03'
,'2011-02-04'
,'2011-03-01'
,'2011-05-05'
,'2011-05-10'
,'2011-06-06'
,'2011-08-15'
,'2011-09-11'
,'2011-09-12'
,'2011-10-03'
,'2011-12-25'
,'2012-01-01'
,'2012-01-22'
,'2012-01-23'
,'2012-01-24'
)
전체 코드의 모습입니다. 이 코드의 내용을 기반으로 간단한 함수를 만들면 작업 끝... 사용자 정의 함수는 직접 만들어 보세요...
Declare @interval int, @startDate date
set @startDate = '2011-03-28'
set @interval = 8
Declare @rCnt int, @cCnt int
SET @rCnt = 1
SET @cCnt = 1
SELECT * INTO #tblCalendar FROM Calendar with(nolock) WHERE calendarDate > @startDate AND calendarDate < DateAdd(dd, @interval*2, @startDate)
while @interval > @rCnt
Begin
if datepart(dw, dateAdd(dd, @cCnt, @startDate)) > 1 AND datepart(dw, dateAdd(dd, @cCnt, @startDate)) < 7
AND exists(SELECT CalendarDate FROM #tblCalendar WHERE CalendarDate = dateAdd(dd, @cCnt, @startDate) AND Holiday = 0)
Begin
SET @rCnt = @rCnt 1
End
SET @cCnt = @cCnt 1
End
select dateAdd(dd, @cCnt, @startDate), @cCnt, @rCnt