USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_View_PatientExams_PerHospNum]    Script Date: 9/23/2020 3:03:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_View_PatientExams_PerHospNum]
   @HospNum VARCHAR(50)
AS
SET NOCOUNT ON;
  
  SELECT	L.IDNum [ID Number], 
            L.LabSectionID [SectionID],
            S.LabSection [SectionName],
            E.LabExam [LabExam],
			E.ShortName [ShortName], 
            L.RequestNum,
            L.ResultDate [ResultDate],
            L.TransDate [RequestDate],
			L.HospNum,             
            L.FormType,
            L.LabExamID, 
            L.PatientType,
			L.RoomID,
			(ISNULL(D.LastName,'') + ' ' + ISNULL(D.FirstName,'')) AS doctor,
			ISNULL(dbo.fn_LabGetMedTechUser(L.UserID),'') AS [MedTech],
            ISNULL(dbo.fn_LabGetMedTechUser(L.VerifyID),'') AS [Verified BY],        
			ISNULL(V.ValueType,'') AS ValueType,
			ISNULL(Sp.SpecimenId,'') AS SpecimenId,
			ISNULL(Sp.Specimen, '') AS Specimen,
			CONVERT(VARCHAR(10), ISNULL(L.VerifyDate,''), 101) + ' ' + CONVERT(VARCHAR(10), ISNULL(L.VerifyDate,''), 108) AS VerifyDate,
			ISNULL(L.AccessionNum,'') AccessionNum,
			CASE WHEN ISNULL(L.ResultPrinted,0) IN ('1','Y') THEN 'Y'
				ELSE 'N' END AS ResultPrinted,
			L.RefNum
FROM laboratory..tbLabLogbook L 
	INNER JOIN LABORATORY..tbLABMaster LabMaster 
		ON L.RequestNum = LabMaster.RequestNum AND ISNULL(LabMaster.RequestStatus,'') <> 'R'
	LEFT OUTER JOIN build_file..tbCoLabSection S ON L.LabSectionID = S.LabSectionID
	LEFT OUTER JOIN build_file..tbCoLabExam E ON L.LabExamID = E.LabExamID
	LEFT OUTER JOIN build_file..tbCoDoctor D ON L.doctorid = D.DoctorID
	LEFT OUTER JOIN build_file..tbCoMedTech M ON L.medtechid = M.medtechid
	LEFT OUTER JOIN build_file..tbCoLabValues V ON L.LabExamID = V.Code
	LEFT OUTER JOIN build_file..tbCoLabSpecimen Sp ON Sp.SpecimenId = L.SpecimenID
WHERE (L.VerifyDate IS NOT NULL OR L.VerifyDate <> '')
       AND L.HospNum = @HospNum AND ISNULL(E.ResultConfidential,'') = 'N'
ORDER BY CAST(VerifyDate AS DATETIME) DESC