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)

--性能指標
with 
realCapacity as(
                select 
                    shifts.DeviceGroupId,
                    sc.ShiftDay, 
                    sc.MachineShiftDetailId ,
                    shifts.planId,
                    shifts.ProductId,
                    SUM(ISNULL(mmc.[Count],0)) as realedcapacity
                from   (
                         select 
                            p.DeviceGroupId, 
                            s.ShiftDay , 
                            s.MachineShiftDetailId as MachinesShiftDetailId,
                            p.id as planId,
                            p.ProductId
                         from ProcessPlans as p 
                                                join MachineDeviceGroups as mdg on p.DeviceGroupId = mdg.DeviceGroupId 
                                                join (
                                                    select msd.ShiftDay,ssi.Name as ShiftName, msd.id as MachineShiftDetailId,msd.MachineId  from 
                                                    MachinesShiftDetails as msd 
                                                    join ShiftSolutionItems as ssi on msd.ShiftSolutionItemId = ssi.Id
                                                ) s on (mdg.MachineId = s.MachineId and p.ShiftDay = s.ShiftDay and p.ShiftName = s.ShiftName)
                                                where mdg.MachineId in (2541) and mdg.DeviceGroupId in (180) and p.ShiftDay between '2021-01-22' AND '2021-01-22'  
                                                group by p.DeviceGroupId,s.ShiftDay,s.MachineShiftDetailId,p.id,p.ProductId
                ) as shifts 
                join ShiftCalendars as sc on sc.MachineShiftDetailId = shifts.MachinesShiftDetailId 
                left join MarkingMachineCapacities as mmc
                on (mmc.ProcessPlanId = shifts.planId and  
                    mmc.CreationTime BETWEEN sc.StartTime AND sc.EndTime
                )
                group by shifts.DeviceGroupId, sc.ShiftDay, sc.MachineShiftDetailId ,shifts.planId, shifts.ProductId
),
beatInternal as (
            select 
                    p.DeviceGroupId, 
                    s.ShiftDay , 
                    s.MachineShiftDetailId as MachinesShiftDetailId,
                    p.id as planId,
                    p.ProductId,
                    p.PlanAmount,
                    b.DeviceGroupBeat * p.PlanAmount as TotalTime
                 from ProcessPlans as p 
                                        join MachineDeviceGroups as mdg on p.DeviceGroupId = mdg.DeviceGroupId 
                                        join (
                                            select msd.ShiftDay,ssi.Name as ShiftName, msd.id as MachineShiftDetailId,msd.MachineId  from 
                                            MachinesShiftDetails as msd 
                                            join ShiftSolutionItems as ssi on msd.ShiftSolutionItemId = ssi.Id
                                        ) s on (mdg.MachineId = s.MachineId and p.ShiftDay = s.ShiftDay and p.ShiftName = s.ShiftName)
                                        join Products as pr on p.ProductId = pr.id
                                        join beats as b on (b.DeviceGroupId = p.DeviceGroupId and b.ProductNumber = pr.Code)
                                        where mdg.MachineId in (2541) and mdg.DeviceGroupId in (180) and p.ShiftDay between '2021-01-22' AND '2021-01-22'  
                                        group by p.DeviceGroupId,s.ShiftDay,s.MachineShiftDetailId,p.id,p.ProductId ,p.PlanAmount,b.DeviceGroupBeat
),
--產線同時加工多種產品時，生產節拍=（零件1節拍*班次內零件1計劃產量+零件2節拍*班次內零件2計劃產量。。。）/（班次內零件1計劃產量+班次內零件2計劃產量。。。）
beat as(
                select 
                                                        bi.DeviceGroupId, 
                                                        bi.ShiftDay, 
                                                        bi.MachinesShiftDetailId,
                                                        iif(sum(bi.PlanAmount) = 0, 0,sum(bi.TotalTime ) / sum(bi.PlanAmount))  as dBeat  --產線生產節拍
                from beatInternal as bi
                group by bi.DeviceGroupId,bi.ShiftDay,bi.MachinesShiftDetailId
),
planedCapcity as (
                select 
                    bi.DeviceGroupId,
                    bi.ShiftDay,
                    bi.MachinesShiftDetailId, 
                    iif(bi.dBeat = 0, 0,DATEDIFF(SECOND,sc.StartTime,sc.EndTime) / bi.dBeat) as planedCapacity
                from beat as bi 
                    join ShiftCalendars as sc on bi.MachinesShiftDetailId = sc.MachineShiftDetailId
)
select 
    rc.DeviceGroupId as DimensionsId,
    dg.DisplayName as DimensionsName,
