USE [ChangHong_612]
GO
/****** Object: StoredProcedure [dbo].[st_MES_RptInspectShipment] Script Date: 10/10/2015 16:00:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lxf
-- Create date: 2014.7.1
-- Description: 出货检验报表
-- st_MES_RptInspectShipment '2015-07-18 08:00','2015-07-18 18:50:50','8860500431K','',''
-- =============================================
ALTER PROCEDURE [dbo].[st_MES_RptInspectShipment]
@StartDate VARCHAR(19)='2015-06-26',
@EndDate VARCHAR(19)='2015-06-26',
@ItemNo varchar(50)='8807400380K',
@MachineNo varchar(50)='',
@CheckMan varchar(20)='',
@BillNO nvarchar(60)=''
AS
BEGIN
declare @strSQL nvarchar(4000)
declare @allRecordSQL nvarchar(4000)
declare @strWhere nvarchar(4000)
--建临时表保存品质检验记录
IF object_id('tempdb..#InspecShipment') is not NULL drop table #InspecShipment
CREATE TABLE #InspecShipment
(
ID int ,
BillNO varchar(50),
MO varchar(20) ,
DispatchNo varchar(50),
DispatchPrior float,
ProcCode varchar(20),
StaCode varchar(10),
ItemNO varchar(50) ,
MachineNo varchar(20) ,
WorkMan varchar(2000) ,
CheckMan varchar(20) ,
CheckTime datetime ,
CheckReason varchar(200),
CheckType smallint ,
Remark varchar(200) ,
AbnormalNO varchar(50) ,
WeightSample int ,
OutSample int ,
InkSample int ,
SizeSample int ,
DeformSample int ,
ConfirmMan varchar(20) ,
ConfirmTime datetime ,
CreateMan nvarchar(200) ,
CreateTime datetime ,
UpdateMan nvarchar(200) ,
UpdateTime datetime,
CheckResult varchar(20),
ProductionTime datetime,
ProdBCCode varchar(20),
ProdNum int,
CustomerInformation varchar(200),
SamplingPlan varchar(200),
--InspectionMethods int,
ThemeColor varchar(50),
InspectionMethodDef int,
InspectionMethodSize int,
InspectionMethodPack int,
InspectionMethodVis int,
SamplingLevelPack varchar(10),
SamplingLevelVis varchar(10),
PackAQL varchar(50),
VisAQL varchar(50)
)
set @strWhere=''
set @strSQL='
select ID ,
BillNO ,
MO ,
DispatchNo ,
DispatchPrior ,
ProcCode ,
StaCode ,
ItemNO ,
MachineNo ,
WorkMan ,
CheckMan ,
CheckTime ,
CheckReason ,
CheckType ,
Remark ,
AbnormalNO ,
WeightSample ,
OutSample ,
InkSample ,
SizeSample ,
DeformSample ,
ConfirmMan ,
ConfirmTime ,
CreateMan ,
CreateTime ,
UpdateMan ,
UpdateTime,
CheckResult,
ProductionTime,
ProdBCCode,
ProdNum,CustomerInformation,SamplingPlan,ThemeColor,InspectionMethodDef,
InspectionMethodSize,InspectionMethodPack,InspectionMethodVis,SamplingLevelPack,SamplingLevelVis,PackAQL,VisAQL from MES_Inspect where checktype=6 '
--ProdNum,CustomerInformation,SamplingPlan,InspectionMethods,ThemeColor from MES_Inspect where checktype=6
set @strWhere=@strWhere+' and createTime between '''+@StartDate+''' and '''+@EndDate+''''
if @ItemNo<>''
begin
set @strWhere=@strWhere+' and itemno like ''%'+@ItemNo+'%'''
end
if @MachineNo<>''
begin
set @strWhere=@strWhere+' and machineno like ''%'+@MachineNo+'%'''
end
if @CheckMan<>''
begin
set @strWhere=@strWhere+' and createMan like ''%'+@CheckMan+'%'''
end
--20150926
if @BillNO<>''
begin
set @strWhere=@strWhere+' and BillNO like ''%'+@BillNO+'%'''
end
--20150925
print @strSQL+@strWhere
insert into #InspecShipment
execute(@strSQL+@strWhere)
declare @sql varchar(500)
select * from (
--select c.BillNO,c.AbnormalNo, c.SizeSample,case when c.InspectionMethods=0 then 'true' else 'false' end InspectionMethods,case when c.InspectionMethods=1 then 'true' else 'false' end InspectionMethods1,c.SamplingPlan,c.WeightSample,
select c.BillNO,c.AbnormalNo, c.SizeSample,c.DeformSample,
case when c.InspectionMethodDef=0 then 'true' else 'false' end InspectionMethodDef,case when c.InspectionMethodDef=1 then 'true' else 'false' end InspectionMethodDef1,
case when c.InspectionMethodSize=0 then 'true' else 'false' end InspectionMethodSize,case when c.InspectionMethodSize=1 then 'true' else 'false' end InspectionMethodSize1,
case when c.InspectionMethodPack=0 then 'true' else 'false' end InspectionMethodPack,case when c.InspectionMethodPack=1 then 'true' else 'false' end InspectionMethodPack1,
case when c.InspectionMethodVis=0 then 'true' else 'false' end InspectionMethodVis,case when c.InspectionMethodVis=1 then 'true' else 'false' end InspectionMethodVis1,
c.SamplingPlan,c.WeightSample,
c.OutSample,ProductionTime=convert(varchar(10),c.ProductionTime,120),c.ProdNum,c.CustomerInformation,c.MachineNo,c.WorkMan,CheckMan=(select Name from sys_user u where u.usercode=c.CheckMan),c.CheckTime,c.CheckReason,c.ConfirmMan,c.SamplingLevelPack,c.SamplingLevelVis,c.PackAQL,c.VisAQL,
c.Remark,c.CreateTime, c.ItemNO,i.CustNO,i.ItemName,CreateMan=(select empNamecn from mes_employee u where u.empid=c.CreateMan),c.ThemeColor Color,i.Model, s.PerformanceTest1,s.PerformanceTest2,s.PerformanceTest3,s.PerformanceTest4,s.PerformanceTest5,s.PerformanceTest6
,s.PerformanceTest7,s.PerformanceTest8,s.PerformanceTest9,s.PerformanceTest10,s.PerformanceTest11,s.PerformanceTest1Qty,s.PerformanceTest2Qty,s.PerformanceTest3Qty,s.PerformanceTest4Qty,s.PerformanceTest5Qty
,s.PerformanceTest6Qty,s.PerformanceTest7Qty,s.PerformanceTest8Qty,s.PerformanceTest9Qty,s.PerformanceTest10Qty,s.PerformanceTest11Qty
,s.PerformanceTest1Remark,s.PerformanceTest2Remark,s.PerformanceTest3Remark,s.PerformanceTest4Remark,s.PerformanceTest5Remark,s.PerformanceTest6Remark,s.PerformanceTest7Remark,s.PerformanceTest8Remark,s.PerformanceTest9Remark,s.PerformanceTest10Remark
,s.PerformanceTest11Remark,s.PackagingProject1,s.PackagingProject2,s.PackagingProject3,s.PackagingProject4
,s.PackagingProjectFalse1,s.PackagingProjectFalse2,s.PackagingProjectFalse3,s.PackagingProjectFalse4,s.PackagingProject1Qty,s.PackagingProject2Qty,s.PackagingProject3Qty,s.PackagingProject4Qty
,s.PackagingProject1Remark,s.PackagingProject2Remark,s.PackagingProject3Remark,s.PackagingProject4Remark,s.VisualInspection1,s.VisualInspection2,s.VisualInspection3,s.VisualInspection4
--,s.VisualInspection5,s.VisualInspection6,s.VisualInspection7,s.VisualInspectionFalse1,s.VisualInspectionFalse2,s.VisualInspectionFalse3,s.VisualInspectionFalse4,s.VisualInspectionFalse5
,s.VisualInspection5,s.VisualInspection6,s.VisualInspectionFalse1,s.VisualInspectionFalse2,s.VisualInspectionFalse3,s.VisualInspectionFalse4,s.VisualInspectionFalse5
--,s.VisualInspectionFalse6,s.VisualInspectionFalse7,s.VisualInspection1Qty,s.VisualInspection2Qty,s.VisualInspection3Qty,s.VisualInspection4Qty,s.VisualInspection5Qty
,s.VisualInspectionFalse6,s.VisualInspection1Qty,s.VisualInspection2Qty,s.VisualInspection3Qty,s.VisualInspection4Qty,s.VisualInspection5Qty
--,s.VisualInspection6Qty,s.VisualInspection7Qty,s.VisualInspection1Remark,s.VisualInspection2Remark,s.VisualInspection3Remark,s.VisualInspection4Remark,s.VisualInspection5Remark,s.VisualInspection6Remark,s.VisualInspection7Remark
,s.VisualInspection6Qty,s.VisualInspection1Remark,s.VisualInspection2Remark,s.VisualInspection3Remark,s.VisualInspection4Remark,s.VisualInspection5Remark,s.VisualInspection6Remark
--,g.LongDownDeformationValue1,g.LongDownDeformationValue2,g.LongDownDeformationValue3,g.LongDownDeformationValue4,g.LongDownDeformationValue5
--,g.LongDownGaugeValue1,g.LongDownGaugeValue2,g.LongDownGaugeValue3,g.LongDownGaugeValue4,g.LongDownGaugeValue5
--,g.LongMiddleDeformationValue1,g.LongMiddleDeformationValue2,g.LongMiddleDeformationValue3,g.LongMiddleDeformationValue4,g.LongMiddleDeformationValue5
--,g.LongMiddleGaugeValue1,g.LongMiddleGaugeValue2,g.LongMiddleGaugeValue3,g.LongMiddleGaugeValue4,g.LongMiddleGaugeValue5
--,g.LongUpDeformationValue1,g.LongUpDeformationValue2,g.LongUpDeformationValue3,g.LongUpDeformationValue4,g.LongUpDeformationValue5
,case when g.LongDownDeformationValue1='' then '－' else g.LongDownDeformationValue1 end LongDownDeformationValue1,
case when g.LongDownDeformationValue2='' then '－' else g.LongDownDeformationValue2 end LongDownDeformationValue2,
case when g.LongDownDeformationValue3='' then '－' else g.LongDownDeformationValue3 end LongDownDeformationValue3,
case when g.LongDownDeformationValue4='' then '－' else g.LongDownDeformationValue4 end LongDownDeformationValue4,
case when g.LongDownDeformationValue5='' then '－' else g.LongDownDeformationValue5 end LongDownDeformationValue5
,case when g.LongDownGaugeValue1='' then '－' else g.LongDownGaugeValue1 end LongDownGaugeValue1,
case when g.LongDownGaugeValue2='' then '－' else g.LongDownGaugeValue2 end LongDownGaugeValue2,
case when g.LongDownGaugeValue3='' then '－' else g.LongDownGaugeValue3 end LongDownGaugeValue3,
case when g.LongDownGaugeValue4='' then '－' else g.LongDownGaugeValue4 end LongDownGaugeValue4,
case when g.LongDownGaugeValue5='' then '－' else g.LongDownGaugeValue5 end LongDownGaugeValue5,
case when g.LongMiddleDeformationValue1='' then '－' else g.LongMiddleDeformationValue1 end LongMiddleDeformationValue1,
case when g.LongMiddleDeformationValue2='' then '－' else g.LongMiddleDeformationValue2 end LongMiddleDeformationValue2,
case when g.LongMiddleDeformationValue3='' then '－' else g.LongMiddleDeformationValue3 end LongMiddleDeformationValue3,
case when g.LongMiddleDeformationValue4='' then '－' else g.LongMiddleDeformationValue4 end LongMiddleDeformationValue4,
case when g.LongMiddleDeformationValue5='' then '－' else g.LongMiddleDeformationValue5 end LongMiddleDeformationValue5
,case when g.LongMiddleGaugeValue1='' then '－' else g.LongMiddleGaugeValue1 end LongMiddleGaugeValue1,
case when g.LongMiddleGaugeValue2='' then '－' else g.LongMiddleGaugeValue2 end LongMiddleGaugeValue2,
case when g.LongMiddleGaugeValue3='' then '－' else g.LongMiddleGaugeValue3 end LongMiddleGaugeValue3,
case when g.LongMiddleGaugeValue4='' then '－' else g.LongMiddleGaugeValue4 end LongMiddleGaugeValue4,
case when g.LongMiddleGaugeValue5='' then '－' else g.LongMiddleGaugeValue5 end LongMiddleGaugeValue5
,case when g.LongUpDeformationValue1='' then '－' else g.LongUpDeformationValue1 end LongUpDeformationValue1,
case when g.LongUpDeformationValue2='' then '－' else g.LongUpDeformationValue2 end LongUpDeformationValue2,
case when g.LongUpDeformationValue3='' then '－' else g.LongUpDeformationValue3 end LongUpDeformationValue3,
case when g.LongUpDeformationValue4='' then '－' else g.LongUpDeformationValue4 end LongUpDeformationValue4,
case when g.LongUpDeformationValue5='' then '－' else g.LongUpDeformationValue5 end LongUpDeformationValue5
,case when g.LongUpGaugeValue1='' then '－' else g.LongUpGaugeValue1 end LongUpGaugeValue1,
case when g.LongUpGaugeValue2='' then '－' else g.LongUpGaugeValue2 end LongUpGaugeValue2,
case when g.LongUpGaugeValue3='' then '－' else g.LongUpGaugeValue3 end LongUpGaugeValue3,
case when g.LongUpGaugeValue4='' then '－' else g.LongUpGaugeValue4 end LongUpGaugeValue4,
case when g.LongUpGaugeValue5='' then '－' else g.LongUpGaugeValue5 end LongUpGaugeValue5
,case when g.WidthDownDeformationValue1='' then '－' else g.WidthDownDeformationValue1 end WidthDownDeformationValue1,
case when g.WidthDownDeformationValue2='' then '－' else g.WidthDownDeformationValue2 end WidthDownDeformationValue2,
case when g.WidthDownDeformationValue3='' then '－' else g.WidthDownDeformationValue3 end WidthDownDeformationValue3,
case when g.WidthDownDeformationValue4='' then '－' else g.WidthDownDeformationValue4 end WidthDownDeformationValue4,
case when g.WidthDownDeformationValue5='' then '－' else g.WidthDownDeformationValue5 end WidthDownDeformationValue5
,case when g.WidthDownGaugeValue1='' then '－' else g.WidthDownGaugeValue1 end WidthDownGaugeValue1,
case when g.WidthDownGaugeValue2='' then '－' else g.WidthDownGaugeValue2 end WidthDownGaugeValue2,
case when g.WidthDownGaugeValue3='' then '－' else g.WidthDownGaugeValue3 end WidthDownGaugeValue3,
case when g.WidthDownGaugeValue4='' then '－' else g.WidthDownGaugeValue4 end WidthDownGaugeValue4,
case when g.WidthDownGaugeValue5='' then '－' else g.WidthDownGaugeValue5 end WidthDownGaugeValue5
,case when g.WidthMiddleDeformationValue1='' then '－' else g.WidthMiddleDeformationValue1 end WidthMiddleDeformationValue1,
case when g.WidthMiddleDeformationValue2='' then '－' else g.WidthMiddleDeformationValue2 end WidthMiddleDeformationValue2,
case when g.WidthMiddleDeformationValue3='' then '－' else g.WidthMiddleDeformationValue3 end WidthMiddleDeformationValue3,
case when g.WidthMiddleDeformationValue4='' then '－' else g.WidthMiddleDeformationValue4 end WidthMiddleDeformationValue4,
case when g.WidthMiddleDeformationValue5='' then '－' else g.WidthMiddleDeformationValue5 end WidthMiddleDeformationValue5
,case when g.WidthMiddleGaugeValue1='' then '－' else g.WidthMiddleGaugeValue1 end WidthMiddleGaugeValue1,
case when g.WidthMiddleGaugeValue2='' then '－' else g.WidthMiddleGaugeValue2 end WidthMiddleGaugeValue2,
case when g.WidthMiddleGaugeValue3='' then '－' else g.WidthMiddleGaugeValue3 end WidthMiddleGaugeValue3,
case when g.WidthMiddleGaugeValue4='' then '－' else g.WidthMiddleGaugeValue4 end WidthMiddleGaugeValue4,
case when g.WidthMiddleGaugeValue5='' then '－' else g.WidthMiddleGaugeValue5 end WidthMiddleGaugeValue5
,case when g.WidthUpDeformationValue1='' then '－' else g.WidthUpDeformationValue1 end WidthUpDeformationValue1,
case when g.WidthUpDeformationValue2='' then '－' else g.WidthUpDeformationValue2 end WidthUpDeformationValue2,
case when g.WidthUpDeformationValue3='' then '－' else g.WidthUpDeformationValue3 end WidthUpDeformationValue3,
case when g.WidthUpDeformationValue4='' then '－' else g.WidthUpDeformationValue4 end WidthUpDeformationValue4,
case when g.WidthUpDeformationValue5='' then '－' else g.WidthUpDeformationValue5 end WidthUpDeformationValue5
,case when g.WidthUpGaugeValue1='' then '－' else g.WidthUpGaugeValue1 end WidthUpGaugeValue1,
case when g.WidthUpGaugeValue2='' then '－' else g.WidthUpGaugeValue2 end WidthUpGaugeValue2,
case when g.WidthUpGaugeValue3='' then '－' else g.WidthUpGaugeValue3 end WidthUpGaugeValue3,
case when g.WidthUpGaugeValue4='' then '－' else g.WidthUpGaugeValue4 end WidthUpGaugeValue4,
case when g.WidthUpGaugeValue5='' then '－' else g.WidthUpGaugeValue5 end WidthUpGaugeValue5
--,g.WidthDownDeformationValue1,g.WidthDownDeformationValue2,g.WidthDownDeformationValue3,g.WidthDownDeformationValue4,g.WidthDownDeformationValue5
--,g.WidthDownGaugeValue1,g.WidthDownGaugeValue2,g.WidthDownGaugeValue3,g.WidthDownGaugeValue4,g.WidthDownGaugeValue5
--,g.WidthMiddleDeformationValue1,g.WidthMiddleDeformationValue2,g.WidthMiddleDeformationValue3,g.WidthMiddleDeformationValue4,g.WidthMiddleDeformationValue5
--,g.WidthMiddleGaugeValue1,g.WidthMiddleGaugeValue2,g.WidthMiddleGaugeValue3,g.WidthMiddleGaugeValue4,g.WidthMiddleGaugeValue5
--,g.WidthUpDeformationValue1,g.WidthUpDeformationValue2,g.WidthUpDeformationValue3,g.WidthUpDeformationValue4,g.WidthUpDeformationValue5
--,g.WidthUpGaugeValue1,g.WidthUpGaugeValue2,g.WidthUpGaugeValue3,g.WidthUpGaugeValue4,g.WidthUpGaugeValue5
,g.LongUpDeformationResult, g.LongMiddleDeformationResult , g.LongDownDeformationResult , g.WidthUpDeformationResult
, g.WidthMiddleDeformationResult , g.WidthDownDeformationResult
,g.LongUpGaugeResult , g.LongMiddleGaugeResult , g.LongDownGaugeResult , g.WidthUpGaugeResult
, g.WidthMiddleGaugeResult , g.WidthDownGaugeResult
,case when(s.PerformanceTest1='' and s.PerformanceTest2='' and s.PerformanceTest3='' and s.PerformanceTest4='' and s.PerformanceTest5=''
and s.PerformanceTest6='' and s.PerformanceTest7='' and s.PerformanceTest8='' and s.PerformanceTest9=''
and s.PerformanceTest10='' and s.PerformanceTest11='') then '－'
else
case when (isnull(s.PerformanceTest1,'NG')='OK' or s.PerformanceTest1='') and (isnull(s.PerformanceTest2,'NG')='OK' or s.PerformanceTest2='')
and (isnull(s.PerformanceTest3,'NG')='OK' or s.PerformanceTest3='') and (isnull(s.PerformanceTest4,'NG')='OK' or s.PerformanceTest4='')
and (isnull(s.PerformanceTest5,'NG')='OK' or s.PerformanceTest5='') and (isnull(s.PerformanceTest6,'NG')='OK' or s.PerformanceTest6='')
and (isnull(s.PerformanceTest7,'NG')='OK' or s.PerformanceTest7='') and (isnull(s.PerformanceTest8,'NG')='OK' or s.PerformanceTest8='')
and (isnull(s.PerformanceTest9,'NG')='OK' or s.PerformanceTest9='') and (isnull(s.PerformanceTest10,'NG')='OK' or s.PerformanceTest10='')
and (isnull(s.PerformanceTest11,'NG')='OK' or s.PerformanceTest11='') then 'true' else 'false' end end PerformanceCheck
,case when(s.PerformanceTest1='' and s.PerformanceTest2='' and s.PerformanceTest3='' and s.PerformanceTest4='' and s.PerformanceTest5=''
and s.PerformanceTest6='' and s.PerformanceTest7='' and s.PerformanceTest8='' and s.PerformanceTest9='' and s.PerformanceTest10='' and s.PerformanceTest11='') then '－'
else
case when (isnull(s.PerformanceTest1,'NG')='OK' or s.PerformanceTest1='') and (isnull(s.PerformanceTest2,'NG')='OK' or s.PerformanceTest2='')
and (isnull(s.PerformanceTest3,'NG')='OK' or s.PerformanceTest3='') and (isnull(s.PerformanceTest4,'NG')='OK' or s.PerformanceTest4='')
and (isnull(s.PerformanceTest5,'NG')='OK' or s.PerformanceTest5='') and (isnull(s.PerformanceTest6,'NG')='OK' or s.PerformanceTest6='')
and (isnull(s.PerformanceTest7,'NG')='OK' or s.PerformanceTest7='') and (isnull(s.PerformanceTest8,'NG')='OK' or s.PerformanceTest8='')
and (isnull(s.PerformanceTest9,'NG')='OK' or s.PerformanceTest9='') and (isnull(s.PerformanceTest10,'NG')='OK' or s.PerformanceTest10='')
and (isnull(s.PerformanceTest11,'NG')='OK' or s.PerformanceTest11='') then 'false' else 'true' end end PerformanceCheckFalse
--,case when isnull(g.LongUpDeformationResult,'NG')='OK' and isnull(g.LongMiddleDeformationResult,'NG')='OK' and isnull(g.LongDownDeformationResult,'NG')='OK' and isnull(g.WidthUpDeformationResult,'NG')='OK'
-- and isnull(g.WidthMiddleDeformationResult,'NG')='OK' and isnull(g.WidthDownDeformationResult,'NG')='OK' then 'OK' else 'NG' end DeformationResult
,DeformationResult= case when((g.LongUpDeformationResult is Null or g.LongUpDeformationResult='') and (g.LongMiddleDeformationResult is Null or g.LongMiddleDeformationResult='') and (g.LongDownDeformationResult is Null or g.LongDownDeformationResult='')
and (g.WidthUpDeformationResult is Null or g.WidthUpDeformationResult='')and (g.WidthMiddleDeformationResult is Null or g.WidthMiddleDeformationResult='')
and (g.WidthDownDeformationResult is Null or g.WidthDownDeformationResult='')) then '－'
else
case when (isnull(g.LongUpDeformationResult,'OK')='OK' or g.LongUpDeformationResult='') and (isnull(g.LongMiddleDeformationResult,'OK')='OK' or g.LongMiddleDeformationResult='') and (isnull(g.LongDownDeformationResult,'OK')='OK' or g.LongDownDeformationResult='')
and (isnull(g.WidthUpDeformationResult,'OK')='OK' or g.WidthUpDeformationResult='') and (isnull(g.WidthMiddleDeformationResult,'OK')='OK' or g.WidthMiddleDeformationResult='') and (isnull(g.WidthDownDeformationResult,'OK')='OK' or g.WidthDownDeformationResult='') then 'true' else 'false' end
end
,DeformationResultFalse= case when((g.LongUpDeformationResult is Null or g.LongUpDeformationResult='') and (g.LongMiddleDeformationResult is Null or g.LongMiddleDeformationResult='') and (g.LongDownDeformationResult is Null or g.LongDownDeformationResult='')
and (g.WidthUpDeformationResult is Null or g.WidthUpDeformationResult='')and (g.WidthMiddleDeformationResult is Null or g.WidthMiddleDeformationResult='')
and (g.WidthDownDeformationResult is Null or g.WidthDownDeformationResult='')) then '－'
else
case when (isnull(g.LongUpDeformationResult,'OK')='OK' or g.LongUpDeformationResult='') and (isnull(g.LongMiddleDeformationResult,'OK')='OK' or g.LongMiddleDeformationResult='') and (isnull(g.LongDownDeformationResult,'OK')='OK' or g.LongDownDeformationResult='')
and (isnull(g.WidthUpDeformationResult,'OK')='OK' or g.WidthUpDeformationResult='') and (isnull(g.WidthMiddleGaugeResult,'OK')='OK' or g.WidthMiddleGaugeResult='') and (isnull(g.WidthDownDeformationResult,'OK')='OK' or g.WidthDownDeformationResult='') then 'false' else 'true' end
end
,GaugeResult= case when((g.LongUpGaugeResult is Null or g.LongUpGaugeResult='') and (g.LongMiddleGaugeResult is Null or g.LongMiddleGaugeResult='') and (g.LongDownGaugeResult is Null or g.LongDownGaugeResult='')
and (g.WidthUpGaugeResult is Null or g.WidthUpGaugeResult='')and (g.WidthMiddleGaugeResult is Null or g.WidthMiddleGaugeResult='')
and (g.WidthDownGaugeResult is Null or g.WidthDownGaugeResult='')) then '－'
else
case when (isnull(g.LongUpGaugeResult,'OK')='OK' or g.LongUpGaugeResult='') and (isnull(g.LongMiddleGaugeResult,'OK')='OK' or g.LongMiddleGaugeResult='') and (isnull(g.LongDownGaugeResult,'OK')='OK' or g.LongDownGaugeResult='')
and (isnull(g.WidthUpGaugeResult,'OK')='OK' or g.WidthUpGaugeResult='') and (isnull(g.WidthMiddleGaugeResult,'OK')='OK' or g.WidthMiddleGaugeResult='') and (isnull(g.WidthDownGaugeResult,'OK')='OK' or g.WidthDownGaugeResult='') then 'true' else 'false' end
end
,GaugeResultFalse= case when((g.LongUpGaugeResult is Null or g.LongUpGaugeResult='') and (g.LongMiddleGaugeResult is Null or g.LongMiddleGaugeResult='') and (g.LongDownGaugeResult is Null or g.LongDownGaugeResult='')
and (g.WidthUpGaugeResult is Null or g.WidthUpGaugeResult='')and (g.WidthMiddleGaugeResult is Null or g.WidthMiddleGaugeResult='')
and (g.WidthDownGaugeResult is Null or g.WidthDownGaugeResult='')) then '－'
else
case when (isnull(g.LongUpGaugeResult,'OK')='OK' or g.LongUpGaugeResult='') and (isnull(g.LongMiddleGaugeResult,'OK')='OK' or g.LongMiddleGaugeResult='') and (isnull(g.LongDownGaugeResult,'OK')='OK' or g.LongDownGaugeResult='')
and (isnull(g.WidthUpGaugeResult,'OK')='OK' or g.WidthUpGaugeResult='') and (isnull(g.WidthMiddleGaugeResult,'OK')='OK' or g.WidthMiddleGaugeResult='') and (isnull(g.WidthDownGaugeResult,'OK')='OK' or g.WidthDownGaugeResult='') then 'false' else 'true' end
end
,PackResult=case when ((s.PackagingProjectFalse1='false' or s.PackagingProjectFalse1='') and (s.PackagingProjectFalse2='false' or s.PackagingProjectFalse2='') and
(s.PackagingProjectFalse3='false' or s.PackagingProjectFalse3='') and (s.PackagingProjectFalse4='false' or s.PackagingProjectFalse4='')) then 'true' else 'false' end
,PackResultFalse=case when ((s.PackagingProjectFalse1='false' or s.PackagingProjectFalse1='') and (s.PackagingProjectFalse2='false' or s.PackagingProjectFalse2='') and
(s.PackagingProjectFalse3='false' or s.PackagingProjectFalse3='') and (s.PackagingProjectFalse4='false' or s.PackagingProjectFalse4='')) then 'false' else 'true' end
,VisResult=case when ((s.VisualInspectionFalse1='false' or s.VisualInspectionFalse1='') and (s.VisualInspectionFalse2='false' or s.VisualInspectionFalse2='') and
(s.VisualInspectionFalse3='false' or s.VisualInspectionFalse3='') and (s.VisualInspectionFalse4='false' or s.VisualInspectionFalse4='')
and (s.VisualInspectionFalse5='false' or s.VisualInspectionFalse5='') and (s.VisualInspectionFalse6='false' or s.VisualInspectionFalse6='')
--and (s.VisualInspectionFalse7='false' or s.VisualInspectionFalse7='')) then 'true' else 'false' end
) then 'true' else 'false' end
,VisResultFalse=case when ((s.VisualInspectionFalse1='false' or s.VisualInspectionFalse1='') and (s.VisualInspectionFalse2='false' or s.VisualInspectionFalse2='') and
(s.VisualInspectionFalse3='false' or s.VisualInspectionFalse3='') and (s.VisualInspectionFalse4='false' or s.VisualInspectionFalse4='')
and (s.VisualInspectionFalse5='false' or s.VisualInspectionFalse5='') and (s.VisualInspectionFalse6='false' or s.VisualInspectionFalse6='')
--and (s.VisualInspectionFalse7='false' or s.VisualInspectionFalse7='')) then 'false' else 'true' end
) then 'false' else 'true' end
--
,case when long is not null and longUp is not null and (g.LongUpGaugeValue1!='' or g.LongUpGaugeValue2!='' or g.LongUpGaugeValue3!=''
or g.LongUpGaugeValue4!='' or g.LongUpGaugeValue5!='')
then '长:'+cast(long-cast(REPLACE(CAST(longdown as varchar(10)),'-','') as float) as varchar(10)) +'~'+cast(long+longup as varchar(10))
end
LongUpDown1
,case when long is not null and longUp is not null and (g.LongMiddleGaugeValue1!='' or g.LongMiddleGaugeValue2!='' or g.LongMiddleGaugeValue3!=''
or g.LongMiddleGaugeValue4!='' or g.LongMiddleGaugeValue5!='')
then '长:'+cast(long-cast(REPLACE(CAST(longdown as varchar(10)),'-','') as float) as varchar(10)) +'~'+cast(long+longup as varchar(10))
end
LongUpDown2
,case when long is not null and longUp is not null and (g.LongDownGaugeValue1!='' or g.LongDownGaugeValue2!='' or g.LongDownGaugeValue3!=''
or g.LongDownGaugeValue4!='' or g.LongDownGaugeValue5!='')
then '长:'+cast(long-cast(REPLACE(CAST(longdown as varchar(10)),'-','') as float) as varchar(10)) +'~'+cast(long+longup as varchar(10))
end
LongUpDown3
--,case when Downlong is not null and DownlongUp is not null
-- then '下长:'+cast(Downlong-cast(REPLACE(CAST(Downlongdown as varchar(10)),'-','') as float) as varchar(10)) +'~'+cast(Downlong+Downlongup as varchar(10))
-- end
-- LongDown
,case when width is not null and widthUp is not null and (g.WidthUpGaugeValue1!='' or g.WidthUpGaugeValue2!='' or g.WidthUpGaugeValue3!=''
or g.WidthUpGaugeValue4!='' or g.WidthUpGaugeValue5!='')
then '宽:'+cast(width-cast(REPLACE(CAST(widthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(width+widthup as varchar(10))
end
WidthUpDown1
,case when width is not null and widthUp is not null and (g.WidthMiddleGaugeValue1!='' or g.WidthMiddleGaugeValue2!='' or g.WidthMiddleGaugeValue3!=''
or g.WidthMiddleGaugeValue4!='' or g.WidthMiddleGaugeValue5!='')
then '宽:'+cast(width-cast(REPLACE(CAST(widthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(width+widthup as varchar(10))
end
WidthUpDown2
,case when width is not null and widthUp is not null and (g.WidthDownGaugeValue1!='' or g.WidthDownGaugeValue2!='' or g.WidthDownGaugeValue3!=''
or g.WidthDownGaugeValue4!='' or g.WidthDownGaugeValue5!='')
then '宽:'+cast(width-cast(REPLACE(CAST(widthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(width+widthup as varchar(10))
end
WidthUpDown3
,case when DfLong is not null and DfLongUp is not null and (g.LongUpDeformationValue1!='' or g.LongUpDeformationValue2!='' or g.LongUpDeformationValue3!=''
or g.LongUpDeformationValue4!='' or g.LongUpDeformationValue5!='')
then '长:'+cast(DfLong-cast(REPLACE(CAST(DfLongDown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(DfLong+DfLongup as varchar(10))
end
LongDeformationUpDown1
,case when DfLong is not null and DfLongUp is not null and (g.LongMiddleDeformationValue1!='' or g.LongMiddleDeformationValue2!='' or g.LongMiddleDeformationValue3!=''
or g.LongMiddleDeformationValue4!='' or g.LongMiddleDeformationValue5!='')
then '长:'+cast(DfLong-cast(REPLACE(CAST(DfLongDown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(DfLong+DfLongup as varchar(10))
end
LongDeformationUpDown2
,case when DfLong is not null and DfLongUp is not null and (g.LongDownDeformationValue1!='' or g.LongDownDeformationValue2!='' or g.LongDownDeformationValue3!=''
or g.LongDownDeformationValue4!='' or g.LongDownDeformationValue5!='')
then '长:'+cast(DfLong-cast(REPLACE(CAST(DfLongDown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(DfLong+DfLongup as varchar(10))
end
LongDeformationUpDown3
,case when Dfwidth is not null and DfWidthUp is not null and (g.WidthUpDeformationValue1!='' or g.WidthUpDeformationValue2!='' or g.WidthUpDeformationValue3!=''
or g.WidthUpDeformationValue4!='' or g.WidthUpDeformationValue5!='')
then '宽:'+cast(Dfwidth-cast(REPLACE(CAST(Dfwidthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(Dfwidth+Dfwidthup as varchar(10))
end
WidthDeformationUpDown1
,case when Dfwidth is not null and DfWidthUp is not null and (g.WidthMiddleDeformationValue1!='' or g.WidthMiddleDeformationValue2!='' or g.WidthMiddleDeformationValue3!=''
or g.WidthMiddleDeformationValue4!='' or g.WidthMiddleDeformationValue5!='')
then '宽:'+cast(Dfwidth-cast(REPLACE(CAST(Dfwidthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(Dfwidth+Dfwidthup as varchar(10))
end
WidthDeformationUpDown2
,case when Dfwidth is not null and DfWidthUp is not null and (g.WidthDownDeformationValue1!='' or g.WidthDownDeformationValue2!='' or g.WidthDownDeformationValue3!=''
or g.WidthDownDeformationValue4!='' or g.WidthDownDeformationValue5!='')
then '宽:'+cast(Dfwidth-cast(REPLACE(CAST(Dfwidthdown as varchar(10)),'-','') as float) as varchar(10))+'~'+cast(Dfwidth+Dfwidthup as varchar(10))
end
WidthDeformationUpDown3,
case CheckResult
when 'OK' then 'true'
else 'false'
end
CheckResultTrue,
case CheckResult
when 'NG'
then 'true'
else 'false'
end
CheckResultFalse
from #InspecShipment c left join
(select billno,
max(CASE ItemCode WHEN 'PerformanceTest1' THEN ItemValue END) AS 'PerformanceTest1',
max(CASE ItemCode WHEN 'PerformanceTest2' THEN ItemValue END) AS 'PerformanceTest2',
max(CASE ItemCode WHEN 'PerformanceTest3' THEN ItemValue END) AS 'PerformanceTest3',
max(CASE ItemCode WHEN 'PerformanceTest4' THEN ItemValue END) AS 'PerformanceTest4',
max(CASE ItemCode WHEN 'PerformanceTest5' THEN ItemValue END) AS 'PerformanceTest5',
max(CASE ItemCode WHEN 'PerformanceTest6' THEN ItemValue END) AS 'PerformanceTest6',
max(CASE ItemCode WHEN 'PerformanceTest7' THEN ItemValue END) AS 'PerformanceTest7',
max(CASE ItemCode WHEN 'PerformanceTest8' THEN ItemValue END) AS 'PerformanceTest8',
max(CASE ItemCode WHEN 'PerformanceTest9' THEN ItemValue END) AS 'PerformanceTest9',
max(CASE ItemCode WHEN 'PerformanceTest10' THEN ItemValue END) AS 'PerformanceTest10',
max(CASE ItemCode WHEN 'PerformanceTest11' THEN ItemValue END) AS 'PerformanceTest11',
max(CASE ItemCode WHEN 'PerformanceTest1' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest1Qty',
max(CASE ItemCode WHEN 'PerformanceTest2' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest2Qty',
max(CASE ItemCode WHEN 'PerformanceTest3' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest3Qty',
max(CASE ItemCode WHEN 'PerformanceTest4' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest4Qty',
max(CASE ItemCode WHEN 'PerformanceTest5' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest5Qty',
max(CASE ItemCode WHEN 'PerformanceTest6' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest6Qty',
max(CASE ItemCode WHEN 'PerformanceTest7' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest7Qty',
max(CASE ItemCode WHEN 'PerformanceTest8' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest8Qty',
max(CASE ItemCode WHEN 'PerformanceTest9' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest9Qty',
max(CASE ItemCode WHEN 'PerformanceTest10' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest10Qty',
max(CASE ItemCode WHEN 'PerformanceTest11' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PerformanceTest11Qty',
max(CASE ItemCode WHEN 'PerformanceTest1' THEN CheckResult end) AS 'PerformanceTest1Remark',
max(CASE ItemCode WHEN 'PerformanceTest2' THEN CheckResult end) AS 'PerformanceTest2Remark',
max(CASE ItemCode WHEN 'PerformanceTest3' THEN CheckResult end) AS 'PerformanceTest3Remark',
max(CASE ItemCode WHEN 'PerformanceTest4' THEN CheckResult end) AS 'PerformanceTest4Remark',
max(CASE ItemCode WHEN 'PerformanceTest5' THEN CheckResult end) AS 'PerformanceTest5Remark',
max(CASE ItemCode WHEN 'PerformanceTest6' THEN CheckResult end) AS 'PerformanceTest6Remark',
max(CASE ItemCode WHEN 'PerformanceTest7' THEN CheckResult end) AS 'PerformanceTest7Remark',
max(CASE ItemCode WHEN 'PerformanceTest8' THEN CheckResult end) AS 'PerformanceTest8Remark',
max(CASE ItemCode WHEN 'PerformanceTest9' THEN CheckResult end) AS 'PerformanceTest9Remark',
max(CASE ItemCode WHEN 'PerformanceTest10' THEN CheckResult end) AS 'PerformanceTest10Remark',
max(CASE ItemCode WHEN 'PerformanceTest11' THEN CheckResult end) AS 'PerformanceTest11Remark',
max(CASE ItemCode WHEN 'PerformanceCheck' THEN case CheckResult when 'OK' then 1 else 0 end END) AS 'PerformanceCheck',
max(CASE ItemCode WHEN 'SizeCheck' THEN case CheckResult when 'OK' then 1 else 0 end END) AS 'SizeCheck',
max(CASE ItemCode WHEN 'PerformanceCheck' THEN case CheckResult when 'NG' then 1 else 0 end END) AS 'PerformanceCheckFalse',
max(CASE ItemCode WHEN 'SizeCheck' THEN case CheckResult when 'NG' then 1 else 0 end END) AS 'SizeCheckFalse',
max(CASE ItemCode WHEN 'PackagingProject1' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'PackagingProject1',
max(CASE ItemCode WHEN 'PackagingProject2' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'PackagingProject2',
max(CASE ItemCode WHEN 'PackagingProject3' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'PackagingProject3',
max(CASE ItemCode WHEN 'PackagingProject4' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'PackagingProject4',
max(CASE ItemCode WHEN 'PackagingProject1' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'PackagingProjectFalse1',
max(CASE ItemCode WHEN 'PackagingProject2' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'PackagingProjectFalse2',
max(CASE ItemCode WHEN 'PackagingProject3' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'PackagingProjectFalse3',
max(CASE ItemCode WHEN 'PackagingProject4' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'PackagingProjectFalse4',
max(CASE ItemCode WHEN 'PackagingProject1' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PackagingProject1Qty',
max(CASE ItemCode WHEN 'PackagingProject2' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PackagingProject2Qty',
max(CASE ItemCode WHEN 'PackagingProject3' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PackagingProject3Qty',
max(CASE ItemCode WHEN 'PackagingProject4' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'PackagingProject4Qty',
max(CASE ItemCode WHEN 'PackagingProject1' THEN CheckResult end) AS 'PackagingProject1Remark',
max(CASE ItemCode WHEN 'PackagingProject2' THEN CheckResult end) AS 'PackagingProject2Remark',
max(CASE ItemCode WHEN 'PackagingProject3' THEN CheckResult end) AS 'PackagingProject3Remark',
max(CASE ItemCode WHEN 'PackagingProject4' THEN CheckResult end) AS 'PackagingProject4Remark',
max(CASE ItemCode WHEN 'VisualInspection1' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection1',
max(CASE ItemCode WHEN 'VisualInspection2' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection2',
max(CASE ItemCode WHEN 'VisualInspection3' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection3',
max(CASE ItemCode WHEN 'VisualInspection4' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection4',
max(CASE ItemCode WHEN 'VisualInspection5' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection5',
max(CASE ItemCode WHEN 'VisualInspection6' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection6',
--max(CASE ItemCode WHEN 'VisualInspection7' THEN case ItemValue when 'OK' then 'true' else 'false' end END) AS 'VisualInspection7',
max(CASE ItemCode WHEN 'VisualInspection1' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse1',
max(CASE ItemCode WHEN 'VisualInspection2' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse2',
max(CASE ItemCode WHEN 'VisualInspection3' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse3',
max(CASE ItemCode WHEN 'VisualInspection4' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse4',
max(CASE ItemCode WHEN 'VisualInspection5' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse5',
max(CASE ItemCode WHEN 'VisualInspection6' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse6',
--max(CASE ItemCode WHEN 'VisualInspection7' THEN case ItemValue when 'NG' then 'true' else 'false' end END) AS 'VisualInspectionFalse7',
max(CASE ItemCode WHEN 'VisualInspection1' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection1Qty',
max(CASE ItemCode WHEN 'VisualInspection2' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection2Qty',
max(CASE ItemCode WHEN 'VisualInspection3' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection3Qty',
max(CASE ItemCode WHEN 'VisualInspection4' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection4Qty',
max(CASE ItemCode WHEN 'VisualInspection5' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection5Qty',
max(CASE ItemCode WHEN 'VisualInspection6' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection6Qty',
--max(CASE ItemCode WHEN 'VisualInspection7' THEN case when ItemValue='' then '/' else Convert(varchar(10),CHeckqty) end END) AS 'VisualInspection7Qty',
max(CASE ItemCode WHEN 'VisualInspection1' THEN CheckResult end) AS 'VisualInspection1Remark',
max(CASE ItemCode WHEN 'VisualInspection2' THEN CheckResult end) AS 'VisualInspection2Remark',
max(CASE ItemCode WHEN 'VisualInspection3' THEN CheckResult end) AS 'VisualInspection3Remark',
max(CASE ItemCode WHEN 'VisualInspection4' THEN CheckResult end) AS 'VisualInspection4Remark',
max(CASE ItemCode WHEN 'VisualInspection5' THEN CheckResult end) AS 'VisualInspection5Remark',
max(CASE ItemCode WHEN 'VisualInspection6' THEN CheckResult end) AS 'VisualInspection6Remark'
--max(CASE ItemCode WHEN 'VisualInspection7' THEN CheckResult end) AS 'VisualInspection7Remark'
from MES_InspectSingle group by billno)
s
on c.BillNO=s.BillNO
left join
(select billno,
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN GaugeValue1 END) AS 'LongUpGaugeValue1',
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN GaugeValue2 END) AS 'LongUpGaugeValue2',
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN GaugeValue3 END) AS 'LongUpGaugeValue3',
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN GaugeValue4 END) AS 'LongUpGaugeValue4',
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN GaugeValue5 END) AS 'LongUpGaugeValue5',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN GaugeValue1 END) AS 'LongMiddleGaugeValue1',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN GaugeValue2 END) AS 'LongMiddleGaugeValue2',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN GaugeValue3 END) AS 'LongMiddleGaugeValue3',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN GaugeValue4 END) AS 'LongMiddleGaugeValue4',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN GaugeValue5 END) AS 'LongMiddleGaugeValue5',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN GaugeValue1 END) AS 'LongDownGaugeValue1',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN GaugeValue2 END) AS 'LongDownGaugeValue2',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN GaugeValue3 END) AS 'LongDownGaugeValue3',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN GaugeValue4 END) AS 'LongDownGaugeValue4',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN GaugeValue5 END) AS 'LongDownGaugeValue5',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthUpGaugeValue1',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthUpGaugeValue2',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthUpGaugeValue3',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthUpGaugeValue4',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthUpGaugeValue5',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthMiddleGaugeValue1',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthMiddleGaugeValue2',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthMiddleGaugeValue3',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthMiddleGaugeValue4',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthMiddleGaugeValue5',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthDownGaugeValue1',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthDownGaugeValue2',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthDownGaugeValue3',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthDownGaugeValue4',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthDownGaugeValue5',
--5是变形量
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN GaugeValue1 END) AS 'LongUpDeformationValue1',
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN GaugeValue2 END) AS 'LongUpDeformationValue2',
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN GaugeValue3 END) AS 'LongUpDeformationValue3',
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN GaugeValue4 END) AS 'LongUpDeformationValue4',
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN GaugeValue5 END) AS 'LongUpDeformationValue5',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN GaugeValue1 END) AS 'LongMiddleDeformationValue1',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN GaugeValue2 END) AS 'LongMiddleDeformationValue2',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN GaugeValue3 END) AS 'LongMiddleDeformationValue3',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN GaugeValue4 END) AS 'LongMiddleDeformationValue4',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN GaugeValue5 END) AS 'LongMiddleDeformationValue5',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN GaugeValue1 END) AS 'LongDownDeformationValue1',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN GaugeValue2 END) AS 'LongDownDeformationValue2',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN GaugeValue3 END) AS 'LongDownDeformationValue3',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN GaugeValue4 END) AS 'LongDownDeformationValue4',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN GaugeValue5 END) AS 'LongDownDeformationValue5',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthUpDeformationValue1',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthUpDeformationValue2',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthUpDeformationValue3',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthUpDeformationValue4',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthUpDeformationValue5',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthMiddleDeformationValue1',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthMiddleDeformationValue2',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthMiddleDeformationValue3',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthMiddleDeformationValue4',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthMiddleDeformationValue5',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN GaugeValue1 END) AS 'WidthDownDeformationValue1',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN GaugeValue2 END) AS 'WidthDownDeformationValue2',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN GaugeValue3 END) AS 'WidthDownDeformationValue3',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN GaugeValue4 END) AS 'WidthDownDeformationValue4',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN GaugeValue5 END) AS 'WidthDownDeformationValue5',
--max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongUpGaugeResult',
--max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongMiddleGaugeResult',
--max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongDownGaugeResult',
--max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthUpGaugeResult',
--max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthMiddleGaugeResult',
--max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthDownGaugeResult',
--max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongUpDeformationResult',
--max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongMiddleDeformationResult',
--max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN isnull(CheckResult,'NG') END) AS 'LongDownDeformationResult',
--max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthUpDeformationResult',
--max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthMiddleDeformationResult',
--max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN isnull(CheckResult,'NG') END) AS 'WidthDownDeformationResult'
/*Update by zhuss 2014-09-25*/
max(CASE when ItemType=3 and ItemCode='上长' and Position='长' THEN CheckResult END) AS 'LongUpGaugeResult',
max(CASE when ItemType=3 and ItemCode='中长' and Position='长' THEN CheckResult END) AS 'LongMiddleGaugeResult',
max(CASE when ItemType=3 and ItemCode='下长' and Position='长' THEN CheckResult END) AS 'LongDownGaugeResult',
max(CASE when ItemType=3 and ItemCode='左宽' and Position='宽' THEN CheckResult END) AS 'WidthUpGaugeResult',
max(CASE when ItemType=3 and ItemCode='中宽' and Position='宽' THEN CheckResult END) AS 'WidthMiddleGaugeResult',
max(CASE when ItemType=3 and ItemCode='右宽' and Position='宽' THEN CheckResult END) AS 'WidthDownGaugeResult',
max(CASE when ItemType=5 and ItemCode='上长' and Position='长' THEN CheckResult END) AS 'LongUpDeformationResult',
max(CASE when ItemType=5 and ItemCode='中长' and Position='长' THEN CheckResult END) AS 'LongMiddleDeformationResult',
max(CASE when ItemType=5 and ItemCode='下长' and Position='长' THEN CheckResult END) AS 'LongDownDeformationResult',
max(CASE when ItemType=5 and ItemCode='左宽' and Position='宽' THEN CheckResult END) AS 'WidthUpDeformationResult',
max(CASE when ItemType=5 and ItemCode='中宽' and Position='宽' THEN CheckResult END) AS 'WidthMiddleDeformationResult',
max(CASE when ItemType=5 and ItemCode='右宽' and Position='宽' THEN CheckResult END) AS 'WidthDownDeformationResult'
from MES_InspectGauge group by billno)
g
on g.BillNO=c.BillNO
left join MES_Item i on c.ItemNO=i.itemno
left join V_DispatchOrder d on d.dispatchno=c.DispatchNo and c.DispatchPrior=d.DispatchPrior
left join MES_WorkOrder w on d.mo=w.mo) a order by createtime desc
END