Select EVSMaster.*
From EVSMaster
Join Client
On (EVSMaster.Client_ID = Client.Client_ID)
Join ClientProgram
On (EVSMaster.Client_ID = ClientProgram.Client_ID)
Left Outer Join ProgramType
On (ClientProgram.ProgramType_Key = ProgramType.ProgramType_Key)
Left Outer Join ProgramLocation
On (ClientProgram.Location_Key = ProgramLocation.Location_Key)
Where EVSMaster_ID in
(Select X.EVSMaster_ID From
(Select EVSDetail.EVSMaster_ID,EVSDetail.Change, EVSDetail.TypeOfCoverage,
        Count(TypeOfCoverage) As RecordCount
  From EVSDetail
  where (EVSDetail.EVSMaster_ID In
   (Select  EVSDetail.EVSMaster_ID
    From EVSDates
    Join EVSMaster
    On  (EVSMaster.Batch_ID = EVSDates.Batch_ID)
    Join EVSDetail
    On  (EVSMaster.EVSMaster_ID = EVSDetail.EVSMaster_ID)
    and (EVSDetail.Change ='Loss')
    and (EVSDetail.TypeOfCoverage = 'MC')
    Join Client
    On  (EVSMaster.Client_ID = Client.Client_ID)
    Join ClientProgram
    On (Client.Client_ID = ClientProgram.Client_ID)
    and ((EVSDates.ServiceDateEnd - ClientProgram.AdmissionDate) DAY > 1)
    and ((ClientProgram.DischargeDate IS Null)
    or  ((ClientProgram.DischargeDate > EVSDates.ServiceDateStart)
    and ((EVSDates.ServiceDateEnd-ClientProgram.DischargeDate) MONTH between 0 and 1) ) )
    Where ((EVSDates.ServiceDateStart = :ServiceDateStart)
    and   (EVSDates.Current=True)
    and   (ClientProgram.ProgramType_Key = :ProgramType_Key)
    and   (ClientProgram.Location_Key = :Location_Key) )))
    and   (EVSDetail.TypeOfCoverage = 'MC')
    Group By EVSMaster_ID
    Order By EVSMaster_ID) X
Where (X.RecordCount = 1)
and   (X.Change = 'Loss')
Group By X.EVSMaster_ID
Order By X.EVSMaster_ID)
Group By EVSMaster.EVSMaster_ID, ClientProgram.ProgramType_Key,Client_ID