SELECT
  X.AppointmentType,
  X.ConsultantID,
  X.Consultant,
  X.ClientID,
  X.ClientName,
  X.AppointmentID,
  X.ProductCategory,
  X.AttendanceType,
  LEFT(X.CreateDateTime, 16) AS CreateDateTime,
  LEFT(X.AttendanceDate, 16) AS CalendarDateTime,
  TIMESTAMPDIFF(MINUTE, X.CreateDateTime, X.AttendanceDate) AS GapMinutes,
  '' AS Gap
FROM (

  SELECT
    AP.AppointmentID,
    'Client' AS AppointmentType,
    AP.ConsultantID,
    U1.FullName AS Consultant,
    AP.ClientID,
    CB.BranchName AS ClientName,
    AT.ProductCategory AS ProductCategory,
    AT.AppointmentType AS AttendanceType,
    AP.CreateDateTime,
    CONCAT(AP.AttendanceDate, ' ', RIGHT(AP.AttendanceTime, 8)) AS AttendanceDate
  FROM
    tbAppointments AS AP
      LEFT JOIN taUsers AS U1 ON (U1.UserID = AP.ConsultantID)
      LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = AP.ClientBranchID)
      LEFT JOIN tbAppointmentTypes AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
  WHERE
    (AP.AttendanceDate BETWEEN @aFromDate AND @aToDate) AND
    (AP.AppointmentStage = 'Authorised') AND
    (AP.CreateDateTime IS NOT NULL) AND
    (AT.LeadTimeApplies = 1)
    @pProductID
    @pBranchID
    @pConsultantID
    @pIncludeClients
    @pClientID

  UNION

  SELECT
    PP.PresentationID AS AppointmentID,
    'Prospect' AS AppointmentType,
    PP.ConsultantID,
    U1.FullName AS Consultant,
    PR.ProspectID AS ClientID,
    PR.ProspectName AS ClientName,
    (SELECT GROUP_CONCAT(DISTINCT A.ProductCategory ORDER BY A.ProductCategory ASC SEPARATOR ' + ')
      FROM taProspectProduct AS A WHERE A.ProspectID = PR.ProspectID) AS ProductCategory,
    'Presentation' AS AttendanceType,
    PP.CreateDateTime,
    CONCAT(PP.AttendanceDate, ' ', RIGHT(PP.AttendanceTime, 8)) AS AttendanceDate
  FROM
    taPresentation AS PP
      LEFT JOIN taProspect AS PR ON (PR.ProspectID = PP.ProspectID)
      LEFT JOIN taUsers AS U1 ON (U1.UserID = PP.ConsultantID),
    taUserBranchMember AS BM
      LEFT JOIN taBranches AS BR ON (BR.BranchID = BM.BranchID)
  WHERE
    (BM.UserID = PP.ConsultantID) AND
    (BM.FromDate = (SELECT MAX(N.FromDate) FROM taUserBranchMember AS N WHERE (N.UserID = PP.ConsultantID) AND (N.FromDate <= PP.AttendanceDate))) AND
    (PP.AttendanceDate BETWEEN @aFromDate AND @aToDate) AND
    (PP.PresentationStage = 'Closed') AND
    (PP.CreateDateTime IS NOT NULL)
    @pProspectProductID
    @pProspectBranchID
    @pSalesPersonID
    @pIncludeProspects
    @pProspectID

) AS X

ORDER BY
  X.AttendanceDate
