表与表特定条件下的关联SQL

USE [ChiefMes]
GO

/****** Object: StoredProcedure [dbo].[Kenta_RptEmpOEE] Script Date: 05/12/2015 13:52:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*************************************************
Creator: WuChun
Create Date: 2013-10-22
Remark: 计算健大员工OEE数据报表
*************************************************/
ALTER procedure [dbo].[Kenta_RptEmpOEE]
(
@GroupType varchar(30) = ‘Sum‘ ,--Detail: 人员OEE明细 Sum:人员OEE汇总
@EmpGroupNO varchar(30) = ‘‘, --人员组别
@DateBegin varchar(10) = ‘2013-04-26‘,
@DateEnd varchar(10) = ‘2015-05-08‘,
@EmpID varchar(10) = ‘‘,
@BC varchar(10) = ‘‘
)

as
declare @ProductName varchar(100), @MachineNo varchar(30), @BCCode varchar(10)
if object_id(‘tempdb..#DailyTableEmp‘) is not null drop table #DailyTableEmp
create table #DailyTableEmp
(
MachineNo varchar(30),--机器编号
EmpGroupNO varchar(50),
EmpID varchar(50),--员工工号
CurrDate varchar(10),--日期
BCCode varchar(10),--班次
DispatchNo varchar(30),--派工单号
TZ_CustomerNo varchar(30),--需求客户
MouldNo varchar(30),
ProductName varchar(2000),--产品名称
ProductNo varchar(30),--产品编号
TZ_MONO varchar(30),--制令单号
DispatchNum int,--派工数量
ProductNum int,--产品种数
StandSocketNum int,--标准出数
SocketNum int,--实际出数
SocketRate decimal(10,2),--出数率
SumNormalMouldNum int,--总的模次数
SumNormalCycle int,--人工周期总和
StandCycle decimal(10,2),--标准周期
AverageCycle decimal(10,2),--实际平均周期
CycleDiffRate decimal(10,2),--平均周期差异率(周期百分比=(AverageCycle-StandCycle)/StandCycle)
TZ_StandEmp decimal(10,2),--标准人数
ActualEmp decimal(10,2),--实际人数
----至此为派工单信息段
DispatchTime decimal(10,2),--派工单总时间
PlanProductTime decimal(10,2),--计划生产时间
RealMacTime decimal(10,2),--实际稼动时间
NoPlanTime decimal(10,2),--计划停机时间
HuanMo decimal(10,2),--换模时间
HuanLiao decimal(10,2),--换料时间
HuanDan decimal(10,2),--换单时间
JiQiGuZhang decimal(10,2),--机器故障时间
MoJuGuZhang decimal(10,2),--模具故障时间
FuSheGuZhang decimal(10,2),--辅设故障时间
DaiLiao decimal(10,2),--待料时间
WuDingDan decimal(10,2),--无订单时间
QiTa decimal(10,2),--在线保养时间
DaiRen decimal(10,2),--原材料不良时间
MacIdleTime decimal(10,2),--待机空闲时间
PermissionTime decimal(10,2) default(0.00),--批准时间
----至此为停机时间信息段
PlanOutputQty int,--计划产量=(总时间-计划停机时间)*3600/标准周期*标准出数
ActualOutputQty int,--实际产量
PackageNum int,--包装数
TheoreticalQty int,--理论实际生产时间产能=实际稼动时间*3600/标准周期*标准出数
ProductedNum int,--已生产数
BadNum int,--不良品数
BadRate decimal(10,4),--不良率
EfficiencyRate decimal(10,4),--机台效率(有效率)=生产总数/理论实际生产时间产能
Availability decimal(10,4),--有效开机率(表现性，有效性)=实际稼动时间/(总时间-计划停机时间-批准时间)
OEE decimal(10,4),--=机台效率*有效开机率*良率
Area varchar(1000),--负责区域
Flag int--层级标志，0派工单级别(有生产)，1派工单级别(无生产)，2机器编号级别，3人员汇总级别
)

if object_id(‘tempdb..#PlanTime‘) is not null drop table #PlanTime
create table #PlanTime
(
MachineNo varchar(30),
PlanProductTime decimal(10,2),
BCCode varchar(10)
)

insert into #DailyTableEmp(MachineNo,EmpGroupNO, EmpID, CurrDate,BCCode,DispatchNo,TZ_CustomerNo,MouldNo,ProductName,
ProductNo,TZ_MONO,DispatchNum,ProductNum,StandSocketNum,SocketNum,SocketRate,SumNormalMouldNum,SumNormalCycle,
StandCycle,AverageCycle,CycleDiffRate,TZ_StandEmp,ActualEmp,DispatchTime, PlanProductTime,RealMacTime,NoPlanTime,
HuanMo,HuanLiao,HuanDan,JiQiGuZhang,MoJuGuZhang,FuSheGuZhang,DaiLiao,WuDingDan,QiTa,DaiRen,MacIdleTime,PermissionTime,
PlanOutputQty,ActualOutputQty,PackageNum,TheoreticalQty,ProductedNum,BadNum,BadRate,EfficiencyRate,Availability,OEE,Flag)
select
p.MachineNo,e.EmpGroupNO, e.EmpID, p.CurrDate,p.BCCode,p.DispatchNo,p.TZ_CustomerNo,p.MouldNo,p.ProductName,p.ProductNo,
p.TZ_MONO,p.DispatchNum,p.ProductNum,p.StandSocketNum,p.SocketNum,p.SocketRate,p.SumNormalMouldNum,p.SumNormalCycle,
p.StandCycle,p.AverageCycle,p.CycleDiffRate,p.TZ_StandEmp,p.ActualEmp,p.DispatchTime, p.PlanProductTime,p.RealMacTime,
p.NoPlanTime,p.HuanMo,p.HuanLiao,p.HuanDan,p.JiQiGuZhang,p.MoJuGuZhang,p.FuSheGuZhang,p.DaiLiao,p.WuDingDan,p.QiTa,
p.DaiRen,p.MacIdleTime,p.PermissionTime,p.PlanOutputQty,p.ActualOutputQty,p.PackageNum,p.TheoreticalQty,
p.ProductedNum,p.BadNum,p.BadRate,p.EfficiencyRate,p.Availability,p.OEE,p.Flag
from Kenta_DailyProductionInfo p
join Kenta_EmpOEEConfig e

on

(@EmpGroupNO = ‘‘ or e.EmpGroupNO = @EmpGroupNO)
and charindex(p.MachineNo,e.MachineNo)>0
and p.BCCode=e.BCCode
and e.ShowOEE=1
and ((p.CurrDate between e.startDate and e.EndDate) or (p.CurrDate>e.startDate and e.EndDate is null))

where p.CurrDate between @DateBegin and @DateEnd
and DispatchNo<>‘‘
and (@EmpID = ‘‘ or e.EmpID = @EmpID or e.EmpName like @EmpID +‘%‘)
order by p.CurrDate, p.BCCode, p.MachineNo



update a set a.PlanProductTime=b.PlanProductTime
from #DailyTableEmp a
inner join #PlanTime b on a.MachineNo=b.MachineNo and a.BCCode=b.BCCode

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
--, Availability=case when DispatchTime-NoPlanTime=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime) end--有效开机率(表现性，有效性)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
where Flag=2

