Query query in SQL Server 2012

Question

Query query in SQL Server 2012

Navigation

#1 by (0 votes)

1

I need this query to come only with records where the CURVE ABC column is different from the MODEL column.

For example, this customer's ABC curve is silver and the model is diamond / gold, so it turns out that the name silver is not in the model field.

SELECT 
C.UsuID [Código],
    U.UsuNome [Cliente],
    P.ProNome [Produto],
    M.MCDescricao [Modelo],
    M.MCId [ID_Modelo],
    A.Descricao [Curva ABC],
    A.CurvaID [ID_Curva]
FROM Usuario U 
INNER JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
INNER JOIN ComplementoColunas C ON U.UsuID = C.UsuID AND C.LinhaID  = 1 
                                                     AND C.CompID       = 32 
                                                     AND C.ColunaID = 12
LEFT JOIN  CurvaABC     A     ON A.CurvaID = U.CurvaID
INNER JOIN Usuario            AU ON AU.UsuID = c.ColunaValor
JOIN ProdutoUsuario PU ON U.UsuID = PU.UsuID
    JOIN Produto P ON PU.ProID = P.ProID
    JOIN ModeloCriticidade M ON PU.MCId = M.MCId AND PU.ProID = M.ProID
WHERE U.EmpLiberada = 1 AND U.UsuTipo = 'C' AND PPC.ProID in(2,5,12)
AND U.UsuID = 316

The result is this way:

Cliente           Produto             Modelo     ID_Modelo  Curva ABC
ChilliBeans Conciliador e-Extrato   Diamante|Ouro   23       PRATA

sql sql-server query

asked by anonymous 09.02.2018 / 14:25

1 answer

Citizen Card in Node [closed] Difficulty in listing SQLite result in ListView Android

score 0 · Accepted Answer

From what I understand, you want to get the records whose value from the ABC CURVE column is not contained in the MODEL column

To do this, simply use one of the following conditions:

Option 1: CHARINDEX(CurvaABC.Descricao,ModeloCriticidade.MCDescricao) = 0

Option 2: NOT ModeloCriticidade.MCDescricao LIKE '%' + CurvaABC.Descricao + '%'

If you want the query to return the unmatched records in the CurvaABC ( Descricao IS NULL ) table, add the ISNULL(CurvaABC.Descricao,'xxx')

The query could look like this:

SELECT 
    C.UsuID [Código],
    U.UsuNome [Cliente],
    P.ProNome [Produto],
    M.MCDescricao [Modelo],
    M.MCId [ID_Modelo],
    A.Descricao [Curva ABC],
    A.CurvaID [ID_Curva]
FROM Usuario U 
JOIN ParametroProdutoCliente PPC ON U.UsuID = PPC.UsuID
JOIN ComplementoColunas C ON U.UsuID = C.UsuID A
                             AND C.LinhaID = 1 
                             AND C.CompID = 32 
                             AND C.ColunaID = 12
JOIN Usuario AU ON AU.UsuID = c.ColunaValor
JOIN ProdutoUsuario PU ON U.UsuID = PU.UsuID
JOIN Produto P ON PU.ProID = P.ProID
JOIN ModeloCriticidade M ON PU.MCId = M.MCId AND PU.ProID = M.ProID
LEFT JOIN CurvaABC A  ON A.CurvaID = U.CurvaID
WHERE U.EmpLiberada = 1 
      AND U.UsuTipo = 'C' 
      AND PPC.ProID in(2,5,12)
      AND CHARINDEX( ISNULL(A.Descricao,'xxx') , M.MCDescricao ) = 0