use patient_data
go


SET QUOTED_IDENTIFIER ON
go


DROP PROCEDURE [dbo].[test]
go


CREATE PROCEDURE [dbo].[test]
	@StartDate as varchar(10),
	@EndDate as varchar(10),
	@Type as varchar(1)

/*
set @StartDate = '06/15/2008'
set @EndDate = '06/18/2008'
set @Type = '3'
*/

as


if @Type = '1'
  begin
	Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate,
		case when isnumeric(A.AccountNum) = 1 then
			'PERSONAL' else C.Company end Account, A.AccountNum
	from Patient_Data..tbOutpatient A
	Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum
	Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum
	where IDNum in (Select IDNum from Medicare..tbMedActual 
		where cast(MedRoomBoard + MedDrug + MedOthers + MedOR as Decimal(12,2)) > 0) 
	and AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
	order by AdmDate
  end

else

if @Type = '2'
  begin
	Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate,
	C.Company as Account, A.AccountNum
	from Patient_Data..tbOutpatient A
	Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum
	Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum
	where isnumeric(A.AccountNum) = 0 and AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
	order by AdmDate
  end

else

if @Type = '3'
  begin
	Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate,
	'PERSONAL' as Account, A.AccountNum
	from Patient_Data..tbOutpatient A
	Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum
	where isnumeric(A.AccountNum) = 1 and AdmDate between @StartDate and @EndDate + ' 23:59:59.99'
	and A.IDNum in (Select IDNum from Billing..tbBillOPDailyOut where DRCR = 'D' group by IDNum)
	order by AdmDate
  end


go