時間用function 來計算...如此精確.

SET QUOTED_IDENTIFIER ON
GO
--目前只對對B班有效
CREATE function [dbo].[per_GetLeaveMinutes]
(
@start_leave_date datetime,
@end_leave_date datetime,
@workshit_name nvarchar(10)
)
returns int
begin
--declare @start_leave_date datetime
--declare @end_leave_date datetime

 

declare @leave_hours int--總請假分鐘數

--班別第一時間段起始分鐘
declare @start_workshift_time1 int
declare @end_workshift_time1 int

--班別第二時間段起始分鐘
declare @start_workshift_time2 int
declare @end_workshift_time2 int

declare @current_date datetime
declare @start_time int
declare @end_time int

select @leave_hours=0
--設置班別各時間段參數。
if @workshit_name='B'
    select @start_workshift_time1=8*60,@end_workshift_time1=12*60,
       @start_workshift_time2=13*60+30,@end_workshift_time2=17*60+30
else if @workshit_name='A'
    select @start_workshift_time1=7*60+30,@end_workshift_time1=11*60+30,
       @start_workshift_time2=13*60+30,@end_workshift_time2=17*60+30

select @current_date=convert(nvarchar,@start_leave_date,112)

while @current_date<=@end_leave_date
begin
   if not exists(select calendar_id from mis..KQ_CALENDAR where item_date=@current_date)
   begin
       if convert(nvarchar,@current_date,112)=convert(nvarchar,@start_leave_date,112)
           select @start_time=datediff(minute,@current_date,@start_leave_date)
       else
           select @start_time=@start_workshift_time1
      
       if convert(nvarchar,@current_date,112)=convert(nvarchar,@end_leave_date,112)
           select @end_time=datediff(minute,@current_date,@end_leave_date)
       else
           select @end_time=@end_workshift_time2

       --select @start_time,@end_time
       --計算第一個時間段
       if(@start_time<@end_workshift_time1)
          select @leave_hours=@leave_hours+case when @end_time>@end_workshift_time1 then @end_workshift_time1 when @end_time<@start_workshift_time1 then @start_workshift_time1 else @end_time end-
                              case when @start_time<@start_workshift_time1 then @start_workshift_time1 else @start_time end
        --計算第二個時間段
        if(@start_time<@end_workshift_time2 and @end_time>@start_workshift_time2)
          select @leave_hours=@leave_hours+case when @end_time>@end_workshift_time2 then @end_workshift_time2 else @end_time end-
                              case when @start_time<@start_workshift_time2 then @start_workshift_time2 else @start_time end
       
   end
   select @current_date=@current_date+1
end
return @leave_hours
end