SELECT
  CR.ChangeRequestID,
  CR.ChangeRequestName,
  CR.ChangeRequestDescription,
  U1.FullName AS RequestedBy,
  U2.FullName AS CreatedBy,
  LEFT(CreateDate, 10) AS CreateDate,
  S1.OptionDescription AS ChangeRequestCategory,
  S2.OptionDescription AS ChangeRequestStatus,
  CR.Priority,
  CR.Estimate
FROM
  taChangeRequest AS CR
    LEFT JOIN taUsers AS U1 ON (U1.UserID = CR.RequestedByID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = CR.CreatedByID)
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'CHANGE-REQUEST-CATEGORY') AND (S1.OptionID = CR.ChangeRequestCategoryID))
    LEFT JOIN taSystemOption AS S2 ON ((S2.OptionType = 'CHANGE-REQUEST-STATUS') AND (S2.OptionID = CR.ChangeRequestStatusID))
WHERE
  1
  @pShowDone
  @pRequestedByID
  @pCreateFromTo
  @pLastChangeFromTo
  @pFilter
ORDER BY
  CR.Priority DESC,
  CR.ChangeRequestID DESC