计算工作日函数修正问题

[sea0302]

/*--------------------------------------------------------------------------------------------------------------------------
这个函数功能是按科室上下班时间,节假日,排除非工作日和上下时间外,实现按科室,开始工作日期加上分钟数,得到一个有效的工作日期带时分
部门上下班表:T_Biz_Department
节假日表:T_Biz_HolidaiesSchedule
比如:分样组,上班8:30,下班16:15
如果输入的开始日期在工作日时,但时间小时和分钟不在工作时间范围的,如2014-1-1 8:20,那就按上班时间8:30计算,如果输入是2014-1-1 16:20,那就按第2天的8:30计算
输入非工作日时间比如,2014-1-4 8:20或2014-1-5 8:20,则自动调到下一个工作日的8:30开始计算
如输入是2014-1-3 16:10,加上60分钟,计算出来的结果应该是2014-1-6 9:25
现在此函数已实现此功能,因上下班时间变成:分样组:上午8:30-11:45,下午12:30-16:15,计算有效日期带时分需要把中午这段非工作时间排除,需要修正下,刚如何调整,我改来改去都有问题!
SELECT [dbo].[Fn_GetDateByValidTime]('分样组','2014-1-1 11:52:01.707',60)
*/--------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[T_Biz_Department] (
	[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_T_SYS_Department_ID] DEFAULT (newid()),
	[CreateTime] [datetime] NULL CONSTRAINT [DF_T_SYS_Department_CreateTime] DEFAULT (getdate()),
	[LastUpdateTime] [datetime] NULL CONSTRAINT [DF_T_SYS_Department_LastUpdateTime] DEFAULT (getdate()),
	[DisplayOrder] [int] NULL,
	[OrgID] [uniqueidentifier] NULL,
	[Code] [nvarchar](32) NULL,
	[Name] [nvarchar](16) NULL,
	[Type] [nvarchar](8) NULL,
	[Address] [nvarchar](64) NULL,
	[Phone] [nvarchar](32) NULL,
	[Fax] [nvarchar](32) NULL,
	[PostCode] [nvarchar](16) NULL,
	[Email] [nvarchar](32) NULL,
	[PersonInCharge] [nvarchar](32) NULL,
	[Remarks] [nvarchar](4000) NULL,
	[State] [int] NULL,
	[CheckInTime] [time] NULL,
	[MidCheckOutTime] [time] NULL,
	[MidCheckInTime] [time] NULL,
	[CheckOutTime] [time] NULL);
GO

INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [MidCheckOutTime], [MidCheckInTime], [CheckOutTime])
	VALUES (CAST ('e8130e6b-10df-4451-98bc-15d237d1be6d' AS uniqueidentifier), NULL, NULL, 1, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'303218956', N'分样组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x070074053f47 AS time), CAST(0x0700c6c37c62 AS time), CAST(0x07001417c668 AS time), CAST(0x07009ab73488 AS time))

GO
INSERT INTO [dbo].[T_Biz_Department] ([ID], [CreateTime], [LastUpdateTime], [DisplayOrder], [OrgID], [Code], [Name], [Type], [Address], [Phone], [Fax], [PostCode], [Email], [PersonInCharge], [Remarks], [State], [CheckInTime], [MidCheckOutTime], [MidCheckInTime], [CheckOutTime])
	VALUES (CAST ('b9e06f82-6df6-4104-8aaa-857c63b27d04' AS uniqueidentifier), NULL, NULL, 4, CAST ('00000000-0000-0000-0000-000000000000' AS uniqueidentifier), N'1927528022', N'定性定量组', N'检测实验室', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, CAST(0x070074053f47 AS time), CAST(0x0700c6c37c62 AS time), CAST(0x07001417c668 AS time), CAST(0x07009ab73488 AS time))

GO
CREATE TABLE [dbo].[T_Biz_HolidaiesSchedule] (
	[ID] [int] NOT NULL IDENTITY (1, 1),
	[YearValue] [int] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_YearValue] DEFAULT ((0)),
	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_SetDate] DEFAULT (getdate()),
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_HolidaiesSchedule_CreatedDate] DEFAULT (getdate()));
GO

SET IDENTITY_INSERT [dbo].[T_Biz_HolidaiesSchedule] ON
GO
INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([ID], [YearValue], [SetDate], [CreatedDate])
	VALUES (25, 2014, CAST(0x0000a2a900000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))

GO
INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([ID], [YearValue], [SetDate], [CreatedDate])
	VALUES (26, 2014, CAST(0x0000a2aa00000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))

GO
INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([ID], [YearValue], [SetDate], [CreatedDate])
	VALUES (27, 2014, CAST(0x0000a2b000000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))

GO
INSERT INTO [dbo].[T_Biz_HolidaiesSchedule] ([ID], [YearValue], [SetDate], [CreatedDate])
	VALUES (28, 2014, CAST(0x0000a2b100000000 AS datetime), CAST(0x0000a260011ebf2d AS datetime))

