SELECT * FROM (

  SELECT
    SM.LabourNetBranch,
    CS.ClientID AS ClientID,
    CS.FinNumber AS FinNumber,
    CS.GroupName AS GroupName,
    IF((CS.ClientStatus = 1), 'Active', 'Cancelled') AS ClientStatus,
    IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, 'National', 'Local') AS National,
    CS.PaymentMethod,
    SM.ClientGroup,
    IF(SP.SuspensionStart IS NULL, '', IF(SP.SuspensionEnd IS NULL, 'Yes', IF(SP.SuspensionEnd < SP.SuspensionStart, 'Yes', ''))) AS Suspended,
    IF(COALESCE(SM.AccountBalance, 0.0) > COALESCE(CS.CreditLimit, 0.0), 'Yes',
      IF(COALESCE(CS.PaymentTerms, 0) < 15, IF(COALESCE(SM.AccountBalance, 0.0) > 0.0, 'Yes', ''),
        IF(CS.PaymentTerms < 45, IF(COALESCE(SM.SixtyDayBalance, 0.0) + COALESCE(SM.NinetyDayBalance, 0.0) + COALESCE(SM.OneTwentyDayBalance, 0.0) > 0.0, 'Yes', ''),
          IF(CS.PaymentTerms < 75, IF(COALESCE(SM.NinetyDayBalance, 0.0) + COALESCE(SM.OneTwentyDayBalance, 0.0) > 0.0, 'Yes', ''),
            IF(COALESCE(SM.OneTwentyDayBalance, 0.0) > 0.0, 'Yes', '')
    )))) AS Blocked,
    SM.ProductCategory,
    CS.DateJoined AS DateJoined,
    CS.TerminationDate,
    CONCAT( TRIM(COALESCE(CS.TerminationReason, '')), IF(TRIM(COALESCE(CS.TerminationNotes, '')) <> '', CONCAT(': ', LEFT(TRIM(COALESCE(CS.TerminationNotes, '')),60), '...'), '')) AS TerminationReason,
    IF(COALESCE(CS.PaymentTerms, 0) < 15, 'Immediate', IF(CS.PaymentTerms > 120, 'Open Ended', CS.PaymentTerms)) AS PaymentTerms,
    CS.CreditLimit,
    SM.InvoicesDueCount,
    SM.ClientRetainer,
    SM.AccountBalance,
    SM.CurrentBalance,
    SM.ThirtyDayBalance,
    SM.SixtyDayBalance,
    SM.NinetyDayBalance,
    SM.OneTwentyDayBalance,
    IF(COALESCE(CS.PaymentTerms, 0) < 15, COALESCE(SM.AccountBalance, 0.0),
      IF(CS.PaymentTerms < 45, COALESCE(SM.ThirtyDayBalance, 0.0) + COALESCE(SM.SixtyDayBalance, 0.0) + COALESCE(SM.NinetyDayBalance, 0.0) + COALESCE(SM.OneTwentyDayBalance, 0.0),
        IF(CS.PaymentTerms < 75, COALESCE(SM.SixtyDayBalance, 0.0) + COALESCE(SM.NinetyDayBalance, 0.0) + COALESCE(SM.OneTwentyDayBalance, 0.0),
          COALESCE(SM.OneTwentyDayBalance, 0.0)
    ))) AS OverdueBalance
  FROM
    taClients AS CS
      LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CS.ClientID)
      LEFT JOIN (

        SELECT
          CS1.ClientID,
          MAX(ST.audPost) AS SuspensionStart,
          MAX(LEFT(EN.audDate, 10)) AS SuspensionEnd
        FROM
          taClients AS CS1
            LEFT JOIN (SELECT audKey, audDate, audPre, audPost 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'))
              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'))
            ) AS M) AS ST ON (ST.audKey = CS1.ClientID)
            LEFT JOIN (SELECT audKey, audDate, audPre, audPost FROM (
              (SELECT A.audKey, A.audDate, A.audPre, A.audPost FROM tbAudit AS A
              WHERE (audTable = 'taClients') AND (audField = 'SuspensionDate') AND (A.audPost = 'Clear'))
              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'))
            ) AS M) AS EN ON (EN.audKey = CS1.ClientID)
        WHERE
          (ST.audDate IS NOT NULL) AND
          ((ST.audPost = EN.audPre) OR (EN.audPre IS NULL))
        GROUP BY
          CS1.ClientID

      ) AS SP ON (SP.ClientID = CS.ClientID)
  WHERE
    (SM.AccountBalance <> 0.0)
    @pProductCategory
    @pBranchRegion

) AS X
ORDER BY
  X.LabourNetBranch,
  X.ProductCategory,
  X.OverdueBalance DESC
