SELECT
  BE.ClientID,
  BE.FinNumber AS AccountCode,
  BE.ClientStatus,
  COALESCE(X.AccountBookType, 'Sage') AS AccountBookType,
  COALESCE(X.AccountBookID, 0) AS AccountBookID,
  COALESCE(BE.GroupName, '') AS TradingName,
  COALESCE(BE.EmailAddress, '') AS EmailAddress,
  COALESCE(BE.VATNumber, '') AS VATNumber,
  COALESCE(BE.CompanyRegNumber, '') AS CompanyRegNumber,
  COALESCE(BE.PaymentMethod, '') AS PaymentMethod,
  COALESCE(S1.OptionDescription, '') AS Industry,
  '3' AS TaxGroup,
  '3' AS TaxSolution,
  'true' AS IsTaxable,
  COALESCE(BE.Postal1, '') AS Postal1,
  COALESCE(BE.Postal2, '') AS Postal2,
  COALESCE(BE.Postal3, '') AS Postal3,
  COALESCE(BE.PostalCode, '') AS PostalCode,
  COALESCE(BE.BankSortCode, '') AS BankSortCode,
  COALESCE(BE.BankAccountNumber, '') AS BankAccountNumber,
  COALESCE(HO.Address1, '') AS Address1,
  COALESCE(HO.Address2, '') AS Address2,
  COALESCE(HO.Address3, '') AS Address3,
  COALESCE(HO.Address4, '') AS Address4,
  COALESCE(HO.Address5, '') AS Address5,
  COALESCE(HO.TelephoneNo, '') AS TelephoneNo,
  COALESCE(HO.TelephoneAreaCode, '') AS TelephoneAreaCode,
  COALESCE(HO.FaxNo, '') AS FaxNo,
  COALESCE(HO.FaxAreaCode, '') AS FaxAreaCode,
  COALESCE(HO.OtherTelNo, '') AS OtherTelNo,
  COALESCE(HO.OtherAreaCode, '') AS OtherAreaCode
FROM
  taClients AS BE
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = BE.IndustryID))
    INNER JOIN taClientBranches AS HO ON ((HO.ClientID = BE.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = 1))
    INNER JOIN (

      SELECT
        Y.ClientID,
        'Sage' AS AccountBookType,
        0 AS AccountBookID
      FROM (

        SELECT
          BE.ClientID
        FROM
          taClients AS BE
            INNER JOIN taAccountBookMember AS BM ON (BM.ClientID = BE.ClientID)
            INNER JOIN taSageEntity AS SE ON (SE.SageEntityID = BM.AccountBookID)
        WHERE
          (BE.ClientStatus = 1) AND
          (BE.FinNumber <> 'NONE') AND
          (BM.AccountBookType = 'Sage') AND
          (SE.Enabled = 1)
          @pBookIDMember
          @pClientID

        UNION

        SELECT
          BE.ClientID
        FROM
          taClients AS BE
            INNER JOIN taClientProducts AS CP ON (CP.ClientID = BE.ClientID)
            INNER JOIN taSageEntity AS SE ON (SE.SageEntityID = CP.AccountBookID)
        WHERE
          (BE.ClientStatus = 1) AND
          (BE.FinNumber <> 'NONE') AND
          (CP.Status = 1) AND
          (CP.AccountBookType = 'Sage') AND
          (SE.Enabled = 1)
          @pBookIDProduct
          @pClientID

      ) AS Y
      GROUP BY Y.ClientID

    ) AS X ON (X.ClientID = BE.ClientID)
WHERE
  (BE.ClientStatus = 1) AND
  (BE.FinNumber <> '') AND
  (BE.FinNumber <> 'NONE')
  @pClientID
ORDER BY
  AccountBookType,
  AccountBookID,
  AccountCode
