USE [EASYCON_LJ]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_pk_eff1]    Script Date: 2019-04-03 10:15:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fn_pk_eff1](
	@pkworkid int

)
returns float
as 
	begin
	declare @eff float
	set @eff=0
	declare cur_eff cursor for select case when sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime]))<>0 then  cast(sum([dbo].[ElecProWorkNum].[RecordNum]*[dbo].[ProProcess].[UnitHour]) as float)/cast(sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime])) as float) else NULL end 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].[ProWorkId]=@pkworkid 
	open cur_eff
	fetch next from cur_eff into @eff
	close cur_eff
	deallocate cur_eff
	return @eff

	end
GO


