USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[LAB_ResultEditing_GetPatientExams]    Script Date: 11/29/2017 13:36:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LAB_ResultEditing_GetPatientExams] 
	@HospNum varchar(10),
	@Refnum varchar(20)=''
AS

if len(rtrim(ltrim(@Refnum))) = 0
	begin
		Select 
			LabExamID as Code, 
			tbCoLabValues.ChargingName [Examination],
			TransDate as [Date Requested], 
			VerifyDate as [Date Verified],
			  IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,''))
							from Password..tbPasswordMain Where EmployeeID = tbLabLogbook.VerifyID
							Group by EmployeeID ), '' ) [Verified By],
			RequestNum as [Lab. #]
		From tbLABLogBook
			Left Join Password..tbPasswordMaster tbPasswordMaster
			   on tbLabLogBook.VerifyID = tbPasswordMaster.EmployeeID
		   Left Join Build_File..tbCoLabValues tbCoLabValues
			  on tbLabLogBook.LabExamID = tbCoLabValues.Code
		Where HospNum = @HospNum
		Order By ResultDate desc
	end
else
	begin
		Select 
			LabExamID as Code, 
			tbCoLabValues.ChargingName [Examination],
			TransDate as [Date Requested], 
			VerifyDate as [Date Verified],
			  IsNull( ( Select Max(IsNull(LastName,'') + ', ' + IsNull(FirstName,'') + ' ' + IsNull(MiddleName,''))
							from Password..tbPasswordMain Where EmployeeID = tbLabLogbook.VerifyID
							Group by EmployeeID ), '' ) [Verified By],
			RequestNum as [Lab. #]
		From tbLABLogBook
			Left Join Password..tbPasswordMaster tbPasswordMaster
			   on tbLabLogBook.VerifyID = tbPasswordMaster.EmployeeID
		   Left Join Build_File..tbCoLabValues tbCoLabValues
			  on tbLabLogBook.LabExamID = tbCoLabValues.Code
		Where HospNum = @HospNum and Refnum = @Refnum
		Order By ResultDate desc
	end