--CREATE PROCEDURE sp_HrtAuditReport
	
declare 
@StartDate varchar(10),
@EndDate varchar(10)

set @startdate = '08/28/2006'
set @Enddate = '08/28/2006' + ' 23:59:59.99'

SELECT DISTINCT tbBillDailyBill.RefNum As Refnum, 
    CASE 
	When tbBillDailyBill.DrCr = 'C' 
	Then (abs(tbBillDailyBill.Amount) * -1) 
	Else tbBillDailyBill.Amount 
    END As Amount, 

    	tbBillDailyBill.RevenueID As Account, 
	tbBillDailyBill.Quantity As Quantity, 
    	tbBillDailyBill.ItemID As ItemID, 
	(Case tbBillDailyBill.SummaryCode
		When 'EL' then tbCoDoctor.LastName + ', '+ tbCoDoctor.FirstName
		When 'HS' then tbCoDoctor.LastName + ', '+ tbCoDoctor.FirstName
		Else tbCoHsExam.HsExam
	 End) As HsExam, 
    	tbPasswordMain.initial As UserID,
	rtrim(tbMaster.Lastname) + ' ,' + rtrim(tbMaster.Firstname) + ' '+ rtrim(tbMaster.Middlename) As Patient,
    	tbBillDailyBill.TransDate As Transdate

FROM Billing..tbBillDailyBill  tbBillDailyBill
	LEFT OUTER JOIN Build_File..tbCoHsExam tbCoHsExam ON tbBillDailyBill.ItemID = tbCoHsExam.HsExamID
	LEFT OUTER JOIN Build_File..tbCoDoctor tbCoDoctor On tbBillDailyBill.Itemid =tbCoDoctor.DoctorID
            LEFT OUTER JOIN Password..tbPasswordMain tbPasswordMain ON tbBillDailyBill.UserID = tbPasswordMain.EmployeeID 
            LEFT OUTER JOIN (Patient_Data..tbpatient tbpatient Inner join Patient_Data..tbmaster tbmaster 
		On tbmaster.hospnum = tbpatient.hospnum) on tbpatient.idnum = tbbilldailybill.idnum
WHERE (tbBillDailyBill.RevenueID = 'hk' or tbBillDailyBill.SummaryCode = 'hk' )
 	  and CONVERT(VARCHAR(10),tbBillDailyBill.Transdate,101) between @StartDate and @EndDate + ' 23:59:59.99' 

UNION ALL

/*OutPatientBill*/

SELECT DISTINCT  tbBillOpDailyOut.RefNum As RefNum,  
     CASE
	when tbBillOpDailyOut.DrCr = 'C' 
	then (abs(tbBillOpDailyOut.Amount) * -1) 
	else tbBillOpDailyOut.Amount 
    END As Amount,

    tbBillOpDailyOut.RevenueID As Account,  
    tbBillOpDailyOut.Quantity As Quantity, 
    tbBillOpDailyOut.ItemID As ItemID, 
	tbCoHsExam.HsExam As HsExam, 
    tbPasswordMain.Initial, 
    	(case substring(tbBillOpDailyOut.refnum,1,2) 
		when 'OR' then tbCashOrMaster.PaymentFrom
		else rtrim(tbMaster.Lastname) + ' ,' + rtrim(tbMaster.Firstname) + ' '+ rtrim(tbMaster.Middlename) 
	end) As Patient,
     tbBillOpDailyOut.TransDate As Transdate
FROM  Billing..tbBillOpDailyOut tbBillOpDailyOut
	LEFT OUTER JOIN Build_File..tbCoHsExam tbCoHsExam ON tbBillOpDailyOut.ItemID = tbCoHsExam.HsExamID
	LEFT OUTER JOIN Build_File..tbCoDoctor tbCoDoctor on tbBillOpDailyOut.ItemID = tbCoDoctor.DoctorID
            LEFT OUTER JOIN Password..tbPasswordMain tbPasswordMain ON  tbBillOpDailyOut.UserID = tbPasswordMain.EmployeeID   
	LEFT OUTER JOIN (Patient_Data..tboutpatient a inner join Patient_Data..tbmaster tbmaster on tbmaster.hospnum = a.hospnum
	LEFT OUTER JOIN Build_File..tbCoCompany tbCoCompany on tbCoCompany.accountnum = a.accountnum) on a.idnum =  tbBillOpDailyOut.idnum              
          LEFT OUTER JOIN Billing..tbCashOrMaster tbCashOrMaster on tbCashOrMaster.RefNum = tbBillOpDailyOut.RefNum
WHERE  (tbBillOpDailyOut.RevenueID = 'Hk' or tbBillOpDailyOut.SummaryCode = 'hk')
 	  and CONVERT(VARCHAR(10),tbBillOpDailyOut.Transdate,101) between @StartDate and @EndDate  + ' 23:59:59.99'  --and Substring(tbBillOpDailyOut.Refnum,len(tbBillOpDailyOut.Refnum),1) = 'V'