Adds WITH query within another query

Rate the following code.

-- código #1 v3
with 
cteUm as (
  SELECT p.projid, tt.tartitulo
    FROM tarefa tt
         INNER JOIN projetos p ON p.projid = tt.projid
         LEFT JOIN tipo c ON c.tipid = tt.tartipid
   WHERE tt.modid = 181
     AND tt.tarstatus <> 9
     AND (tt.tartipid BETWEEN 867 AND 934
          OR tt.tartipid IN (1004, 1034))
     AND c.tipdescricao LIKE 'Habilita%'
),
cteDois as (
SELECT r.projid,
       STUFF((SELECT ', ' + t.tartitulo
                FROM cteUm t
               WHERE t.projid = r.projid
                 FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS operadoras
  FROM cteUm r
  GROUP BY projid
)
SELECT pp.projdesc, pp.projid,
       c.compdesc as qtdecnpjs,
       u1.usuuf, C2.operadoras
  FROM projetos pp
       INNER JOIN tarefa t ON pp.projid = t.projid
       INNER JOIN usuario u ON t.usuidresponsavel = u.usuid
       INNER JOIN usuario u1 ON t.usuidcliente = u1.usuid
       --LEFT JOIN statusaux s ON t.tarstatus = s.statusid
       LEFT JOIN complemento c ON c.usuid = t.usuidcliente
                              AND c.compid = 1
       LEFT JOIN cteDois as C2 on C2.projid = pp.projid
 WHERE t.macprocid = 33
   AND t.tartipid = 866;

--
IF Object_Id('tempDB..#FiltroTarefa', 'U') is not null
  DROP TABLE #FiltroTarefa;
go

The external SELECT of the first code is transformed into the cteDois CTE.

It seems to me that the statusaux table is not required in the outer (the latter) query, mainly because the join is of type OUTER JOIN.

DISTINCT has been removed. If the result indicates that it is necessary, reinsert it.

Column projdesc has been replaced by column projid in cteUm . It is that object-object comparison is not always reliable when it is possible to have more than one object with the same name.

If you want to use the concatenation of the first query to return the records of the second you would have to do the following:

WITH tabela (projeto, operadora, compdesc, usuuf) AS (
  SELECT pp.projdesc,
         t.titulo,
         c.compdesc,
         u1.usuuf
  FROM projetos pp
       INNER JOIN tarefa t ON pp.projid = t.projid
       INNER JOIN usuario u ON t.usuidresponsavel = u.usuid
       INNER JOIN usuario u1 ON t.usuidcliente = u1.usuid
       LEFT JOIN statusaux s ON t.tarstatus = s.statusid
       LEFT JOIN complemento c ON c.usuid = t.usuidcliente
                              AND c.compid = 1
 WHERE t.macprocid = 33
   AND t.tartipid IN (866))

SELECT r.projeto,
       STUFF((SELECT ', ' + t.operadora
                FROM tabela t
               WHERE t.projeto = r.projeto

                 FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS operadoras,
       SUM(r.compdesc) AS qtdecnpjs,
       r.usuff
  FROM tabela r
 GROUP BY r.projeto, r.usuff;