

/****** Object:  UserDefinedFunction [dbo].[DuracionOT]    Script Date: 07/31/2015 09:01:08 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DuracionOT]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[DuracionOT]
GO
CREATE function [dbo].[DuracionOT](@p_id int) returns float
as 
begin
declare @duracionTotalOt as float
declare @table table(reporte int ,duracion float ,ot int)
insert into @table (reporte  ,duracion ,ot )
select id_reporteTecnico, round(sum(duracion),2)/ COUNT(distinct id_usuario )   ,[id_documento.ot] from ort_programacion 
where [id_documento.ot]=@p_id and id_tipoprogramacion=6 and active=1     
group by id_reporteTecnico ,[id_documento.ot]
set @duracionTotalOt =(select SUM(duracion) from @table group by ot)
return @duracionTotalOt
end


