

/****** Object:  UserDefinedFunction [dbo].[fn_pk_eff4]    Script Date: 2019-06-21 15:15:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





/*²úÏßÐ§ÂÊ*/
CREATE function [dbo].[fn_pk_eff4](
	@depid int,
	@centerid int,
	@starttime datetime,
	@endtime datetime

)
returns float
as 
	begin
	declare @eff float
	set @eff=0
	--declare cur_eff cursor for select cast(sum([dbo].[ElecProWorkNum].[RecordNum]*[dbo].[ProProcess].[UnitHour]) as float)/cast(sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime])) as float) from [dbo].[ProduceFormM] INNER JOIN  [dbo].[ProcessWork]  ON [dbo].[ProduceFormM].[PFormMId] = [dbo].[ProcessWork].[PFormMId] INNER JOIN [dbo].[ElecProWorkNum] ON [dbo].[ProcessWork].[ProWorkId]=[dbo].[ElecProWorkNum].[ProWorkId] INNER JOIN [dbo].[ProProcess] ON [dbo].[ProProcess].[PFormMId]=[dbo].[ProcessWork].[PFormMId] AND [dbo].[ProcessWork].[ProProcessId]=[dbo].[ProProcess].[ProProcessId] where [dbo].[ProduceFormM].[PStartTime]>=@starttime and [dbo].[ProduceFormM].[PEndTime]<=@endtime and [dbo].[ProduceFormM].[DepName]=@depname;
		declare cur_eff cursor for select cast(sum([dbo].[ElecProWorkNum].[RecordNum]*[dbo].[ProProcess].[UnitHour]) as float)/cast(sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime])) as float) from   [dbo].[ProcessWork]  INNER JOIN [dbo].[ElecProWorkNum] ON [dbo].[ProcessWork].[ProWorkId]=[dbo].[ElecProWorkNum].[ProWorkId] INNER JOIN [dbo].[ProProcess] ON [dbo].[ProProcess].[PFormMId]=[dbo].[ProcessWork].[PFormMId] AND [dbo].[ProcessWork].[ProProcessId]=[dbo].[ProProcess].[ProProcessId] where [dbo].[ProcessWork].[PStartTime]>=@starttime and [dbo].[ProcessWork].[PEndTime]<=@endtime and [dbo].[ProcessWork].[DepId]=@depid and [dbo].[ProcessWork].[CenterId]=@centerid;
	open cur_eff
	fetch next from cur_eff into @eff
	close cur_eff
	deallocate cur_eff
	return @eff

	end
GO


