Adds WITH query within another query

Question

Guys, how to add the query below as a subconsulta.

Consultation with WITH:

WITH tabela (projeto, operadora) AS (
  SELECT p.projdesc,
         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%')

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
  FROM tabela r
 GROUP BY projeto;

Another Query, where this is to place the query from above, because another column will be generated with the query above.

SELECT DISTINCT(pp.projdesc),
               c.compdesc qtdecnpjs,
               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)

Answer 1

If you want to use the first concatenation 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;

Answer 2

Evaluate 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 necessary in the external query (the last one), mainly because the junction is of type OUTER JOIN.

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

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