USE [LABORATORY]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_LabWithVerifiedResult]    Script Date: 9/23/2020 2:44:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_LabWithVerifiedResult] (@IdNum varchar(20),@HospNumber varchar(20) = null )  
RETURNS varchar(1) AS 
BEGIN 
	Declare @WithVerifiedResult varchar(1),
			@HospNum varchar(10)

	Set @IdNum = ltrim(rtrim(@idNum));
	Set @HospNum = ltrim(rtrim(@HospNumber));
	
	If exists (	Select B.RefNum from  tbLabLogbook B 
					Inner Join LABORATORY..tbLABMaster LabMaster 
						on B.RequestNum = LabMaster.RequestNum and IsNull(LabMaster.RequestStatus,'') <> 'R'
					left outer join Build_File..tbCoLabExam E on E.Labexamid = B.LabExamID
				where B.HospNum = @HospNum 
					and IsNull(B.VerifyId,'') <> '' 
					and IsNull(E.ResultConfidential,'N')= 'N')
		Set @WithVerifiedResult  = '1' 
	Else 
		Set @WithVerifiedResult  ='0'
		
Return @WithVerifiedResult
END