SELECT

  -- Appointment Details
  AP.AppointmentID,
  AP.ConsultantID,
  AP.CalendarID,
  AP.AttendanceDate,
  SUBSTR(AP.AttendanceTime, 12, 8) AS AttendanceTime,
  AttendanceTime AS SubjectName,
  AP.Duration,
  AP.EmployeeParticulars,
  CB.MapReference,
  AP.ClientID AS ClientID,
  AP.CancelledByReason,
  AP.CancelledByFullname,
  AP.OutlookEventWebLink AS OutlookLink,

  -- Client Details
  CS.FinNumber,
  CS.GroupName AS ClientName,

  -- Participants
  ( SELECT GROUP_CONCAT(DISTINCT CONCAT(TRIM(CONCAT(TRIM(C1.Title), ' ', TRIM(CONCAT(TRIM(C1.FirstName), ' ', TRIM(C1.Surname))))), ' [', C1.Position, '] (', C1.Email, ')') SEPARATOR '<br />')
    FROM taClientContacts AS C1
    WHERE
      (C1.TypeID = AP.AppointmentID) AND
      (C1.ContactType = 'Appointment')
  ) AS Participants,

  -- Branch Manager
  BR.BranchManagerID,

  -- Regional Product Managers
  NULL AS RegionalProductManagerID,

  -- Product Executives
  NULL AS ProductExecutiveID,

  -- Allocated Consultants
  AP.ConsultantID,

  -- Sales People
  NULL AS SalesPersonID,

  -- Branch Administrators
  ( SELECT GROUP_CONCAT(DISTINCT ConsultantID)
    FROM taBranchStructure
    WHERE
      (BranchID = AP.ConsultantBranchID) AND
      (Description LIKE '%Branch Administrator%')
  ) AS BranchAdministratorID,

  -- Debtor Clerk
  NULL AS DebtorClerkID
FROM
  tbAppointments AS AP
    LEFT JOIN taClients AS CS ON (CS.ClientID = AP.ClientID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = AP.ClientBranchID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = AP.ConsultantBranchID)
WHERE
  (AP.AppointmentID = @nAppointmentID)
