SELECT * FROM (

  SELECT
    PS.ProspectID,
    PS.ProspectName,
    IF(X.OpenStatusCount > 0, 'Open', 'Closed') AS ProspectStatus,
    IF(CS.FinNumber IS NOT NULL, CS.FinNumber, 'new') AS FinNumber,
    S1.OptionDescription AS Industry,
    X.Products,
    X.QuotedAmount,
    BR.BranchName AS Region,
    Y.SalesPeople,
    PS.LastChangeDate
  FROM
    taProspect AS PS
      LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = PS.IndustryID))
      LEFT JOIN taBranches AS BR ON (BR.BranchID = PS.LabourNetBranchID)
      LEFT JOIN taClients AS CS ON (CS.ClientID = PS.ClientID)
      LEFT JOIN (
        SELECT
          PP.ProspectID,
          SUM(IF((PP.SalesPhaseID IS NULL) OR (PP.SalesPhaseID < 5), 1, 0)) AS OpenStatusCount,
          GROUP_CONCAT(DISTINCT PP.ProductCategory) AS Products,
          SUM(IF(PP.QuotedAmount IS NULL, 0, PP.QuotedAmount)) AS QuotedAmount
        FROM taProspectProduct AS PP
        GROUP BY PP.ProspectID

      ) AS X USING (ProspectID)
      LEFT JOIN (

        SELECT
          PP.ProspectID,
          GROUP_CONCAT(DISTINCT U1.FullName) AS SalesPeople
        FROM
          taProspectProduct AS PP
            LEFT JOIN taUsers AS U1 ON (U1.UserID = PP.SalesPersonID)
        GROUP BY PP.ProspectID

      ) AS Y USING (ProspectID)
  WHERE
    1
    @pConsultantID

) AS M
WHERE
  1
  @pFilter