update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=2

--派工单层的OEE导入完成
--==================================================================================================================================

insert into #PlanTime
select distinct MachineNo, PlanProductTime, BCCode from #DailyTableEmp where CurrDate between @DateBegin and @DateEnd

insert into #DailyTableEmp(MachineNo,EmpGroupNO, EmpID, CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum, Flag)
select MachineNo, EmpGroupNO, EmpID, CurrDate, BCCode, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime/ProductNum), SUM(RealMacTime/ProductNum), SUM(NoPlanTime/ProductNum), SUM(HuanMo/ProductNum), SUM(HuanLiao/ProductNum), SUM(HuanDan/ProductNum), SUM(JiQiGuZhang/ProductNum), SUM(MoJuGuZhang/ProductNum), SUM(FuSheGuZhang/ProductNum), SUM(DaiLiao/ProductNum), SUM(WuDingDan/ProductNum), SUM(QiTa/ProductNum), SUM(DaiRen/ProductNum), SUM(MacIdleTime/ProductNum), SUM(PermissionTime/ProductNum), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),2
from #DailyTableEmp
where Flag in(0,1)
group by MachineNo,EmpGroupNO, EmpID, CurrDate, BCCode


update a set a.PlanProductTime=b.PlanProductTime from #DailyTableEmp a inner join #PlanTime b on a.MachineNo=b.MachineNo and a.BCCode=b.BCCode

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
--, Availability=case when DispatchTime-NoPlanTime=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime) end--有效开机率(表现性，有效性)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
where Flag=2
update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=2
--按机器编号层的OEE至此计算完成
--补充需求2013-05-27要求将所有派工单的产品名称用分号隔开对应到每台机器
--declare @rEmpGroupNO varchar(50), @rEmpNO varchar(50)
--declare cur_Daily cursor for
--select MachineNo, BCCode, ProductName, EmpGroupNO, EmpId from #DailyTableEmp where Flag in(0,1) and isnull(ProductName,‘‘)<>‘‘
--open cur_Daily
--fetch next from cur_Daily into @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--while @@FETCH_STATUS=0
--begin
-- print @MachineNo+ @BCCode+@ProductName
-- update #DailyTableEmp set ProductName=isnull(ProductName,‘‘)+@ProductName+‘; ‘
-- where Flag=2 and MachineNo=@MachineNo and BCCode=@BCCode and EmpGroupNO = @rEmpGroupNO and EmpID = @rEmpNO

