Query Doubt in SQL Server 2012

Question

Query Doubt in SQL Server 2012

Asked 6 years, 9 months ago

Viewed 78 times

1

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

For example, this customer the ABC curve of it is silver and the model is diamond/gold, so it is noticed that the name silver is not found in the field model.

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

Where CurvaABC <> Modelo?

– Jéf Bueno

2018/02/09 at 13:47
It would be more like a like. In the column Curve ABC, has the name silver, however, this name silver is not in the column model. Understood?

– Renan Bessa

2018/02/09 at 13:50
Not exists https://technet.microsoft.com/pt-br/library/ms184297(v=sql.105). aspx , I believe solves

– Motta

2018/02/09 at 17:54
Comparisons using string may not be practical. There is no way to match tables CurvaABC and ModeloCriticidade, using the columns CurvaID and McID?

– José Diz

2018/02/12 at 21:05

1 answer

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.

by Fernando • **1,027** points · Answer 1 · 2018-02-11T00:33:30+00:00

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

To do this, just 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 records that do not match in the Curvaabc table (Descricao IS NULL), add in the above options the function 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