USE [MES]
GO

/****** Object:  UserDefinedFunction [dbo].[HourlyResultTR]    Script Date: 12/20/2021 18:35:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HourlyResultTR]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[HourlyResultTR]
GO

USE [MES]
GO

/****** Object:  UserDefinedFunction [dbo].[HourlyResultTR]    Script Date: 12/20/2021 18:35:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
Total
OncePass
NTF
RealFail
*/
CREATE FUNCTION [dbo].[HourlyResultTR](
	@Line varchar(30),
	@Station varchar(30),
	@ODF varchar(30),
	@TestDate DATE,
	@TestHour INT)
RETURNS TABLE as
	RETURN select 
		--抄写总数(包含重流和非重流的)
		TotalAll = count(1),-- as 'Total',
		TotalZero = count(case when ReDo = '0' then 1 else null end),-- as 'Total',
		--有且只有一次测试且成功 Test01=FinalTest=PASS & Test02=NULL
		OncePassAll = count(case when ResultType = 'OncePass' then 1 else null end),-- as 'OncePass',
		OncePassZero = count(case when ResultType = 'OncePass' and ReDo = '0' then 1 else null end),-- as 'OncePass',
		--抄写2次或2次以上：Test01=FAIL & Test02=FinalTest=PASS
		NTFAll = count(case when ResultType = 'NTF' then 1 else null end),-- as 'NTF',
		NTFZero = count(case when ResultType = 'NTF' and ReDo = '0' then 1 else null end),-- as 'NTF',
		--含重码.
		RealFail = count(case when ResultType = 'RealFail' then 1 else null end),-- as 'RealFail',
		NTFSN = stuff((select ',' + SN from AMResult_TR 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_TR 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_TR where Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour
	
GO