-- fetch next from cur_Daily into @MachineNo, @BCCode, @ProductName, @rEmpGroupNO, @rEmpNO
--end
--close cur_Daily
--deallocate cur_Daily
--==================================================================================================================================

insert into #DailyTableEmp(EmpGroupNO,EmpID,CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, PlanProductTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum,Area, Flag)
select a.EmpGroupNO, a.EmpID, CurrDate, BCCode, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime), SUM(PlanProductTime), SUM(RealMacTime), SUM(NoPlanTime), SUM(HuanMo), SUM(HuanLiao), SUM(HuanDan), SUM(JiQiGuZhang), SUM(MoJuGuZhang), SUM(FuSheGuZhang), SUM(DaiLiao), SUM(WuDingDan), SUM(QiTa), SUM(DaiRen), SUM(MacIdleTime), SUM(PermissionTime), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),
‘‘, 3
from #DailyTableEmp a inner join MachineMstr b on a.MachineNo=b.Machine_Code
where Flag=2
group by a.EmpGroupNO, a.EmpID, CurrDate, BCCode


insert into #DailyTableEmp(EmpGroupNO,EmpID,CurrDate, BCCode, StandSocketNum, SocketNum, TZ_StandEmp, ActualEmp, DispatchTime, PlanProductTime, RealMacTime, NoPlanTime, HuanMo, HuanLiao, HuanDan, JiQiGuZhang, MoJuGuZhang, FuSheGuZhang, DaiLiao, WuDingDan, QiTa, DaiRen, MacIdleTime, PermissionTime, PlanOutputQty, ActualOutputQty, PackageNum, TheoreticalQty, ProductedNum, BadNum, Flag)
select ‘汇总‘,EmpID,‘‘, ‘‘, SUM(StandSocketNum), SUM(SocketNum), SUM(TZ_StandEmp), SUM(ActualEmp), SUM(DispatchTime), SUM(PlanProductTime), SUM(RealMacTime), SUM(NoPlanTime), SUM(HuanMo), SUM(HuanLiao), SUM(HuanDan), SUM(JiQiGuZhang), SUM(MoJuGuZhang), SUM(FuSheGuZhang), SUM(DaiLiao), SUM(WuDingDan), SUM(QiTa), SUM(DaiRen), SUM(MacIdleTime), SUM(PermissionTime), SUM(PlanOutputQty), SUM(ActualOutputQty), SUM(PackageNum), SUM(TheoreticalQty), SUM(ProductedNum), SUM(BadNum),3
from #DailyTableEmp a inner join MachineMstr b on a.MachineNo=b.Machine_Code
where Flag=2
group by EmpGroupNO, a.EmpID

update #DailyTableEmp set SocketRate=case when StandSocketNum=0 then 0 else SocketNum/(StandSocketNum*1.0) end--出数率
, BadRate=case when ProductedNum=0 then 0 else BadNum/(ProductedNum*1.0) end--不良率
, EfficiencyRate=case when TheoreticalQty=0 then 0 else ProductedNum/(TheoreticalQty*1.0) end--机台效率(有效率)
, Availability=case when DispatchTime-NoPlanTime-PermissionTime-DaiRen=0 then 0 else RealMacTime/(DispatchTime-NoPlanTime-PermissionTime-DaiRen) end--有效开机率(表现性，有效性)
--, Availability=case when PlanProductTime=0 then 0 else RealMacTime/(PlanProductTime-PermissionTime) end--有效开机率(表现性，有效性)
where Flag=3
update #DailyTableEmp set OEE=EfficiencyRate*Availability*(1-BadRate)--机台效率*有效开机率*良率
where Flag=3
--按人员层级的OEE至此计算完成