CONVERT(varchar(100), sc.MachineShiftDetailName, 23) as ShiftDay,sc.ShiftItemName as ShiftName, 
    rc.MachineShiftDetailId as MachinesShiftDetailId, 
    sum(rc.realedcapacity) as RealCapacity,
    sum(pc.planedCapacity) as PlanCapacity,
    sum(rc.realedcapacity) * 1.0 / sum( pc.planedCapacity) as Rate
from (
    select DeviceGroupId,ShiftDay,MachineShiftDetailId, sum(realedcapacity) as realedcapacity from realCapacity group by DeviceGroupId,ShiftDay,MachineShiftDetailId
) as rc
join ShiftCalendarsView as sc on rc.MachineShiftDetailId = sc.MachineShiftDetailId
join DeviceGroups as dg on rc.DeviceGroupId = dg.Id
join planedCapcity as pc on (rc.DeviceGroupId  =pc.DeviceGroupId and rc.ShiftDay = pc.ShiftDay and rc.MachineShiftDetailId  = pc.MachinesShiftDetailId)
group by rc.DeviceGroupId,dg.DisplayName,rc.ShiftDay,rc.MachineShiftDetailId ,sc.MachineShiftDetailName,sc.ShiftItemName

go

----質量指數 
with 
dgPlan as (
                        select 
                            p.DeviceGroupId, 
                            s.ShiftDay , 
                            max(s.MachineShiftDetailId) as MachinesShiftDetailId,   --取產線對應的首個設備班次 
                            p.id as planId
                         from ProcessPlans as p 
                                                join MachineDeviceGroups as mdg on p.DeviceGroupId = mdg.DeviceGroupId 
                                                join (
                                                    select msd.ShiftDay,ssi.Name as ShiftName, msd.id as MachineShiftDetailId,msd.MachineId  from 
                                                    MachinesShiftDetails as msd 
                                                    join ShiftSolutionItems as ssi on msd.ShiftSolutionItemId = ssi.Id
                                                ) s on (mdg.MachineId = s.MachineId and p.ShiftDay = s.ShiftDay and p.ShiftName = s.ShiftName)
                                                where p.DeviceGroupId in(180) and p.ShiftDay between  '2021-01-22' AND '2021-01-22'  
                                                group by p.DeviceGroupId,s.ShiftDay,p.id
)
select 
                            p.DeviceGroupId as DimensionsId, 
                            dg.DisplayName as DimensionsName,
                            p.ShiftDay , 
                            sc.MachineShiftDetailName,
                            p.MachinesShiftDetailId,
                            sum(c.productCount + c.DefectiveCount) as TotalCount, 
                            sum(c.QualifiedCount) as QualifiedCount 
from dgPlan as p
join ShiftCalendarsView as sc on p.MachinesShiftDetailId = sc.MachineShiftDetailId
join DeviceGroups as dg on p.DeviceGroupId = dg.id
join CapacityReportResults as c on p.planId = c.ProcessPlanId
group by p.DeviceGroupId,dg.DisplayName, p.ShiftDay, p.MachinesShiftDetailId,sc.MachineShiftDetailName

posted @ 2020-09-25 19:26 扶我起來我還要敲閱讀(675) 評論(1) 收藏舉報

刷新頁面返回頂部

OEE計算

公告