SELECT
  BP.ClientBranchProductID,
  BP.ClientProductID,
  BP.ClientBranchID,
  BR.BranchName AS Branch,
  U1.FullName AS Consultant,
  CP.ClientID,
  CB.BranchName,
  BP.Status,
  COALESCE(IF(CP.AccountBookType = 'Pastel', PB.DatabaseName, IF(CP.AccountBookType = 'Xero', XE.XeroEntityName, IF(CP.AccountBookType = 'Sage', SE.SageEntityName, NULL))), CONCAT(CP.AccountBookType, '_', CP.AccountBookID)) AS AccountBookName,
  SM.ClientGroup,
  CP.Batch,
  PR.ProductName,
  fnClientCoverPeriodEnd(CP.ClientID, NOW()) AS CoverEndDate
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientSummary AS SM ON (SM.ClientID = CB.ClientID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = CB.LabourNetBranchID)
    LEFT JOIN taClients AS BE ON (BE.ClientID = CB.ClientID)
    LEFT JOIN taPastelBook AS PB ON ((PB.PastelBookID = CP.AccountBookID) AND (CP.AccountBookType = 'Pastel'))
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Sage'))
WHERE
  1