select A.MfgOrderName,C.货号,C.批次号,
C.一次质检时间,C.一次质检结果,C.二次质检时间,C.二次质检结果,一次生产合格率,B.一检通过率 from (select mm.MfgOrderName,
mm.a ,(mm.a+ISNULL(mm.b,0)) as al,
concat(convert(decimal(18,2),(mm.a*1.0/(mm.a+ISNULL(mm.b,0)))*100 ),'%') as '一次生产合格率'
from(select *
from (select m.MfgOrderName,m.tag ,count (*) as q from (select
case when cl.QC_Result =1 then 'a'
else 'b' end tag,
m.MfgOrderName, cc.ContainerName,cl.QC_Result, cl.N_FirstResult
FROM N_CompleteLotDetail mcl
LEFT JOIN MfgOrder m ON m.MfgOrderId = mcl.MfgOrderId
left join container c on m.MfgOrderId = c.MfgOrderId
LEFT JOIN N_CompletenessLots cl ON cl.N_CompletenessLotsId = mcl.N_CompletenessLotsId
left join N_CompletenessLotsDetail de on cl.N_CompletenessLotsId = de.N_CompletenessLotsId
LEFT JOIN Product P1 ON P1.ProductId=(CASE WHEN m.ProductId!='0000000000000000' THEN m.ProductId else(select RevOfRcdId from ProductBase where ProductBaseId=m.ProductBaseId) end)
LEFT JOIN N_PartNumber PN ON PN.N_PartNumberId=P1.N_PartNumberId
LEFT JOIN N_CompletenessSaveHistory H ON H.N_CompletenessNo=CL.N_CompletenessLotsName AND H.MfgOrderId=M.MfgOrderId
left join MoveInHistory mm on mm.HistoryId = de.RealLotNumberId
left join container cc on cc.ContainerId = de.RealLotNumberId
where 1=1 and cl.N_CancelBindDate is null
group by m.MfgOrderName, cc.ContainerName,cl.QC_Result, cl.N_FirstResult
)m
group by m.MfgOrderName,m.tag)n
pivot(max(q)for tag in([a],[b])) piv)mm)A
inner join
(select mm.MfgOrderName,
mm.a ,(mm.a+ISNULL(mm.b,0)) as al,
concat(convert(decimal(18,2),(mm.a*1.0/(mm.a+ISNULL(mm.b,0)))*100 ),'%') as '一检通过率'
from(select *
from (select m.MfgOrderName,m.tag1 ,count (*) as q from (select
case when cl.QC_Result =cl.QC_Result then 'a'
else 'b' end tag1,
m.MfgOrderName, cc.ContainerName,cl.QC_Result, cl.N_FirstResult
FROM N_CompleteLotDetail mcl
LEFT JOIN MfgOrder m ON m.MfgOrderId = mcl.MfgOrderId
left join container c on m.MfgOrderId = c.MfgOrderId
LEFT JOIN N_CompletenessLots cl ON cl.N_CompletenessLotsId = mcl.N_CompletenessLotsId
left join N_CompletenessLotsDetail de on cl.N_CompletenessLotsId = de.N_CompletenessLotsId
LEFT JOIN Product P1 ON P1.ProductId=(CASE WHEN m.ProductId!='0000000000000000' THEN m.ProductId else(select RevOfRcdId from ProductBase where ProductBaseId=m.ProductBaseId) end)
LEFT JOIN N_PartNumber PN ON PN.N_PartNumberId=P1.N_PartNumberId
LEFT JOIN N_CompletenessSaveHistory H ON H.N_CompletenessNo=CL.N_CompletenessLotsName AND H.MfgOrderId=M.MfgOrderId
left join MoveInHistory mm on mm.HistoryId = de.RealLotNumberId
left join container cc on cc.ContainerId = de.RealLotNumberId
where 1=1 and cl.N_CancelBindDate is null
group by m.MfgOrderName, cc.ContainerName,cl.QC_Result, cl.N_FirstResult
)m
group by m.MfgOrderName,m.tag1)n
pivot(max(q)for tag1 in([a],[b])) piv)mm) B on A.MfgOrderName = B.MfgOrderName
