SELECT
  CS.ClientID,
  CS.FinNumber,
  IF(CS.ClientStatus = 1, 'Active', 'Cancelled') AS ClientStatus,
  IF((CS.EmployersOrganisation IS NULL) OR (TRIM(CS.EmployersOrganisation) = ''), "No", CS.EmployersOrganisation) AS EmployersOrganisation,
  IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, 'National', 'Local') AS National,
  CS.GroupName,
  SM.ProductCategory,
  SM.LabourNetBranch AS Regions,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  CS.EstTurnOver AS EstTurnOver,
  SM.BranchCount,
  fnClientRetainer(CS.ClientID, NOW()) AS Retainer
FROM
  taClients AS CS
    LEFT JOIN taSystemOption  AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = CS.IndustryID))
    LEFT JOIN taSystemOption  AS S2 ON ((S2.OptionType = 'SETA')     AND (S2.OptionID = CS.SetaID))
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
WHERE
  ((TRIM(@aDateJoinedFrom) = '') OR (CS.DateJoined >= @aDateJoinedFrom))
  @pProductCategory
  @pBranchRegion
  @pClientStatus
ORDER BY
  SM.LabourNetBranch
