SELECT
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  (SELECT COUNT(*) FROM taClientBranches AS CB WHERE (CB.BranchStatus = 1) AND (CB.ClientID = CS.ClientID)) AS ClientBranchCount,
  (SELECT COUNT(*) FROM taClientProducts AS CP WHERE (CP.Status = 1) AND (CP.ClientID = CS.ClientID)) AS ClientProductCount
FROM
  taClients AS CS
WHERE
  (CS.ClientStatus = 1) AND
  (CS.TerminationDate IS NOT NULL) AND
  (CS.TerminationDate <> '0000-00-00') AND
  (CS.TerminationDate <= @aEffectiveDate)
ORDER BY
  CS.TerminationDate,
  CS.ClientID
