OEE计算

记录一下~   你们应该用不到~

--当一个班次时间内同时加工多种产品时，生产节拍=（零件1节拍*班次内零件1设备实际产量+零件2节拍*班次内零件2设备实际产量+...+）/（班次内零件1设备实际产量+班次内零件2设备实际产量+...+）
with
--1、依据公式计算各个零件实际产量及（零件节拍*班次内零件设备实际产量）值
beatInternal as (
                select 
                                c.machineid , 
                                c.MachineCode,
                                c.ShiftDay, 
                                c.MachinesShiftDetailId,
                                c.Yield as RealYield,
                                c.Yield * mb.Beat as  TotalTime
                from (
                        select            --班次内各产品产量计算
                                        c.machineid , 
                                        c.MachineCode,
                                        c.ShiftDetail_ShiftDay as ShiftDay, 
                                        c.MachinesShiftDetailId,
                                        c.ProductId,
                                        sum(c.Yield) as Yield 
                        from Capacities as c 
                        group by c.MachineId,c.MachineCode,c.ShiftDetail_ShiftDay,c.MachinesShiftDetailId,c.ProductId
                ) as c    
                    join Products as p on c.ProductId = p.Id
                    join MachineBeats as mb on (p.Code = mb.ProductNumber and mb.MachineId = c.MachineId)
),
--2、依据公式计算班次内 各设备生产节拍
beat as(
                select 
                                                        b.machineid , 
                                                        b.MachineCode,
                                                        b.ShiftDay, 
                                                        b.MachinesShiftDetailId,
                                                        sum(b.TotalTime ) / sum(b.RealYield) as mbeat  --设备生产节拍
                from beatInternal as b
                group by b.MachineId,b.MachineCode,b.ShiftDay,b.MachinesShiftDetailId
),
--3、计算班次内设备运行时间
realTime AS (
                                select machineid, 
                                    MachineCode,
                                    ShiftDetail_ShiftDay as ShiftDay,
                                    MachinesShiftDetailId,  
                                    sum(case code when 'Run' then Duration else 0 end) as Duration 
                                from states 
                                where  machineid in (1)
                                    and ShiftDetail_ShiftDay between  '' and ''
                                group by machineid ,MachineCode,ShiftDetail_ShiftDay,MachinesShiftDetailId
            ),
--4、计算班次内设备实际产量
realCapacity as (
                        select 
                                        c.machineid , 
                                        c.MachineCode,
                                        c.ShiftDetail_ShiftDay as ShiftDay, 
                                        c.MachinesShiftDetailId,
                                        sum(c.Yield) as Yield 
                        from Capacities as c 
                        group by c.MachineId,c.MachineCode,c.ShiftDetail_ShiftDay,c.MachinesShiftDetailId
),
--5、依据公式计算班次内设备计划产量
planCapacity as(
                        select 
                            b.machineid, 
                            b.MachineCode,
                            b.ShiftDay,
                            b.MachinesShiftDetailId,  
                            r.Duration / b.mbeat as planCount  
                        from beat as b
                            join realTime as r on (b.MachineId= r.MachineId and b.ShiftDay = r.ShiftDay and b.MachinesShiftDetailId = r.MachinesShiftDetailId)
)
--最后计算班次内各设备性能运转率   =实际产量/计划产量
select    
    rc.machineid, 
    rc.MachineCode,
    rc.ShiftDay,
    rc.MachinesShiftDetailId,  
    rc.Yield / pc.planCount as PerformanceRate  
from realCapacity as rc
    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