USE [PATIENT_DATA]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

Create FUNCTION [dbo].[fn_GetPrevFinalDiagnosis] (@HospNum as varchar(10),@IDnum as varchar(15),@Admdate varchar(10))  
RETURNS varchar(1000)
AS  
BEGIN 
  Declare @PrevFinalDiagnosis as varchar(1000)
        Set @PrevFinalDiagnosis =
             (  Select top 1  b.FinalDiagnosis
				--Select top 1  a.HospNum,a.Idnum,a.AdmDate,a.DcrDate
				From Patient_Data..tbpatient a
				Left Join PATIENT_DATA..tbpatientHistory b on a.idnum = b.IDNum
				Where a.HospNum = @Hospnum and a.IdNum <> @IDnum and admdate < @Admdate
				Order By a.AdmDate desc
			  )

     Return(@PrevFinalDiagnosis)
END
GO

CREATE FUNCTION [dbo].[fn_GetPrevAdmdate] (@HospNum as varchar(10),@IDnum as varchar(15),@Admdate varchar(10))  
RETURNS datetime
AS  
BEGIN 
  Declare @PrevAdmdate as datetime
        Set @PrevAdmdate =
             (  Select top 1  a.AdmDate
				--Select top 1  a.HospNum,a.Idnum,a.AdmDate,a.DcrDate
				From Patient_Data..tbpatient a
				Where a.HospNum = @Hospnum and a.IdNum <> @IDnum and admdate < @Admdate
				Order By a.AdmDate desc
			  )

     Return(@PrevAdmdate)
END
GO

CREATE FUNCTION [dbo].[fn_GetICDCode] (@Idnum as varchar(15))

RETURNS Varchar(200)  AS  
BEGIN
Declare @ICDCode as varchar(200)
Set @ICDCode = ''

	select @ICDCode = (Case When @ICDCode = '' Then
			     DiagnosisID Else @ICDCode + ', ' + DiagnosisID End)
	       From Patient_Data..tbPatientDiagnosis 
	       Where IDNUm = @Idnum;

	RETURN(@ICDCode)
END
GO