SELECT
  CP.Batch,
  CS.ClientID,
  CS.GroupName AS ClientName,
  CS.EmailAddress,
  CS.DateJoined,
  PR.ProductCategory,
  PR.ProductName,
  U1.FullName AS Consultant,
  U2.FullName AS SalesCloser,
  PF.StartDate,
  (PF.Difference / (PF.RetainerFee - PF.Difference) * 100.0) AS RetainerIncreasePercentage,
  (PF.RetainerFee - PF.Difference) AS OldRetainer,
  PF.Difference AS Increase,
  PF.RetainerFee AS NewRetainer
FROM
  taClientProductFees AS PF
    LEFT OUTER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = PF.ClientBranchProductID)
    LEFT OUTER JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT OUTER JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    LEFT OUTER JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT OUTER JOIN taUsers AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT OUTER JOIN taUsers AS U2 ON (U2.UserID = BP.SalesCloserID)
WHERE
  (PF.StartDate BETWEEN @aFromDate AND @aToDate) AND
  (PF.EntryType = 'A')
  @pBatch
  @pProductCategory
ORDER BY
  CP.Batch,
  CS.GroupName,
  PF.StartDate

