SELECT
  CS.ClientID,
  CS.FinNumber,
  IF(CS.ClientStatus = 1, "Active", "Cancelled") AS ClientStatus,
  SM.ProductCategory,
  CS.GroupName,
  SM.ClientName,
  SM.LabourNetBranch,
  SM.AccountBooks,
  SM.Batches,
  SM.BranchCount,
  (SELECT COUNT(*) FROM taClientBranches AS A WHERE (A.ClientID = CS.ClientID) AND (A.BranchStatus = 1) AND (TRIM(COALESCE(A.MapReference, '')) <> '')) AS MapRefCount,
  SM.ClientDocCount,
  SM.ClientContactFirstName,
  SM.ClientContactSurname
FROM
  taClients AS CS
    LEFT JOIN taClientSummary SM ON (SM.ClientID = CS.ClientID)
WHERE
  1
  @pClientStatus
  @pConsultantID
  @pIsMallClient
  @pFilter