GO
create FUNCTION  [dbo].[fn_returnRoundData]
(
	-- 跳过节假日函数
	@ParsDate datetime 
)
RETURNS datetime 
AS
BEGIN
	-- Declare the return variable here
	declare @holidyflag bit =1		  
		declare @tmpcount int =0	
			  declare @tmpbdate datetime =convert(datetime,convert(nvarchar(10),@ParsDate,120))  
			  
		while( @holidyflag =1  )
			begin 
			if exists( select * from T_Biz_HolidaiesSchedule where SetDate = @tmpbdate)
				begin
					set  @tmpcount = @tmpcount + 1
					set  @tmpbdate  = @tmpbdate +1				
				end
			else  
				set @holidyflag =0	 
			end  
		  return @ParsDate + @tmpcount

		  
END
GO
create FUNCTION [dbo].[Fn_GetDateByValidTime] (@ks VARCHAR(20),@Bdate datetime,@Times int)  --按科室开始工作日期加上分钟计算有效工作日期(带时分)
RETURNS datetime -- 工作时间上下班表(按科室)[RFIDDataBase].[dbo].[T_Biz_Department]  节假日表[RFIDDataBase]..[T_Biz_HolidaiesSchedule]
 AS              --@ks为科室,@Bdate为开始日期,@Times为加上的时间分钟
BEGIN 
  declare @retunrVal Datetime =@Bdate  
  if @Times is null 
	return @Bdate 
  declare @CheckTime int ,@CheckInTime time,@CheckOutTime time 
  select @CheckTime = isnull(DATEDIFF(MINUTE,  CheckInTime,CheckOutTime ),0),@CheckInTime = CheckInTime,@CheckOutTime = CheckOutTime
	from T_Biz_Department  where   Name=@ks
  if @CheckTime <=0  or  @CheckInTime  is null  or @CheckOutTime is null
	return   @Bdate
  if( convert(varchar(8),@Bdate,114) <= @CheckInTime)
	begin		 
	  set @Bdate = DATEADD(MINUTE,datediff(MINUTE,convert(varchar(8),@Bdate,114),@CheckInTime),@Bdate)
	end 
  else if ( convert(varchar(8),@Bdate,114) >=@CheckOutTime)
	set @Bdate =DATEADD(MINUTE, DATEDIFF(MINUTE,  convert(varchar(8),DATEADD(DAY,1,  @Bdate),114),@CheckInTime ),@Bdate+1)
  DECLARE @tmpBdate datetime
	select   @tmpBdate = dbo.fn_returnRoundData(@Bdate)
  IF(DATEDIFF(DAY,@tmpBdate ,@Bdate) <>0)
	BEGIN
	  set @tmpBdate= DATEADD(MINUTE,datediff(MINUTE,convert(varchar(8),@tmpBdate,114),@CheckInTime),@tmpBdate)          
    END
      SET @Bdate =  @tmpBdate
  declare @TimeInterval decimal(18,8)  --取得天数
	set @TimeInterval = CONVERT(decimal(18,2), @Times ) /CONVERT(decimal(18,2), @CheckTime ) 
  declare @flag bit = 0
  if(DATEDIFF(MINUTE, @CheckOutTime ,convert(varchar(8),@Bdate,114))< @Times)
	set @flag =1
  declare @Count  int =0,@tmpDate datetime 
	set @tmpDate =  @Bdate
	   while (@flag =1 and  @TimeInterval >0)
		  begin  
			declare @mindate datetime ,@Enddate  datetime 
			  select @mindate = min(SetDate)  from T_Biz_HolidaiesSchedule  where setdate> @tmpDate
            if(DATEDIFF(DAY,@Bdate, @mindate)< @TimeInterval + @Count )
			  begin 
			    set @Count =@Count +1
			    set @tmpDate = @mindate
			  end 
			else 
			  set @flag =0
		   end 		
  declare @lastDay datetime  		
	set @lastDay  = @Bdate + @Times/@CheckTime + @Count 
  if(@Times % @CheckTime >0 and datediff(MINUTE,convert(nvarchar(8), @lastDay ,114),@CheckOutTime) < @Times % @CheckTime)
	begin 	   
	  set @lastDay  =convert (datetime,   stuff( convert(nvarchar(11), @lastDay + 1,120)  , 11 ,0,'  ' + cast(@CheckInTime as nvarchar(10)))) +  cast( @Times % @CheckTime - (datediff(MINUTE,convert(nvarchar(8), @lastDay ,114),@CheckOutTime) ) as decimal(18,8)) /(24*60)
	end 
  else
    set @lastDay  =@lastDay + cast((@Times % @CheckTime) as decimal(18,8)) /(24*60)
	select   @retunrVal = dbo.fn_returnRoundData(@lastDay)
	return @retunrVal
END
GO
--SELECT [dbo].[Fn_GetDateByValidTime]('分样组','2010-1-1 19:09:37.437',60)