SELECT
  CP.ProjectID,
  CP.ClientID,
  CL.FinNumber,
  CL.GroupName AS ClientName,
  CP.ProjectName,
  CP.ConsultantID,
  US.FullName,
  CP.ProjectTypeID,
  PR.ProductName,
  SO.OptionDescription AS ProjectType,
  CP.StartDate,
  CP.GoLiveDate,
  CP.EmployeeCount,
  CP.RiskLevelID,
  CP.ProjectPhaseID,
  SUBSTR(SO1.OptionDescription,4) AS ProjectPhase,
  IF(CP.ProjectStatus = 1, 'ACTIVE', 'CLOSED') AS ProjectStatus
FROM
  taClientProjects  AS CP
LEFT JOIN taClients AS CL ON (CL.ClientID = CP.ClientID)
LEFT JOIN taUsers   AS US ON (US.UserID = CP.ConsultantID)
LEFT JOIN taSystemOption AS SO  ON (CP.ProjectTypeID = SO.OptionID AND SO.OptionType = 'PROJECT-TYPE')
LEFT JOIN taSystemOption AS SO1 ON (CP.ProjectPhaseID = SO1.OptionID AND SO1.OptionType = 'PROJECT-PHASE')
LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
WHERE
  (CP.ClientID = @nClientID)
  @pFilter
ORDER BY CP.ProjectID DESC