WITH

ttAppointmentAudit AS (
  SELECT A.audKey AS AppointmentID, A.audOperator FROM
  tbAudit AS A
    INNER JOIN (
      SELECT A.audKey, MAX(audID) AS audID FROM tbAudit AS A
      WHERE (A.audTable = 'tbAppointments') AND (A.audField = 'ClientSatisfactionRating') AND
        (DATE(A.audDate) BETWEEN @aFromDate AND @aToDate)
      GROUP BY 1
    ) AS B ON (B.audID = A.audID)
)

SELECT
  AP.ClientID,
  CS.FinNumber,
  SM.National,
  CS.GroupName,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  CS.DateJoined,
  AP.AppointmentID,
  SM.LabourNetBranch AS Regions,
  CONCAT(PR.ProductCategory, ' - ', PR.ProductName) AS Product,
  U1.FullName AS Consultant,
  Z.audOperator AS SubmittedBy,
  AP.ProductID,
  AP.BillingDate,
  AP.ClientSatisfactionComment,
  IF((AP.ClientSatisfactionRating IS NULL) OR (AP.ClientSatisfactionRating = 0), 'Not Selected', IF(AP.ClientSatisfactionRating = 1, 'Green', 'Red')) AS ClientSatisfactionRating,
  IF(AP.ClientHasComplained IS NULL, 'no', IF(AP.ClientHasComplained = 1, 'Yes', 'no')) AS ClientComplaint,
  IF(AP.IsCancelImminent IS NULL, 'no', IF(AP.IsCancelImminent = 1, 'Yes', 'no')) AS IsCancelImminent,
  fnClientProductRetainer(CP.ClientProductID, NOW()) AS Retainer
FROM
  tbAppointments AS AP
    INNER JOIN ttAppointmentAudit Z ON (Z.AppointmentID = AP.AppointmentID)
    INNER JOIN taClients AS CS ON (CS.ClientID = AP.ClientID)
    INNER JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = CS.IndustryID))
    LEFT JOIN taSystemOption AS S2 ON ((S2.OptionType = 'SETA') AND (S2.OptionID = CS.SetaID))
    LEFT JOIN taProducts AS PR ON (PR.ProductID = AP.ProductID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = AP.ConsultantID)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
WHERE
  (AP.ClientSatisfactionRating IS NOT NULL) AND
  (AP.ClientSatisfactionRating > 0)
  @pProductCategory
  @pBranchRegion
ORDER BY
  AP.ClientID,
  AP.AppointmentID
