WITH

ttCurrentRetainerFee AS (
  SELECT
    M.ClientBranchProductID,
    MAX(M.ClientProductFeeID) AS ClientProductFeeID
  FROM
    taClientProductFees AS M
      INNER JOIN taClientBranchProducts AS BP USING (ClientBranchProductID)
  WHERE
    (BP.Status = 1) AND
    (M.StartDate = (
      SELECT MAX(N.StartDate)
      FROM taClientProductFees AS N
      WHERE
        (N.ClientBranchProductID = M.ClientBranchProductID) AND
        (N.StartDate <= @aToDate)
    ))
  GROUP BY 1
),

ttAutoIncreaseCount AS (
  SELECT
    PF.ClientBranchProductID,
    COUNT(*) AS AutoIncreaseCount
  FROM
    taClientProductFees AS PF
      INNER JOIN taClientBranchProducts AS BP USING (ClientBranchProductID)
  WHERE
    (PF.StartDate >= @aFromDate) AND
    (PF.EntryType = 'A')
  GROUP BY 1
)

SELECT
  CP.Batch,
  CP.ClientID,
  CS.GroupName AS ClientName,
  CS.EmailAddress,
  CS.DateJoined,
  PR.ProductCategory,
  PR.ProductName,
  U1.FullName AS Consultant,
  U2.FullName AS SalesCloser,
  CP.NextIncreaseDate,
  BP.ClientBranchProductID,
  PR.RetainerIncreasePercentage,
  PF.RetainerFee AS OldRetainer,
  (PF.RetainerFee * PR.RetainerIncreasePercentage / 100.0) AS Increase,
  (PF.RetainerFee * (100.0 + PR.RetainerIncreasePercentage) / 100.0) AS NewRetainer
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = BP.SalesCloserID)
    LEFT JOIN ttCurrentRetainerFee AS RF ON (RF.ClientBranchProductID = BP.ClientBranchProductID)
    LEFT JOIN taClientProductFees AS PF ON (PF.ClientProductFeeID = RF.ClientProductFeeID)
    LEFT JOIN ttAutoIncreaseCount AS AC ON (AC.ClientBranchProductID = BP.ClientBranchProductID)
WHERE
  (BP.Status IS NOT NULL) AND (BP.Status = 1) AND
  (CP.Status IS NOT NULL) AND (CP.Status = 1) AND
  (CS.ClientStatus IS NOT NULL) AND (CS.ClientStatus = 1) AND
  (PF.RetainerFee IS NOT NULL) AND (PF.RetainerFee > 0) AND
  ((AC.AutoIncreaseCount IS NULL) OR (AC.AutoIncreaseCount <= 0)) AND
  (CP.NextIncreaseDate BETWEEN @aFromDate AND @aToDate)
  @pBatch
  @pProductCategory
  @pClientID
ORDER BY
  CP.ClientID
