CREATE PROCEDURE spAuxilliary_IncomeReport

@Mode as Varchar(1),
@startdate as Varchar(10),
@Enddate as Varchar(10)

 AS



--set @Mode ='o'
--set @startdate = '12/01/2005'
--set @Enddate = '12/31/2005' + ' 23:59:59.99'


If @Mode = 'O'
     Begin
	select max(b.transdate) TransDate, max(b.refnum) Refnum, max(b.idnum)Idnum, 
	  max(p.hospnum) HospNum, max(m.lastname + ', ' + m.firstname) [patient],
	  max(E.LastName + ', ' + E.FirstName) as RequestDoc ,
	  max(case  when b.revenueid = 'MD' then 'DR. ' + d.lastname
	    when isnull(b.itemid, '') = '' then r.itemname  end) Reader,
	  max(case  when b.revenueid = 'HC' then o.otherrevenue end) TestDone ,
	  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
	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
	
	
	
   End

if @Mode = 'I'
      Begin
	select max(b.transdate) TransDate, max(b.refnum) Refnum, max(b.idnum)Idnum, 
	  max(p.hospnum) HospNum, max(m.lastname + ', ' + m.firstname) [patient],
	  max(E.LastName + ', ' + E.FirstName) as RequestDoc ,
	  max(case  when b.revenueid = 'MD' then 'DR. ' + d.lastname
	    when isnull(b.itemid, '') = '' then r.itemname  end) Reader,
	  max(case  when b.revenueid = 'HC' then o.otherrevenue end) TestDone ,
	  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
	  left join PATIENT_DATA..tbmaster m
	    on p.hospnum = m.hospnum
	  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
	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
	
	
    End
GO