USE [MES]
GO

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

USE [MES]
GO

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

SET QUOTED_IDENTIFIER ON
GO
/*
TotalAll
TotalZero
OncePassAll
OncePassZero
NTFAll
NTFZero
RealFail
NTFSN
FailDSN
*/
CREATE FUNCTION [dbo].[HourlyResult](
	@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',
		--一次成功数量,含重流;
		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',
		--NTF总数数量,含重流;
		NTFAll = count(case when ResultType = 'NTF' then 1 else null end),-- as 'NTF',
		--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',
		--NTF的SN集合;
		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',
		--FinalTest为Fail的集合;
		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' 
		--查询条件：线体、工站、ODF、测试日期、整点
		from  AMResult where Line=@Line and Station=@Station and ODF=@ODF and TestDate=@TestDate and TestHour=@TestHour
	
GO