USE [CoatERdb]
GO
/****** Object: UserDefinedFunction [dbo].[V_ReProducing] Script Date: 01/28/2010 09:45:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER function [dbo].[V_ReProducing](
@_begin datetime,
@_end datetime
)
returns table
as
Return (
Select NO,Op,Class,
sum(_IN) as _SumIN,
sum(_Out) as _SumOut,
sum(_RePro) as _SumRP,
cast((sum(_IN)+sum(cast(_RePro as int))-sum(_Out))/cast((sum(_IN)+sum(cast(_RePro as int))) as float )*100 as numeric(18,1)) as _RP
from
((Select t_machno as NO,t_operator as Op,t_opclass as Class,
sum(isnull(t_innum,0)-isnull(t_TInNum,0)-isnull(t_OverTol,0) ) as _IN,
sum(isnull(t_outnum,0)+isnull(t_POkNum,0)) as _Out, 0 as _RePro
from er_tb_rpt
where (InType<30) and (OrderType=0) and t_begintime>=@_begin and t_begintime<=@_end
group by t_machno,t_operator,t_opclass )
Union all
(Select t_machno as NO,t_operator as Op,t_opclass as Class,
0 as _IN,
0 as _Out,
sum(cast(isnull(b_Remarknum,0) as int)) as _RePro
from er_tb_rpt
where (InType<30) and (OrderType=0) and t_begintime>=@_begin and t_begintime<=@_end
and ((CHARINDEX('導線發黑', bSurRemark) <= 0) or (CHARINDEX('導線沾機油', bSurRemark) <= 0))
group by t_machno,t_operator,t_opclass )) A
group by NO,Op,Class
having (sum(_IN)+sum(_RePro))<>0
)
/*
Select no,Op,class,_SumIN,_SumOut,_SumRP,_RP,
RP=case (case when _RP<0 then 0.0 else _RP end)
when '0.0' then '0'
when '100.0' then '100%'
else cast(_RP as varchar(50))+'%' end
from dbo.V_ReProducing('2009/3/4 7:59:59','2009/3/5 7:59:59')
where no='1#'
Select
RP=case (sum(case when _RP<0 then 0.0 else _RP end)/count(_RP))
when '0.0' then '0'
when '100.0' then '100%'
else cast(_RP as varchar(50))+'%' end
from dbo.V_ReProducing('2009/3/4 7:59:59','2009/3/5 7:59:59') where no='1#'
group by _RP
*/