update #DailyTableEmp set StandSocketNum=null,SocketNum=null,SocketRate=null,TZ_StandEmp=null,ActualEmp=null,DispatchTime=null,
PlanProductTime=null,EfficiencyRate=null,Availability=null,BadNum=null,BadRate=null where EmpGroupNO=‘汇总‘
--==================================================================================================================================


if @GroupType=‘Detail‘
begin
select t.MachineNo,t.EmpID,CurrDate,case when t.BCCode=‘A‘ then ‘白班‘ when t.BCCode=‘B‘ then ‘晚班‘ else ‘‘ end as BCCode,DispatchNo,TZ_CustomerNo,ProductName,ProductNo,TZ_MONO,DispatchNum,StandSocketNum,SocketNum
,convert(varchar(10), CAST(SocketRate*100 as decimal(10,0)))+‘%‘ as SocketRate,StandCycle,AverageCycle
,convert(varchar(10), CAST(CycleDiffRate*100 as decimal(10,0)))+‘%‘ as CycleDiffRate,TZ_StandEmp,ActualEmp,DispatchTime, PlanProductTime,RealMacTime,NoPlanTime,HuanMo,HuanLiao,HuanDan,JiQiGuZhang,MoJuGuZhang,FuSheGuZhang,DaiLiao,WuDingDan,QiTa,DaiRen,MacIdleTime,PermissionTime,PlanOutputQty,ActualOutputQty,PackageNum,TheoreticalQty
, EfficiencyRate,Availability = isnull(Availability,0),BadNum,BadRate,OEE = ISNULL(OEE,0)
, EmpGroupName =(select top 1 EmpGroupName from MESAlarm.dbo.AlarmEmpGroup g where g.EmpGroupNo = t.EmpGroupNO)
, EmployeeName = e.EmpName
from #DailyTableEmp t
left join Kenta_EmpOEEConfig e on t.EmpID = e.EmpID
where t.Flag=2 and t.BCCode like ‘%‘+@BC+‘%‘
order by t.CurrDate, t.MachineNo, t.BCCode, t.DispatchNo
end
else if @GroupType=‘Sum‘
begin
select t.EmpGroupNO,case when t.EmpGroupNO=‘汇总‘ then ‘‘ else t.EmpID end EmpID,t.EmpID EmpID2, CurrDate, case when t.BCCode=‘A‘ then ‘白班‘ when t.BCCode=‘B‘ then ‘晚班‘ else ‘‘ end as BCCode,DispatchNo,TZ_CustomerNo,ProductName,ProductNo,TZ_MONO,DispatchNum,StandSocketNum,SocketNum
,convert(varchar(10), CAST(SocketRate*100 as decimal(10,0)))+‘%‘ as SocketRate,StandCycle,AverageCycle
,convert(varchar(10), CAST(CycleDiffRate*100 as decimal(10,0)))+‘%‘ as CycleDiffRate,TZ_StandEmp,ActualEmp,DispatchTime, PlanProductTime,RealMacTime,NoPlanTime,HuanMo,HuanLiao,HuanDan,JiQiGuZhang,MoJuGuZhang,FuSheGuZhang,DaiLiao,WuDingDan,QiTa,DaiRen,MacIdleTime,PermissionTime,PlanOutputQty,ActualOutputQty,PackageNum,TheoreticalQty
,EfficiencyRate,Availability,BadNum,case when t.EmpGroupNO=‘汇总‘ then ‘汇总‘ else convert(varchar(10),BadRate) end BadRate,OEE,
dbo.GetMachineArea(e.MachineNo) Area,case when t.EmpGroupNO=‘汇总‘ then ‘‘ else t.EmpGroupNo end EmpGroupName
, EmployeeName = case when t.EmpGroupNO=‘汇总‘ then ‘‘ else e.EmpName end
from #DailyTableEmp t
left join Kenta_EmpOEEConfig e on t.EmpID = e.EmpID
and ((t.CurrDate between e.startDate and e.EndDate) or (t.CurrDate>e.startDate and e.EndDate is null))
where t.Flag=3 and t.BCCode like ‘%‘+@BC+‘%‘
order by EmpID2, case when t.EmpGroupNO=‘汇总‘ then 2 else 1 end,t.CurrDate, t.MachineNo, BCCode, DispatchNo
end

drop table #DailyTableEmp

GO