,(----Wafer Out / Rework by ENTITYselect DATADATE  ,ENTITY  ,sum(QTY) QTY  ,sum(REWORK) REWORK  from PT_LOTMOVE_TBL group by DATADATE ,ENTITY   ) tbl4 where tbl1.DATADATE = tbl2.DATADATE   and tbl1.DATADATE = tbl4.DATADATE   and tbl1.ENTITY COLLATE Chinese_Taiwan_Stroke_CS_AI = tbl2.ENTITY   and tbl1.ENTITY COLLATE Chinese_Taiwan_Stroke_CS_AI = tbl3.ENTITY   and tbl1.ENTITY COLLATE Chinese_Taiwan_Stroke_CS_AI = tbl4.ENTITY   ) x   left join(----Wafer Scrap by ENTITYselect convert(varchar(10),convert(date,a.DATADATE),120) DATADATE  ,b.ENTITY  ,sum(a.WAFER_CNT) as WAFER_CNT  from SCRAP_WAFER_RD a   left join (SELECT distinct ENTITY,EQPID FROM [OEE].[dbo].[LONGDOWN_EQPTYPE_LIST] where TYPE_NAME = 'MAIN TOOL') b

  ,(----Cycle Time Base Line by ENTITYselect ENTITY  ,STDCT  from (SELECT [ENTITY]  ,STDCT  ,ROW_NUMBER() over( partition by ENTITY order by DATAUPDATE desc ) SORT  FROM [OEE].[dbo].PT_PC_ENTITY_TGT  WHERE ENTITY is not null   AND STDCT is not null ) tbl where SORT = 1) tbl3

  ,(SELECT distinct ENTITY,EQPID  FROM [OEE].[dbo].[LONGDOWN_EQPTYPE_LIST] where TYPE_NAME = 'CHAMBER' ) b where a.EQPID COLLATE Chinese_Taiwan_Stroke_CS_AI = b.EQPID group by b.ENTITY ,convert(varchar(10),convert(datetime,a.DATADATE)-1,120)) tbl1  ,(----Cycle Time by ENTITYSELECT [DATADATE]  ,[ENTITY]  ,AVG([M_CT]) [M_CT]  FROM [OEE].[dbo].[PT_CYCLE_TIME_RD] GROUP BY [DATADATE] ,[ENTITY]) tbl2

  ,case when (24*100*100-24*UP*(100-UT)-24*SD*100) = 0 then 0when (24*100*100-24*UP*(100-UT)-24*SD*100-24*UD*100)=(24*100*100-24*UP*(100-UT)-24*SD*100) then 1else ((24*100*100-24*UP*(100-UT)-24*SD*100-24*UD*100)/(24*100*100-24*UP*(100-UT)-24*SD*100)) end U  ,case when STDCT         else 1 end E  from (----Uptime by ENTITYselect b.ENTITY  ,convert(varchar(10),convert(datetime,a.DATADATE)-1,120) DATADATE  ,avg(a.UT) UT  ,avg(a.UP) UP  ,avg(a.SD) SD  ,avg(a.UD) UD  from (select convert(varchar(10),DATADATE) DATADATE  ,EQPID  ,CASE WHEN (PD+SB)=0 THEN 100 ELSE ROUND(PD/(PD+SB)*100,2) END AS UT  ,UP  ,SD  ,UD  from UPTIME_EQPUTILMOVE where EQPTYPE = 'CHAMBER') a

 on x.DATADATE COLLATE Chinese_Taiwan_Stroke_CS_AI = y.DATADATEand x.EQPID   COLLATE Chinese_Taiwan_Stroke_CS_AI = y.EQPID  where x.DATADATE = convert(varchar(10),getdate()-1,120)  order by 1,2  ----OEE By Entity----delete from dbo.OEE_BY_ENTITY_W where DATADATE = convert(varchar(10),getdate()-1,120)insert into OEE_BY_ENTITY_Wselect x.DATADATE  ,x.ENTITY  ,x.UT  ,x.UP  ,x.SD  ,x.UD  ,x.STDCT  ,x.M_CT  ,x.QTY  ,x.REWORK  ,ISNULL(y.WAFER_CNT,0) SCRAP  ,x.U  ,x.E ,case when QTY > 0 then (1-(REWORK+ISNULL(y.WAFER_CNT,0))/QTY)else 1 end P ,case when QTY > 0 then x.U*x.E*(1-(REWORK+ISNULL(y.WAFER_CNT,0))/QTY)else x.U*x.E*1 end OEE  from (select tbl1.DATADATE  ,tbl1.ENTITY  ,tbl1.UT  ,tbl1.UP  ,tbl1.SD  ,tbl1.UD  ,tbl3.STDCT  ,tbl2.M_CT  ,tbl4.QTY  ,tbl4.REWORK

&nbsp;where&nbsp;tbl1.DATADATE&nbsp;=&nbsp;tbl2.DATADATE&nbsp;&nbsp;&nbsp;and&nbsp;tbl1.DATADATE&nbsp;=&nbsp;tbl4.DATADATE&nbsp;&nbsp;&nbsp;and&nbsp;tbl1.EQPID&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;=&nbsp;tbl2.EQPID&nbsp;&nbsp;&nbsp;and&nbsp;tbl1.EQPID&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;=&nbsp;tbl3.EQPID&nbsp;&nbsp;&nbsp;and&nbsp;tbl1.EQPID&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;=&nbsp;tbl4.EQPID&nbsp;&nbsp;&nbsp;)&nbsp;x&nbsp;&nbsp;&nbsp;left&nbsp;join(----Wafer&nbsp;Scrap&nbsp;by&nbsp;EQPIDselect&nbsp;convert(varchar(10),convert(date,a.DATADATE),120)&nbsp;DATADATE&nbsp;&nbsp;,b.EQPID&nbsp;&nbsp;,sum(a.WAFER_CNT)&nbsp;as&nbsp;WAFER_CNT&nbsp;&nbsp;from&nbsp;SCRAP_WAFER_RD&nbsp;a&nbsp;&nbsp;&nbsp;left&nbsp;join&nbsp;(SELECT&nbsp;distinct&nbsp;EQPID&nbsp;FROM&nbsp;[OEE].[dbo].[LONGDOWN_EQPTYPE_LIST]&nbsp;where&nbsp;TYPE_NAME&nbsp;=&nbsp;'MAIN&nbsp;TOOL')&nbsp;bon&nbsp;(a.EQP_OR_CHAMBER&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;like&nbsp;b.EQPID+'%'&nbsp;orb.EQPID&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;like&nbsp;a.EQP_OR_CHAMBER+'%')&nbsp;where&nbsp;ISNULL(EQP_OR_CHAMBER,'')&nbsp;<>&nbsp;''&nbsp;&nbsp;&nbsp;and&nbsp;EQPID&nbsp;is&nbsp;not&nbsp;null&nbsp;group&nbsp;by&nbsp;convert(varchar(10),convert(date,a.DATADATE),120)&nbsp;,b.EQPID&nbsp;&nbsp;&nbsp;)y

&nbsp;&nbsp;,STDCT&nbsp;&nbsp;,ROW_NUMBER()&nbsp;over(&nbsp;partition&nbsp;by&nbsp;ENTITY&nbsp;order&nbsp;by&nbsp;DATAUPDATE&nbsp;desc&nbsp;)&nbsp;SORT&nbsp;FROM&nbsp;[OEE].[dbo].PT_PC_ENTITY_TGT&nbsp;&nbsp;WHERE&nbsp;ENTITY&nbsp;is&nbsp;not&nbsp;null&nbsp;&nbsp;&nbsp;AND&nbsp;STDCT&nbsp;is&nbsp;not&nbsp;null&nbsp;)&nbsp;tbl&nbsp;where&nbsp;SORT&nbsp;=&nbsp;1)&nbsp;a&nbsp;&nbsp;,(SELECT&nbsp;distinct&nbsp;ENTITY,EQPID&nbsp;&nbsp;FROM&nbsp;[OEE].[dbo].[LONGDOWN_EQPTYPE_LIST]&nbsp;&nbsp;WHERE&nbsp;TYPE_NAME&nbsp;=&nbsp;'MAIN&nbsp;TOOL'&nbsp;)&nbsp;b&nbsp;where&nbsp;a.ENTITY&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;=&nbsp;b.ENTITY)&nbsp;tbl3&nbsp;&nbsp;,(----Wafer&nbsp;Out&nbsp;/&nbsp;Rework&nbsp;by&nbsp;EQPIDselect&nbsp;DATADATE&nbsp;&nbsp;,EQPID&nbsp;&nbsp;,sum(QTY)&nbsp;QTY&nbsp;&nbsp;,sum(REWORK)&nbsp;REWORK&nbsp;&nbsp;from&nbsp;PT_LOTMOVE_TBL&nbsp;group&nbsp;by&nbsp;DATADATE&nbsp;,EQPID&nbsp;&nbsp;&nbsp;)&nbsp;tbl4

