SELECT
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CB.ClientBranchID,
  CB.BranchName,
  COALESCE(CB.TerminationDate,   CS.TerminationDate) AS TerminationDate,
  COALESCE(CB.TerminationReason, CS.TerminationReason) AS TerminationReason,
  COALESCE(CB.TerminationNotes,  CS.TerminationNotes) AS TerminationNotes,
  ( (SELECT COUNT(*) FROM taClientProducts AS CP LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientProductID = CP.ClientProductID) WHERE (CP.Status = 1) AND (BP.ClientBranchID = CB.ClientBranchID))
    +
    (SELECT COUNT(*) FROM taClientBranchProducts AS BP WHERE (BP.Status = 1) AND (BP.ClientBranchID = CB.ClientBranchID))
  ) AS ClientBranchProductCount
FROM
  taClientBranches AS CB
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
WHERE
  (CB.BranchStatus = 1) AND (
    (
      (CB.TerminationDate IS NOT NULL) AND
      (CB.TerminationDate <> '0000-00-00') AND
      (CB.TerminationDate <= @aEffectiveDate)
    ) OR (
      (CS.ClientStatus = 1) AND
      (CS.TerminationDate IS NOT NULL) AND
      (CS.TerminationDate <> '0000-00-00') AND
      (CS.TerminationDate <= @aEffectiveDate)
    )
  )
ORDER BY
  CB.ClientBranchID
