USE [MEDICARE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--declare



Create PROCEDURE [dbo].[MedicRep_DailyMedicareReceivable] 

   @strStartDate as varchar(10),
   @strEndDate as varchar(10)


AS

--set @strStartDate = '09/01/2013'
--set @strEndDate = '09/01/2013'


  Select IsNull(A.IdNum,'') [IdNum],
       A.DcrDate [DcrDate],
       Convert(varchar(10),A.DcrDate,101) [DischargeDate],
       C.ComputedDate,
       IsNull(B.LastName,'') + ', ' +
       IsNull(B.FirstName,'') + ' ' +
       IsNull(B.MiddleName,'') [PatientName],
       IsNull(C.MedRoomBoard,0) [ActualRB],
       IsNull(C.MedDrug,0) [ActualDrug],
       IsNull(C.MedOthers,0) [ActualOthers],
       IsNull(C.MedOR,0) [ActualOR],
       IsNull(D.MedRoomBoard,0) [ClaimRB],
       IsNull(D.MedDrug,0) [ClaimDrug],
       IsNull(D.MedOthers,0) [ClaimOthers],
       IsNull(D.MedOR,0) [ClaimOR]
  from Patient_Data..tbPatient A Inner Join Patient_Data..tbMaster B
  On A.HospNum = B.HospNum Inner Join Medicare..tbMedActual C
  On A.IdNum = C.IdNum Left Outer Join Medicare..tbMedClaim D
  On A.IdNum = D.IdNum
  Where (C.ComputedDate Between @strStartDate and @strEndDate + ' 23:59:59') and
        not C.IdNum is null

union all

  Select IsNull(A.IdNum,'') [IdNum],
       A.DcrDate [DcrDate],
       Convert(varchar(10),A.DcrDate,101) [DischargeDate],
       C.ComputedDate,
       IsNull(B.LastName,'') + ', ' +
       IsNull(B.FirstName,'') + ' ' +
       IsNull(B.MiddleName,'') [PatientName],
       IsNull(C.MedRoomBoard,0) [ActualRB],
       IsNull(C.MedDrug,0) [ActualDrug],
       IsNull(C.MedOthers,0) [ActualOthers],
       IsNull(C.MedOR,0) [ActualOR],
       IsNull(D.MedRoomBoard,0) [ClaimRB],
       IsNull(D.MedDrug,0) [ClaimDrug],
       IsNull(D.MedOthers,0) [ClaimOthers],
       IsNull(D.MedOR,0) [ClaimOR]
  from Patient_Data..tbOutPatient A Inner Join Patient_Data..tbMaster B
  On A.HospNum = B.HospNum Inner Join Medicare..tbMedActual C
  On A.IdNum = C.IdNum Left Outer Join Medicare..tbMedClaim D
  On A.IdNum = D.IdNum

  Where (C.ComputedDate Between @strStartDate and @strEndDate + ' 23:59:59') and
        not C.IdNum is null
  Order by A.DcrDate, [PatientName];