CREATE PROCEDURE spAuxilliary_Statistics

 @StartDate as varchar(10),
 @EndDate as varchar(10)

AS




Select max(type) Type,sum(Number) Number,sum(total) Total , sum(RFee)RFee from (
	Select  'OPD'as Type,sum(case When b.RevenueID = 'HC' then 1 else 0 end) Number,
	  sum(case When b.RevenueID = 'HC' then b.Amount else 0 end) Total,
	  sum(case When b.RevenueID = 'MD' then b.Amount else 0 end) RFee
	from BILLING..tbbillopdailyout b
	  left join PATIENT_DATA..tboutpatient p
	    on b.idnum = p.idnum
	  left join BUILD_FILE..tbcorevenuecode r
	    on b.revenueid = r.revenueid
	  left join BUILD_FILE..tbcootherrevenue o
	    on b.itemid = o.otherrevenueid
	where (r.AuxilliaryActive = 'Y') and B.revenueid in ('MD', 'hc') --(b.refnum like 'H%C')
	  and b.transdate between @startdate and @enddate + ' 23:59:59'
	group by  B.idnum , B.Refnum
	having sum(B.amount) > 0
)A
	
union all

Select Max(Type)Type,sum(Number) Number,sum(total) Total , sum(RFee)RFee from (
   
	select 'IN-PATIENT' Type,sum(case When b.RevenueID = 'HC' then 1 else 0 end) Number,
	  sum(case When b.RevenueID = 'HC' then b.Amount else 0  end) Total,
	  sum(case When b.RevenueID = 'MD' then b.Amount else 0  end) RFee
	from BILLING..tbbilldailybill b
	  left join PATIENT_DATA..tbpatient p
	    on b.idnum = p.idnum
	where  B.summarycode ='hc' and B.revenueid in ('MD', 'hc') 
	--(r.AuxilliaryActive = 'Y') and (b.refnum like 'H%C')
	  and b.transdate between @startdate and @enddate  + ' 23:59:59.99'
	group by  B.idnum , B.Refnum
	having sum(B.amount) > 0
	)a
	
union all

Select Max(Type)Type,sum(Number) Number,sum(total) Total , sum(RFee)RFee from (
	Select 	  'CASH' Type, max(Case when B.Idnum = 'CASH' then 1 else 0 end) Number,
	  sum(case When b.RevenueID = 'HC' then b.Amount else 0 end) Total,
	  sum(case When b.RevenueID = 'MD' then b.Amount else 0 end) RFee
	
	from BILLING..tbbillopdailyout b
	  left join PATIENT_DATA..tboutpatient p
	    on b.idnum = p.idnum
	  left join PATIENT_DATA..tbmaster m
	    on p.hospnum = m.hospnum
	  left join BILLING..tbcashormaster z
	    on b.refnum = z.refnum
	  left join BUILD_FILE..tbcorevenuecode r
	    on b.revenueid = r.revenueid
	  left join BUILD_FILE..tbcodoctor d
	    on b.itemid = d.doctorid
	  left join Build_File..tbcoDoctor E
	    on B.RequestDocId = E.DoctorID
	  left join BUILD_FILE..tbcootherrevenue o
	    on b.itemid = o.otherrevenueid
	  left outer join BILLING..tbCashORMaster Cash
		on B.RefNUm = Cash.RefNum	
	where (b.ChargeSlip like 'HC%')
	  and left(b.Refnum,2) = 'OR'	
	  and b.transdate between @startdate and @enddate + ' 23:59:59'
	
	group by B.Refnum)A


--set @StartDate ='12/01/2005'
--set @EndDate ='12/31/2005'

/*select 'In-Patient' Type ,sum(case Revenueid 
	when 'HC' then 1 else 0 end) Total, sum(amount) amount
	
from Billing..tbbilldailybill 
where summarycode ='hc' and revenueid in ('MD', 'hc')
and transdate between @StartDate and @EndDate + ' 23:59:59.99'

union all


select 'OPD' Type,sum(case Revenueid 
	when 'HC' then 1 else 0 end) Total, sum(amount) amount
from Billing..tbbillopdailyout 
where summarycode =  'hc' and IDnum <> 'CASH'
and transdate between @StartDate and @EndDate + ' 23:59:59.99'



union all

Select max(Type) 'CASH', sum(Total) Total, sum(Amount) Amount
From
	(select 'CASH' Type, max(case Revenueid 
		when 'HC' then 1 else 0 end)  Total,
		max(amount) amount
	from billing..tbbillopdailyout 
	where chargeSLIP LIKE 'HC%'  and Revenueid in ('HC', 'MD')
	 and transdate between @StartDate and @EndDate + ' 23:59:59.99'
	group by refnum
	) A
*/
/*
select 'CASH' Type, max(case Revenueid 
	when 'HC' then 1 else 0 end) Total, sum(amount) amount
from billing..tbbillopdailyout 
where chargeSLIP LIKE 'HC%'  and Revenueid in ('MD','HC')
 and transdate between @StartDate and @EndDate + ' 23:59:59.99'
--group by refnum
*/
GO