Hee's Story

시작일이 존재 : @start_date
종료일이 존재 : @end_date

working_day라는 Table에 2010년도 휴무일을 제외한 날짜 존재함.



create function working_day_func (@start_date datetime, @end_date datetime)
returns int
as
begin
 declare @mi_time int
 declare @s_time int
 declare @e_time int
 declare @working_day int
 
 set @s_time = case when datepart(weekday, @start_date) = 1 then '0'
        when datepart(weekday, @start_date) = 7 then '0'
        else datediff(mi, convert(char(8), @start_date, 24),'23:59:00')
      end
 set @e_time = case when datepart(weekday, @end_date) = 1 then '0'
        when datepart(weekday, @end_date) = 7 then '0'
        else datediff(mi,'00:00:00',convert(char(8), @end_date, 24))
      end
 select @working_day = count(working_day),
     @mi_time = case when count(working_day) >= 3 then (count(working_day)-2)*1440
         else '0'
       end
 FROM working_day
 WHERE working_day BETWEEN convert(char(10), @start_date, 120) AND @end_date

 set @mi_time = case when @working_day <= 1 then datediff(mi, @start_date, @end_date)
      else (@mi_time+@s_time+@e_time+1)
       end
 return (@mi_time)
end