SELECT
  AP.AttendanceTime,
  BR.BranchName AS ConsultantBranch,
  U1.FullName AS Consultant,
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  AP.AppointmentID,
  AT.AppointmentType,
  AP.AppointmentStage,
  U2.FullName AS BookedBy,
  AP.InvoiceNumber,
  (COALESCE(AP.ServiceValue, 0.0) + COALESCE(AA.ServiceValue, 0.0)) AS ServiceValue
FROM
  tbAppointments AS AP
    LEFT JOIN tbAppointmentTypes AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = AP.ClientID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = AP.ConsultantBranchID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = AP.ConsultantID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = AP.BookedByID)
    LEFT JOIN (

      SELECT
        B.AppointmentID,
        SUM(IF(B.AmountTypeID = 2, B.Amount, 0.0)) AS TravelTime,
        SUM(IF(B.AmountTypeID = 3, B.Amount, 0.0)) AS TravelDistance,
        SUM(B.Amount) AS ServiceValue
      FROM
        taAppointmentAmount AS B
          INNER JOIN tbAppointments AS AP ON (AP.AppointmentID = B.AppointmentID)
      WHERE
        (AP.AppointmentID IS NOT NULL) AND
        (AP.AppointmentStage = 'Appointment')
        @pProductID
        @pBranchID
        @pConsultantID
        @pClientID
        @pAppointmentStatus
        @pFromTo
      GROUP BY 1

    ) AS AA ON (AA.AppointmentID = AP.AppointmentID)
WHERE
  (AP.AppointmentID IS NOT NULL)
  @pProductID
  @pBranchID
  @pConsultantID
  @pClientID
  @pAppointmentStatus
  @pFromTo
ORDER BY
  BR.BranchName,
  LEFT(CB.BranchName, 30),
  AP.AttendanceDate