&nbsp;&nbsp;from&nbsp;(select&nbsp;convert(varchar(10),DATADATE)&nbsp;DATADATE&nbsp;&nbsp;,EQPID&nbsp;&nbsp;,CASE&nbsp;WHEN&nbsp;(PD+SB)=0&nbsp;THEN&nbsp;100&nbsp;ELSE&nbsp;ROUND(PD/(PD+SB)*100,2)&nbsp;END&nbsp;AS&nbsp;UT&nbsp;&nbsp;,UP&nbsp;&nbsp;,SD&nbsp;&nbsp;,UD&nbsp;&nbsp;from&nbsp;UPTIME_EQPUTILMOVE&nbsp;where&nbsp;EQPTYPE&nbsp;=&nbsp;'CHAMBER'&nbsp;)&nbsp;a&nbsp;&nbsp;,(SELECT&nbsp;distinct&nbsp;ENTITY,EQPID&nbsp;&nbsp;FROM&nbsp;[OEE].[dbo].[LONGDOWN_EQPTYPE_LIST]&nbsp;where&nbsp;TYPE_NAME&nbsp;=&nbsp;'CHAMBER'&nbsp;)&nbsp;b&nbsp;where&nbsp;a.EQPID&nbsp;COLLATE&nbsp;Chinese_Taiwan_Stroke_CS_AI&nbsp;=&nbsp;b.EQPID&nbsp;group&nbsp;by&nbsp;substring(b.EQPID,1,len(b.EQPID)-1)&nbsp;,convert(varchar(10),convert(datetime,a.DATADATE)-1,120))&nbsp;tbl1&nbsp;&nbsp;,(----Cycle&nbsp;Time&nbsp;by&nbsp;EQPIDSELECT&nbsp;[DATADATE]&nbsp;&nbsp;,[EQPID]&nbsp;&nbsp;,AVG([M_CT])&nbsp;[M_CT]&nbsp;&nbsp;FROM&nbsp;[OEE].[dbo].[PT_CYCLE_TIME_RD]&nbsp;GROUP&nbsp;BY&nbsp;[DATADATE]&nbsp;,[EQPID])&nbsp;tbl2&nbsp;&nbsp;,(----Cycle&nbsp;Time&nbsp;Base&nbsp;Line&nbsp;by&nbsp;EQPIDselect&nbsp;b.EQPID&nbsp;&nbsp;,a.STDCT&nbsp;&nbsp;from&nbsp;(select&nbsp;ENTITY&nbsp;&nbsp;,STDCT&nbsp;&nbsp;from&nbsp;(SELECT&nbsp;[ENTITY]

&nbsp;&nbsp;from&nbsp;(select&nbsp;tbl1.DATADATE&nbsp;&nbsp;,tbl1.EQPID&nbsp;&nbsp;,tbl1.UT&nbsp;&nbsp;,tbl1.UP&nbsp;&nbsp;,tbl1.SD&nbsp;&nbsp;,tbl1.UD&nbsp;&nbsp;,tbl3.STDCT&nbsp;&nbsp;,tbl2.M_CT&nbsp;&nbsp;,tbl4.QTY&nbsp;&nbsp;,tbl4.REWORK&nbsp;&nbsp;,case&nbsp;when&nbsp;(24*100*100-24*UP*(100-UT)-24*SD*100)&nbsp;=&nbsp;0&nbsp;then&nbsp;0when&nbsp;(24*100*100-24*UP*(100-UT)-24*SD*100-24*UD*100)=(24*100*100-24*UP*(100-UT)-24*SD*100)&nbsp;then&nbsp;1else&nbsp;((24*100*100-24*UP*(100-UT)-24*SD*100-24*UD*100)/(24*100*100-24*UP*(100-UT)-24*SD*100))&nbsp;end&nbsp;U&nbsp;&nbsp;,case&nbsp;when&nbsp;STDCT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;1&nbsp;end&nbsp;E&nbsp;&nbsp;from&nbsp;(----Uptime&nbsp;by&nbsp;EQPIDselect&nbsp;substring(b.EQPID,1,len(b.EQPID)-1)&nbsp;EQPID&nbsp;&nbsp;,convert(varchar(10),convert(datetime,a.DATADATE)-1,120)&nbsp;DATADATE&nbsp;&nbsp;,avg(a.UT)&nbsp;UT&nbsp;&nbsp;,avg(a.UP)&nbsp;UP&nbsp;&nbsp;,avg(a.SD)&nbsp;SD&nbsp;&nbsp;,avg(a.UD)&nbsp;UD

USE&nbsp;[OEE]GO/******&nbsp;Object:&nbsp;&nbsp;StoredProcedure&nbsp;[dbo].[sp_OEE_Calculate_W]&nbsp;&nbsp;&nbsp;&nbsp;Script&nbsp;Date:&nbsp;10/15/2022&nbsp;7:10:44&nbsp;PM&nbsp;******/SET&nbsp;ANSI_NULLS&nbsp;ONGOSET&nbsp;QUOTED_IDENTIFIER&nbsp;ONGO--&nbsp;=============================================--&nbsp;Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:ESI/QXC--&nbsp;Create&nbsp;date:&nbsp;2021-01-08--&nbsp;Description:sp_OEE_Calculate&nbsp;base&nbsp;on&nbsp;wafer&nbsp;level&nbsp;raw&nbsp;data.--&nbsp;=============================================ALTER&nbsp;PROCEDURE&nbsp;[dbo].[sp_OEE_Calculate_W]asBEGIN----OEE&nbsp;By&nbsp;Eqpid----delete&nbsp;from&nbsp;dbo.OEE_BY_EQPID_W&nbsp;where&nbsp;DATADATE&nbsp;=&nbsp;convert(varchar(10),getdate()-1,120)insert&nbsp;into&nbsp;OEE_BY_EQPID_Wselect&nbsp;x.DATADATE&nbsp;&nbsp;,x.EQPID&nbsp;&nbsp;,x.UT&nbsp;&nbsp;,x.UP&nbsp;&nbsp;,x.SD&nbsp;&nbsp;,x.UD&nbsp;&nbsp;,x.STDCT&nbsp;&nbsp;,x.M_CT&nbsp;&nbsp;,x.QTY&nbsp;&nbsp;,x.REWORK&nbsp;&nbsp;,ISNULL(y.WAFER_CNT,0)&nbsp;SCRAP&nbsp;&nbsp;,x.U&nbsp;&nbsp;,x.E ,case when QTY >&nbsp;0&nbsp;then&nbsp;(1-(REWORK+ISNULL(y.WAFER_CNT,0))/QTY)else&nbsp;1&nbsp;end&nbsp;P ,case when QTY >&nbsp;0&nbsp;then&nbsp;x.U*x.E*(1-(REWORK+ISNULL(y.WAFER_CNT,0))/QTY)else&nbsp;x.U*x.E*1&nbsp;end&nbsp;OEE

OEE_BY_ENTITY"UPTIME_EQPUTILMOVEPT_CYCLE_TIME_RDPT_PC_ENTITY_TGTPT_LOTMOVE_TBLSCRAP_WAFER_RDsp_OEE_RAW_DATA本数据库UPTIME_EQPUTILMOVEOEE_RAW_DATA计算OEE原始数据sp_PT_CYCLE_TIME_RDF12X_SDBPT_LOTMOVEPT_ENTITY_CYCLE_TIME_RD获取生产周期数据PT_CYCLE_TIME_RDsp_PT_LOTMOVE_RDF12X_SDBPT_LOTMOVEPT_LOTMOVE_RD获取工序移动数据PT_LOTMOVE_RDPT_LOTMOVE_TBLsp_PT_PC_ENTITY_TGTF12X_SDBPT_PC_ENTITY_TGTPT_PC_ENTITY_TGT获取实体目标值sp_SCRAP_WAFER_RDF12X_SDBnotes_lotlossSCRAP_WAFER_RD获取报废数据sp_UD_WAIT_TIME_RDF12X_UEDADF_eqphistUD_WAIT_TIME_RD获取非计划停机时间sp_UPTIME_EQPTARGETF12XESIDB01UPTIME_EQP_TARGETUPTIME_EQP_TARGET获取时间开动率目标值sp_UPTIME_EQPUTILMOVEF12XESIDB01UPTIME_EQPUTILMOVEUPTIME_EQPUTILMOVEsp_OEE_Calculate_W本数据库UPTIME_EQPUTILMOVE"OEE_BY_EQPID_WOEE_BY_ENTITY_W"LONGDOWN_EQPTYPE_LISTPT_CYCLE_TIME_RDPT_PC_ENTITY_TGTLONGDOWN_EQPTYPE_LISTPT_LOTMOVE_TBLSCRAP_WAFER_RD