OEE之性能指数计算

 1 --当一个班次时间内同时加工多种产品时，生产节拍=（零件1节拍*班次内零件1设备实际产量+零件2节拍*班次内零件2设备实际产量+...+）/（班次内零件1设备实际产量+班次内零件2设备实际产量+...+）
 2 with
 3 --1、依据公式计算各个零件实际产量及（零件节拍*班次内零件设备实际产量）值
 4 beatInternal as (
 5                 select 
 6                                 c.machineid , 
 7                                 c.MachineCode,
 8                                 c.ShiftDay, 
 9                                 c.MachinesShiftDetailId,
10                                 c.Yield as RealYield,
11                                 c.Yield * mb.Beat as  TotalTime
12                 from (
13                         select            --班次内各产品产量计算
14                                         c.machineid , 
15                                         c.MachineCode,
16                                         c.ShiftDetail_ShiftDay as ShiftDay, 
17                                         c.MachinesShiftDetailId,
18                                         c.ProductId,
19                                         sum(c.Yield) as Yield 
20                         from Capacities as c 
21                         group by c.MachineId,c.MachineCode,c.ShiftDetail_ShiftDay,c.MachinesShiftDetailId,c.ProductId
22                 ) as c    
23                     join Products as p on c.ProductId = p.Id
24                     join MachineBeats as mb on (p.Code = mb.ProductNumber and mb.MachineId = c.MachineId)
25 ),
26 --2、依据公式计算班次内 各设备生产节拍
27 beat as(
28                 select 
29                                                         b.machineid , 
30                                                         b.MachineCode,
31                                                         b.ShiftDay, 
32                                                         b.MachinesShiftDetailId,
33                                                         sum(b.TotalTime ) / sum(b.RealYield) as mbeat  --设备生产节拍
34                 from beatInternal as b
35                 group by b.MachineId,b.MachineCode,b.ShiftDay,b.MachinesShiftDetailId
36 ),
37 --3、计算班次内设备运行时间
38 realTime AS (
39                                 select machineid, 
40                                     MachineCode,
41                                     ShiftDetail_ShiftDay as ShiftDay,
42                                     MachinesShiftDetailId,  
43                                     sum(case code when ‘Run‘ then Duration else 0 end) as Duration 
44                                 from states 
45                                 where  machineid in (1)
46                                     and ShiftDetail_ShiftDay between  ‘‘ and ‘‘
47                                 group by machineid ,MachineCode,ShiftDetail_ShiftDay,MachinesShiftDetailId
48             ),
49 --4、计算班次内设备实际产量
50 realCapacity as (
51                         select 
52                                         c.machineid , 
53                                         c.MachineCode,
54                                         c.ShiftDetail_ShiftDay as ShiftDay, 
55                                         c.MachinesShiftDetailId,
56                                         sum(c.Yield) as Yield 
57                         from Capacities as c 
58                         group by c.MachineId,c.MachineCode,c.ShiftDetail_ShiftDay,c.MachinesShiftDetailId
59 ),
60 --5、依据公式计算班次内设备计划产量
61 planCapacity as(
62                         select 
63                             b.machineid, 
64                             b.MachineCode,
65                             b.ShiftDay,
66                             b.MachinesShiftDetailId,  
67                             r.Duration / b.mbeat as planCount  
68                         from beat as b
69                             join realTime as r on (b.MachineId= r.MachineId and b.ShiftDay = r.ShiftDay and b.MachinesShiftDetailId = r.MachinesShiftDetailId)
70 )
71 --最后计算班次内各设备性能运转率   =实际产量/计划产量
72 select    
73     rc.machineid, 
74     rc.MachineCode,
75     rc.ShiftDay,
76     rc.MachinesShiftDetailId,  
77     rc.Yield / pc.planCount as PerformanceRate  
78 from realCapacity as rc
79     join planCapacity as pc on (rc.MachineId = pc.MachineId and rc.ShiftDay = pc.ShiftDay and rc.MachinesShiftDetailId = pc.MachinesShiftDetailId)