USE [Station]
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Nurse_GetPatient_LabResult_NormalValues')
EXEC ('CREATE PROC dbo.sp_Nurse_GetPatient_LabResult_NormalValues AS SELECT 1')
GO


/****** Object:  StoredProcedure [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues]    Script Date: 04/18/2012 17:42:26 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[sp_Nurse_GetPatient_LabResult_NormalValues]
@IDNum as Varchar(10)

 AS
DECLARE @OpdIDNum as Varchar(10);

Select @OpdIDNum = Isnull(OPDIDNum,'') from Patient_data..tbPatient
		Where IDNum = @IDnum;

select 	
	case 
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P') then c.ItemID
		else a.labexamID
	end as labexamid,
	a.labsectionid ,
	case a.labexamid
		when '1' then d.labsection + ' (CBC)'
		else d.labsection
	end  as section ,
	case 
		when a.labexamid = '1' then ltrim(convert(varchar(30),e.shortname) + ' ' + convert(varchar(35),e.resultname))
		when a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P' and labexamID <> '1') then g.labexam
		else b.labexam
		end as Exam ,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.minvalue as decimal(10,2))) + ' - ' +	convert(varchar(10),cast(c.maxvalue as decimal(10,2))) + ' ' + c.unit 
		when '7' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when '0' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'U' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		when 'P' then isnull(c.StrNValues,'') --+ ' ' + isnull(c.Unit,'')
		else 'No Normal Value'
		END
		as NormalValue,
	case a.formtype
		when '1' then convert(varchar(10),cast(c.result as decimal(10,2)))
		when '7' then isnull(c.Strresult,'')
		when '0' then isnull(c.Strresult,'')
		when 'U' then isnull(c.Strresult,c.result)
		when 'P' then isnull(c.Strresult,c.result)
		else 'No Result' 
		END
		 as Result ,
	--convert(varchar(10),Isnull(a.Verifydate,a.ResultDate),101) + substring(convert(varchar(19),Isnull(a.Verifydate,a.ResultDate),100),12,8) as ResultDate,
	Isnull(a.Verifydate,a.ResultDate) as ResultDate,
	a.formtype as FormType

from laboratory..tblablogbook a
left outer join build_file..tbcolabsection d
	on a.labsectionid = d.labsectionid
left outer join build_file..tbcolabexam b
	on a.labexamid = b.labexamid
left outer join laboratory..tblabresultnvalues c
	on a.requestnum = c.requestnum --and a.labexamid = c.itemid
left outer join build_file..tbcolabvalues e 
	on e.code = c.itemid
left outer join build_file..tbcolabexam g
	on c.Itemid = g.labexamid
where (a.IDnum=@IDNum or a.IDNum = @OPDIDNum) and (a.labsectionid='H' or a.labexamID IN (Select LabExamID from Build_File..tbCoLabExam where Form='P')) and (a.formtype IN ('1','0','7','U','P')) 
	and(isnumeric(c.Strresult) = 1 )	

order by section,Exam,Isnull(a.Verifydate,a.ResultDate);

GO