Moka_CShare_FactoryTool/Doc/G客户相关/查询AMResult表NTF数据.sql

declare @Line varchar(30) 
declare @Station varchar(30) 
declare @ODF varchar(30)
declare @TestDate DATE
declare @TestHour INT
declare @THour varchar
set @Line='Test'
set @Station='DataBurning'
set @ODF='DGG7509699'
set @TestDate='2021-12-02'
set @TestHour=10

SELECT * FROM [GET_HOURRESULT] (@Line, @Station, @ODF, @TestDate, @TestHour)

/*
print @Line

select 
--抄写总数(包含重流和非重流的)
count(1) as 'Total',
--实际数量：非重流的,即ReDo=0
count(case when ReDo = '0' then 1 else null end) as 'RealTotal', 
--有且只有一次测试且成功 Test01=FinalTest=PASS & Test02=NULL
count(case when ResultType = 'OncePass' then 1 else null end) as 'OncePass',
--有且只有一次测试且失败 Test01=FinalTest=FAIL & Test02=NULL
count(case when ResultType = 'OnceFail' then 1 else null end) as 'OnceFail',
--抄写2次或2次以上：Test01=FAIL & Test02=FinalTest=PASS
count(case when ResultType = 'NTF' then 1 else null end) as 'NTF',
--抄写2次或2次以上：Test01=Test02=FinalTest=FAIL
count(case when ResultType = 'TwiceFail' then 1 else null end) as 'TwiceFail',
--2次或2次以上：Test01=PASS & Test02=FinalTest=FAIL 
--或者 抄写3次及以上：Test01=Test02=PASS & FinalTest=FAIL
--或者 抄写3次及以上：Test01=FAIL & Test02=PASS & FinalTest=FAIL
count(case when ResultType = 'FinalFail' then 1 else null end) as 'FinalFail',
--2次或2次以上：Test01=Test02=FinalTest=PASS 
--或者 抄写3次及以上：Test01=FinalTest=PASS & Test02=FAIL
count(case when ResultType = 'FinalPass' then 1 else null end) as 'FinalPass',
--真实成功：Redo=0的SN.
count(case when FinalTest = 'PASS' and ReDo = '0' then 1 else null end) as 'RealPass',
--真实失败：Redo=0的SN.
count(case when FinalTest = 'FAIL' and ReDo = '0' then 1 else null end) as 'RealFail',
stuff((select ',' + SN from AMResult where Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour and ResultType='NTF' for XML PATH('')),1,1,'') as 'NTFSN',
stuff((select ',' + DSN from AMResult where Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour and FinalTest='FAIL' for XML PATH('')),1,1,'') as 'FailDSN' 
from  AMResult where  Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour


*/
select SN,ODF,FinalTest, ResultType, ReDo, Test01, Test02,DSN from AMResult 
where Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour