Not bringing records - SQL Query

Question

Not bringing records - SQL Query

Navigation

#1 by (1 votes)

0

Good afternoon!

Galera, below is the query that is to bring the number of active clients in one column and the number of clients blocked in another column, but when executing the query, it is null in both columns. NOTE: When executing the separate subqueries, the quantity comes normally.

                               --Quantidade de Clientes Bloqueados e Ativos--
SELECT 
BLOQUEADOS. [Quantidade de Clientes Bloqueados],
ATIVOS. [Quantidade de Clientes Ativos]
FROM
(SELECT COUNT(DISTINCT T.UsuIDCliente) [Quantidade de Clientes Bloqueados]
  FROM Tarefa T
  LEFT JOIN Usuario U
  ON U.UsuID = T.UsuIDCliente
  INNER JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
 WHERE T.TarTitulo = 'Bloquear Cliente - Inadimplente'
   AND T.TarTipID = 572
   AND PPC.ProID = 2
   AND NOT EXISTS(SELECT 1
                    FROM Tarefa t2
                   WHERE t2.TarTitulo = 'Desbloqueio Cliente'
                     AND t2.TarTipID = 574
                     AND t2.usuidcliente  = t.usuidcliente)) BLOQUEADOS
INNER JOIN
(SELECT DISTINCT COUNT(U.UsuID) [Quantidade de Clientes Ativos]
FROM Usuario U 
INNER JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
LEFT JOIN CurvaABC ABC ON U.CurvaID = ABC.CurvaID
LEFT JOIN Solicitacao S ON U.UsuID = S.UsuIDCliente
LEFT JOIN FRM_3 F3 ON S.SolID = F3.ChamadoID
LEFT JOIN ComplementoColunas CC ON U.UsuID = CC.UsuID AND CC.CompID = 32 AND CC.ColunaID = 12
LEFT JOIN Regioes R ON U.EstadoID = R.EstadoID
LEFT JOIN CRMDetalhesConta DC ON U.UsuID = DC.ContaID
LEFT JOIN CRMRamo RM ON DC.RamoID = RM.RamoID
WHERE U.UsuTipo = 'C'
      AND PPC.ProID = 2
      AND S.SolCaminho = 'Implantação Boavista'
      AND F3.C03 IS NOT NULL
      AND U.EmpLiberada = 1) ATIVOS
ON BLOQUEADOS.[Quantidade de Clientes Bloqueados] = ATIVOS.[Quantidade de Clientes Ativos]

sql sql-server query

asked by anonymous 21.07.2017 / 20:42

1 answer

What is the operation of a list chained in C? [duplicate] Static, private and final variables

score 1 · Accepted Answer

If your two subqueries already do everything you want, there's no need to do JOIN between them.

Furthermore, you are joining the two with the Blocked and Active quantity columns, which in my view will hardly bring results.

Take the JOIN and exchange for a comma, and take the ON as well.

Type:

SELECT 
BLOQUEADOS. [Quantidade de Clientes Bloqueados],
ATIVOS. [Quantidade de Clientes Ativos]
FROM
(SELECT COUNT(DISTINCT T.UsuIDCliente) [Quantidade de Clientes Bloqueados]
  FROM Tarefa T
  LEFT JOIN Usuario U
  ON U.UsuID = T.UsuIDCliente
  INNER JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
 WHERE T.TarTitulo = 'Bloquear Cliente - Inadimplente'
   AND T.TarTipID = 572
   AND PPC.ProID = 2
   AND NOT EXISTS(SELECT 1
                    FROM Tarefa t2
                   WHERE t2.TarTitulo = 'Desbloqueio Cliente'
                     AND t2.TarTipID = 574
                     AND t2.usuidcliente  = t.usuidcliente)) BLOQUEADOS
,
(SELECT DISTINCT COUNT(U.UsuID) [Quantidade de Clientes Ativos]
FROM Usuario U 
INNER JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
LEFT JOIN CurvaABC ABC ON U.CurvaID = ABC.CurvaID
LEFT JOIN Solicitacao S ON U.UsuID = S.UsuIDCliente
LEFT JOIN FRM_3 F3 ON S.SolID = F3.ChamadoID
LEFT JOIN ComplementoColunas CC ON U.UsuID = CC.UsuID AND CC.CompID = 32 AND CC.ColunaID = 12
LEFT JOIN Regioes R ON U.EstadoID = R.EstadoID
LEFT JOIN CRMDetalhesConta DC ON U.UsuID = DC.ContaID
LEFT JOIN CRMRamo RM ON DC.RamoID = RM.RamoID
WHERE U.UsuTipo = 'C'
      AND PPC.ProID = 2
      AND S.SolCaminho = 'Implantação Boavista'
      AND F3.C03 IS NOT NULL
      AND U.EmpLiberada = 1) ATIVOS