USE [MES]
GO
/****** Object:  UserDefinedFunction [dbo].[GET_HOURRESULT]    Script Date: 12/14/2021 10:28:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GET_HOURRESULT](
	@Line varchar(30),
	@Station varchar(30),
	@ODF varchar(30),
	@TestDate DATE,
	@TestHour INT)
RETURNS TABLE as
	RETURN select 
		--抄写总数(包含重流和非重流的)
		Total = count(1),-- as 'Total',
		--实际数量：非重流的,即ReDo=0
		RealTotal = count(case when ReDo = '0' then 1 else null end),-- as 'RealTotal', 
		--有且只有一次测试且成功 Test01=FinalTest=PASS & Test02=NULL
		OncePass = count(case when ResultType = 'OncePass' then 1 else null end),-- as 'OncePass',
		--有且只有一次测试且失败 Test01=FinalTest=FAIL & Test02=NULL
		OnceFail = count(case when ResultType = 'OnceFail' then 1 else null end),-- as 'OnceFail',
		--抄写2次或2次以上：Test01=FAIL & Test02=FinalTest=PASS
		NTF = count(case when ResultType = 'NTF' then 1 else null end),-- as 'NTF',
		--抄写2次或2次以上：Test01=Test02=FinalTest=FAIL
		TwiceFail = 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
		FinalFail = count(case when ResultType = 'FinalFail' then 1 else null end),-- as 'FinalFail',
		--2次或2次以上：Test01=Test02=FinalTest=PASS 
		--或者 抄写3次及以上：Test01=FinalTest=PASS & Test02=FAIL
		FinalPass = count(case when ResultType = 'FinalPass' then 1 else null end),-- as 'FinalPass',
		--真实成功：Redo=0的SN.
		RealPass = count(case when FinalTest = 'PASS' and ReDo = '0' then 1 else null end),-- as 'RealPass',
		--真实失败：Redo=0的SN.
		RealFail = count(case when FinalTest = 'FAIL' and ReDo = '0' then 1 else null end),-- as 'RealFail',
		NTFSN = 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',
		FailDSN = 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
	
	--HourlyResultTR