SELECT
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CP.ClientProductID,
  PR.ProductName,
  (SELECT COUNT(*) FROM taClientBranchProducts AS BP WHERE (BP.Status = 1) AND (BP.ClientProductID = CP.ClientProductID)) AS ClientBranchProductCount
FROM
  taClientProducts AS CP
    LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
WHERE
  (CP.Status = 1) AND
  (CP.TerminationDate IS NOT NULL) AND
  (CP.TerminationDate <> '0000-00-00') AND
  (CP.TerminationDate <= @aEffectiveDate) AND
  (CP.CancelApproveDateManager IS NOT NULL) AND
  (CP.CancelApproveDateManager <> '0000-00-00') AND
  (CP.CancelApproveDateExecutive IS NOT NULL) AND
  (CP.CancelApproveDateExecutive <> '0000-00-00')
ORDER BY
  CP.TerminationDate,
  CP.ClientProductID
