USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_View_PatientRequests]    Script Date: 9/23/2020 3:00:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_View_PatientRequests]
	@IdNum varchar(20),
	@HospNumber varchar(20) = null
AS
SET NOCOUNT ON;

Declare @HospNum varchar(20)

Set @HospNum = @HospNumber


Select distinct 
	IsNull(M.RefNum,'') as RefNum, 
	IsNull(M.IdNum,'') as IdNum, 
	M.TransDate as TransDate,
	
	case when IsNumeric(L.HospNum) = 1 Then
			case when L.Idnum like 'T%' Then ''
				 when L.IdNum like '%B' Then convert(varchar(10), O.AdmDate, 101) + ' ' + convert(varchar(10), O.AdmDate, 108)
			Else convert(varchar(10), P.AdmDate, 101) + ' ' + convert(varchar(10), P.AdmDate, 108) 
			End
	Else ''
	End as AdmDate
From tbLabLogbook L
	Inner Join tbLabMaster M on L.requestNum = M.RequestNum and IsNull(M.RequestStatus,'') <> 'R'
	left Outer join Patient_Data..tbPatient P on P.IdNum = L.IdNum
	left outer join Patient_Data..tbOutPatient O on O.IdNum = L.IdNum
where L.HospNum = @HospNum and IsNull(L.VerifyDate,'') <> ''
order by M.TransDate Desc