SELECT
  AP.ClientID,
  AP.AppointmentID,
  AP.ProductID,
  AP.AttendanceDate,
  AP.ConsultantID,
  U1.FullName AS Consultant,
  U2.FullName AS BookedBy,
  CS.FinNumber,
  CS.GroupName AS ClientName,
  CB.BranchName AS ClientBranchName,
  BR.BranchRegion AS BranchRegion,
  B3.BranchRegion AS OnBehalfOfBranch,
  AT.ProductCategory AS ProductCategory,
  AT.AppointmentType,
  COALESCE(AB.SubAppointmentType, '') AS SubAppointmentType,
  AP.AppointmentStage,
  AP.EmployeeParticulars,
  AP.InvoiceNumber,
  ROUND((AP.TimeTravelled * AP.TimeTravelledRate) + COALESCE(AA.TravelTime, 0,0), 2) AS TravelTime,
  ROUND((AP.DistanceTravelled * AP.DistanceTravelledRate) + COALESCE(AA.TravelDistance, 0.0), 2) AS TravelDistance,
  ROUND((AP.TimeTravelled * AP.TimeTravelledRate) + COALESCE(AA.TravelTime, 0,0), 2) +
    ROUND((AP.DistanceTravelled * AP.DistanceTravelledRate) + COALESCE(AA.TravelDistance, 0.0), 2) AS TravelValue,
  AP.CoverUsed AS CoverAmount,
  AP.FreeTimeAmount,
  (AP.ServiceValue + COALESCE(AA.ServiceValue, 0.0)) AS ServiceValue,
  AT.IsService
FROM
  tbAppointments AS AP
    LEFT JOIN tbAppointmentTypes     AS AT ON (AT.AttendanceTypeID      = AP.AttendanceTypeID)
    LEFT JOIN taAppointmentSubType   AS AB ON (AB.SubTypeID             = AP.SubAttendanceType)
    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        = AP.ClientBranchID)
    LEFT JOIN taClients              AS CS ON (CS.ClientID              = AP.ClientID)
    LEFT JOIN taUsers                AS U1 ON (U1.UserID                = AP.ConsultantID)
    LEFT JOIN taUsers                AS U2 ON (U2.UserID                = AP.BookedByID)
    LEFT JOIN taBranches             AS BR ON (BR.BranchID = AP.ConsultantBranchID)
    LEFT JOIN taBranches             AS B3 ON (B3.BranchID = CB.LabourNetBranchID)
    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')
        @pAppointmentStatus
        @pFromTo
        @pProductID
        @pBranchID
        @pConsultantID
        @pClientID
      GROUP BY 1

    ) AS AA ON (AA.AppointmentID = AP.AppointmentID)
WHERE
  (AP.AppointmentID IS NOT NULL)
  @pAppointmentStatus
  @pFromTo
  @pProductID
  @pBranchID
  @pConsultantID
  @pClientID
