SELECT
  A.ConsultantID,
  U1.FullName AS Consultant
FROM
  tbAppointments AS A
    LEFT JOIN tbAppointmentTypes AS D ON (D.AttendanceTypeID = A.AttendanceTypeID)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = A.ClientBranchProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = A.ConsultantID)
WHERE
  (A.AttendanceDate BETWEEN @aFromDate AND @aToDate) AND
  (A.AppointmentStage = 'Authorised') AND
  (D.IsService = 0)
  @pProductID
  @pBranchID
  @pConsultantID
  @pTravelMode
  @pIncludeClients
  @pClientID

UNION

SELECT
  A.ConsultantID,
  C.FullName AS Consultant
FROM
  taPresentation A
    LEFT JOIN taProspect AS B ON (B.ProspectID = A.ProspectID)
    LEFT JOIN taUsers AS C ON (C.UserID = A.ConsultantID)
    LEFT JOIN taProspectProduct AS D ON (D.ProspectID = A.ProspectID),
  taUserBranchMember AS BM
WHERE
  (BM.UserID = A.ConsultantID) AND
  (BM.FromDate = (SELECT MAX(N.FromDate) FROM taUserBranchMember AS N WHERE (N.UserID = A.ConsultantID) AND (N.FromDate <= A.AttendanceDate))) AND
  (LEFT(AttendanceDate, 10) BETWEEN @aFromDate AND @aToDate) AND
  (A.PresentationStage = 'Closed')
  @pProspectBranchID
  @pSalesPersonID
  @pTravelMode
  @pIncludeProspects
  @pProspectID

ORDER BY 1