SELECT
  LabourNetBranch AS LabourNetBranchRegion,
  ClientID,
  FinNumber,
  GroupName,
  IF((ClientStatus = 1), 'Active', 'Cancelled') AS ClientStatus,
  DateJoined,
  ClientGroup,
  FinCategoryName,
  ProductCategory,
  National,
  ClientRetainer AS Retainer,
  SuspensionStart,
  SuspensionEnd,
  IF(SuspensionEnd IS NULL, 'Yes', '') AS CurrentlySuspended,
  IF(SuspensionEnd IS NULL, ROUND(fnClientRetainer(ClientID, SuspensionStart), 2), 0.0) AS SuspendedRetainer,
  TerminationDate,
  IF(TerminationDate <> '', ROUND(fnClientRetainer(ClientID, DATE(TerminationDate) - INTERVAL 1 DAY), 2), 0.0) AS TerminatedRetainer,
  TerminationReason
FROM (

  SELECT
    CS.ClientID,
    CS.FinNumber,
    CS.GroupName,
    CS.ClientStatus,
    CS.DateJoined,
    SM.ProductCategory,
    SM.ClientRetainer,
    SM.ClientGroup,
    SM.National,
    SM.LabourNetBranch,
    FC.FinCategoryName,
    ST.SuspensionStart,
    ST.SuspensionEnd,
    IF((TRIM(CS.TerminationDate) = '') OR (CS.TerminationDate IS NULL), '', CS.TerminationDate) AS TerminationDate,
    IF((TRIM(CS.TerminationDate) = '') OR (CS.TerminationDate IS NULL), '', CS.TerminationReason) AS TerminationReason
  FROM
    taClients AS CS
      LEFT JOIN taFinCategory AS FC ON (FC.FinCategoryID = CS.FinCategory)
      LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
      INNER JOIN (SELECT M.audKey AS ClientID, M.audDate, M.audPost AS SuspensionStart, LEFT(N.audDate, 10) AS SuspensionEnd FROM (
          (SELECT A.audKey, A.audDate, A.audPre, A.audPost FROM tbAudit AS A
          WHERE (A.audTable = 'taClients') AND (A.audField = 'SuspensionDate') AND (A.audPre = 'Clear') AND (A.audPost >= @aFromDate) AND (A.audPost <= @aEffectiveDate))
          UNION
          (SELECT B.audKey, B.audDate, B.audPre, B.audPost FROM cms_archive.tbAudit AS B
          WHERE (B.audTable = 'taClients') AND (B.audField = 'SuspensionDate') AND (B.audPre = 'Clear') AND (B.audPost >= @aFromDate) AND (B.audPost <= @aEffectiveDate))
        ) AS M LEFT JOIN (
            (SELECT A.audKey, A.audDate, A.audPre, A.audPost FROM tbAudit AS A
            WHERE (audTable = 'taClients') AND (audField = 'SuspensionDate') AND (A.audPost = 'Clear') AND (A.audDate >= @aFromDate))
            UNION
            (SELECT B.audKey, B.audDate, B.audPre, B.audPost FROM cms_archive.tbAudit AS B
            WHERE (audTable = 'taClients') AND (audField = 'SuspensionDate') AND (B.audPost = 'Clear') AND (B.audDate >= @aFromDate))
          ) AS N ON ((N.audKey = M.audKey) AND (N.audPost = M.audPre))
      ) AS ST ON (ST.ClientID = CS.ClientID)

) AS X
ORDER BY
  LabourNetBranchRegion,
  GroupName,
  SuspensionStart
