USE [PATIENT_DATA]


Alter table patient_data..tbadmittingsetup add
isAllowDischargeBlocklisted bit not null default(0)


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[sp_Adm_MasterPatientSubSearch]
  @LastName as VarChar(50),
  @Sex As VarChar(1),
  @BirthDate As VarChar(10)
AS

Declare @BirthMonth as varchar(2),
		@BirthDay as varchar(2),
		@BirthYear as varchar(4),
		@LastNameF as varchar(50)

set @BirthMonth = month(@BirthDate)
set @BirthDay = day(@BirthDate)
set @Birthyear = year(@BirthDate)
set @LastNameF = left(@LastName,3)


    BEGIN
	SELECT Distinct Master.LastName AS [Last Name], 
		Master.FirstName AS [First Name], 
		Master.MiddleName AS [Middle Name], 
		Master.HospNum AS [Hospital #], 
		convert(varchar(10),Master.BirthDate,101) AS [Birth Date], 	
		Master.Age,
		RTRIM( ISNULL( Master.HouseStreet ,'' ) ) + ' ' + RTRIM( ISNULL( Master.Barangay,'' ) ) + ', ' + 
			RTRIM( ISNULL(Address.Town,'' ) ) + ' ' + RTRIM( ISNULL( Address.Province,'') ) AS Address,
		Master.AccountNum AS [Account #]
	FROM	tbMaster AS Master LEFT OUTER JOIN Build_File..tbCoAddress AS Address ON Master.ZipCode = Address.ZipCode
	WHERE Master.LastName Like '%' + @LastNameF + '%' AND
--		Master.Sex Like @Sex + '%' AND
		(month(Master.Birthdate) = @BirthMonth  and day(Master.Birthdate) = @BirthDay) 
--	ORDER BY Master.LastName, Master.FirstName, Master.MiddleName

	UNION ALL

	SELECT Distinct Master.LastName AS [Last Name], 
		Master.FirstName AS [First Name], 
		Master.MiddleName AS [Middle Name], 
		Master.HospNum AS [Hospital #], 
		convert(varchar(10),Master.BirthDate,101) AS [Birth Date], 	
		Master.Age,
		RTRIM( ISNULL( Master.HouseStreet ,'' ) ) + ' ' + RTRIM( ISNULL( Master.Barangay,'' ) ) + ', ' + 
			RTRIM( ISNULL(Address.Town,'' ) ) + ' ' + RTRIM( ISNULL( Address.Province,'') ) AS Address,
		Master.AccountNum AS [Account #]
	FROM	tbMaster AS Master LEFT OUTER JOIN Build_File..tbCoAddress AS Address ON Master.ZipCode = Address.ZipCode
	WHERE Master.LastName Like '%' + @LastNameF + '%' AND
--		Master.Sex Like @Sex + '%' AND
		(month(Master.Birthdate) = @BirthMonth  and year(Master.Birthdate) = @BirthYear)

	UNION ALL

	SELECT Distinct Master.LastName AS [Last Name], 
		Master.FirstName AS [First Name], 
		Master.MiddleName AS [Middle Name], 
		Master.HospNum AS [Hospital #], 
		convert(varchar(10),Master.BirthDate,101) AS [Birth Date], 	
		Master.Age,
		RTRIM( ISNULL( Master.HouseStreet ,'' ) ) + ' ' + RTRIM( ISNULL( Master.Barangay,'' ) ) + ', ' + 
			RTRIM( ISNULL(Address.Town,'' ) ) + ' ' + RTRIM( ISNULL( Address.Province,'') ) AS Address,
		Master.AccountNum AS [Account #]
	FROM	tbMaster AS Master LEFT OUTER JOIN Build_File..tbCoAddress AS Address ON Master.ZipCode = Address.ZipCode
	WHERE Master.LastName Like '%' + @LastNameF + '%' AND
--		Master.Sex Like @Sex + '%' AND
		(day(Master.Birthdate) = @BirthDay  and year(Master.Birthdate) = @BirthYear)  
	ORDER BY Master.LastName, Master.FirstName, Master.MiddleName

    END