SELECT
  AD.audDate AS AudDate,
  CL.ClientID,
  CL.FinNumber,
  CL.GroupName,
  CL.DateJoined,
  CB.ClientBranchID,
  REPLACE(REPLACE(REPLACE(COALESCE(CB.BranchName, ''), '"', ''), '''', ''), '\\', '') AS ClientBranchName,
  COALESCE(CB.LabourNetBranchID, '') AS LabourNetBranchID,
  COALESCE(BR.BranchName, '') AS LabourNetBranchName
FROM
    tbAudit AS AD
    LEFT JOIN cms_archive.taClients        AS CL ON (CL.ClientID = AD.audKey)
    LEFT JOIN cms_archive.taClientBranches AS CB ON (CB.ClientID = CL.ClientID)
    LEFT JOIN taBranches                   AS BR ON (BR.BranchID = CB.LabourNetBranchID)
WHERE
  AD.audProcess = 'ClientArchive'
  AND AD.audTable = 'taClients'
  @pFromDate
  @pToDate
  AND (
      (@aBranchRegion = '') OR
      (BR.BranchRegion = @aBranchRegion)
    )
ORDER BY
  AD.audDate DESC;