SELECT
  B.Batch,
  B.ClientID,
  B.FinNumber,
  B.InvoiceCount,
  B.GroupName,
  COALESCE(IF(B.AccountBookType = 'Xero', XE.XeroEntityName, IF(B.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(B.AccountBookType, '_', B.AccountBookID)) AS AccountBook,
  IF(B.CountProcessed >= B.InvoiceCount, 'Yes', '-') AS Processed,
  B.RetainerFeesTotal AS Value,
  (B.RetainerFeesProcessed / B.InvoiceCount) AS Invoiced,
  (B.RetainerFeesTotal - (B.RetainerFeesProcessed / B.InvoiceCount)) AS Outstanding
FROM (

    SELECT
      MD.Batch,
      MD.ClientID,
      MD.AccountBookType,
      MD.AccountBookID,
      BE.FinNumber,
      BE.GroupName,
      COUNT(*) AS InvoiceCount,
      SUM(COALESCE(Y.InvoiceCount, 0)) AS CountProcessed,
      SUM(COALESCE(MD.RetainerFees, 0.0)) AS RetainerFeesTotal,
      SUM(COALESCE(Y.RetainerFeesProcessed, 0.0)) AS RetainerFeesProcessed
    FROM
      ttRetainerBatchMembersDue AS MD
        LEFT JOIN taClients AS BE USING (ClientID)
        LEFT JOIN (

          SELECT M.Batch, M.ClientID, M.AccountBookType, M.AccountBookID, COUNT(*) AS InvoiceCount, SUM(M.UnitPriceExcl) AS RetainerFeesProcessed FROM (
            SELECT A.Batch, A.ClientID, A.AccountBookType, A.AccountBookID, A.UnitPriceExcl FROM (
              SELECT
                CAST(TRIM(SUBSTR(Batch, LOCATE(' ', IH.Batch))) AS SIGNED) AS Batch,
                IH.ClientID,
                IH.AccountBookType,
                IH.AccountBookID,
                ROUND(IL.UnitPriceExcl, 2) AS UnitPriceExcl
              FROM
                taInvoiceHeader AS IH
                  INNER JOIN taInvoiceLine AS IL ON (IL.CMSInvNo = IH.CMSInvNo)
                  INNER JOIN taClients AS BE ON (BE.ClientID = IH.ClientID)
              WHERE
                (IH.InvoiceType = 'R') AND
                (IH.DocumentNumber <> 'VOID') AND
                (IH.AppointmentID = @nYearMonth) AND
                (BE.ClientStatus = 1) AND
                (CAST(TRIM(SUBSTR(Batch, LOCATE(' ', IH.Batch))) AS SIGNED) = @nBatch)
            ) AS A
          ) AS M GROUP BY M.Batch, M.ClientID, M.AccountBookType, M.AccountBookID

        ) AS Y USING (Batch, ClientID, AccountBookType, AccountBookID)
    GROUP BY 1,2,3,4,5,6

  ) AS B
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = B.AccountBookID) AND (B.AccountBookType = 'Sage'))
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = B.AccountBookID) AND (B.AccountBookType = 'Xero'))
ORDER BY
  B.Batch, B.FinNumber
