CODEWITHSUNDEEP
sqldivide
Tiago Costa
Tiago Costa

Reputation: 35

SQL Divide a field If

I'm trying to build a query to get the minimum stock of X components from an article. Imagine a computer, it has rams, motherboard, etc etc. I can get the min component quantity easily, however, we usually place 2 RAM's in the computer we want to build, so, I want the query to get the StockValue of the RAMs/2.

Here's my query:

SELECT A1.Artigo AS Artigo,
       A1.Descricao AS Descricao,
       MIN(AA.StkActual) AS Stock,
       0 AS QuantidadeEmEncomendasPendentes,
       'Componente Stock' AS Id
FROM Artigo A1
     INNER JOIN ComponentesArtigos CA ON CA.ArtigoComposto = A1.Artigo
     INNER JOIN Artigo A2 ON A2.Artigo = CA.Componente
                         AND (A2.Familia = 98
                           OR A2.Familia = 45
                           OR A2.Familia = 39
                           OR A2.Familia = 37
                           OR A2.Familia = 38)
     INNER JOIN ArtigoArmazem AA ON AA.Artigo = A2.Artigo
                                AND AA.Armazem = 'A7'
GROUP BY A1.Artigo,
         A1.Descricao
HAVING A1.Artigo = '0180005132';

Things you need to know: min(AA.StkActual) is the StockValue I'm talking about. The RAM's are defined by family (A2.Familia = 39)

I don't want to divide the result of the query by 2, I want to divide the stock of the component/article which family = 39 by 2

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

If I understand correctly, you want a conditional minimum:

SELECT A1.Artigo AS Artigo,
       A1.Descricao AS Descricao,
       MIN(CASE WHEN A2.familia = 38 THEN AA.StkActual / 2 ELSE AA.StkActual END) AS Stock,
       0 AS QuantidadeEmEncomendasPendentes,
       'Componente Stock' AS Id
. . .

Upvotes: 1

Related Questions