CTE Error (nvarchar to numeric)
You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland's complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn't expect it.

As long as aggregates and fulltext functions aren't involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';


You will need to change something, something to the proper precision/scale (or use one of the int types if you don't need decimal places). You should never declare varying types without specifying the length - this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it's always useful to include information like what version of SQL Server you're using), you can simplify this:

CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)


to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)


And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.