SQL SERVER - Calculation

Question

SQL SERVER - Calculation

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have a question for setting up a scorecard and that I can not get out of it.

I basically have two tables.

Table 1: CONTRACTS
  Fields: Company ID, Contract ID, Product ID, Quantity

Table 2: RECEIPTS
  Fields: Company ID, Receiving ID, Contract ID, Quantity

With this I mounted this query

SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) Total
 FROM 
    Contrato A
 WHERE 
    A.Registro='Ativo'
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa

This query gives me a correct result.

To try to bring me the result of the total received from the contracts I mounted the following query.

SELECT
        A.ID_Empresa,
        A.ID_Produto,
        SUM(CAST(A.Quantidade AS int)) Total
     FROM 
        Contrato A
LEFT JOIN Recebimentos B ON B.ID_Contrato=A.ID_Contrato 
     WHERE 
        A.Registro='Ativo'
    GROUP BY
        A.ID_Empresa,
        A.ID_Produto
    ORDER BY
        A.ID_Empresa

But when I try to do this join with the RECEIVEDS table to know how much has been received, the query is adding the contract quantity each time it appears on the receiving line.

Is there any way to keep these accounts separate and not duplicate the sum of the contracts?

sql sql-server

asked by anonymous 24.08.2017 / 20:25

2 answers

1

You did not put sample data, not even an example of how the correct result would be, however I re-created your tables in the fiddle and put some data. I believe your need is to know Contracted Quantity / Quantity Received and for the example I created, this query solves your problem. See:

   SELECT
A.ID_Empresa,
A.ID_Produto,
A.id_contrato,
SUM(CAST(A.Quantidade AS int)) TotalContratado,
sum(coalesce(r.quantidade,0)) as totalRecebido
FROM 
    Contratos A
 LEFT OUTER JOIN recebimentos r on r.id_contrato = a.id_contrato   
GROUP BY
    A.ID_Empresa,
    A.ID_Produto,
    A.id_contrato
ORDER BY
    A.ID_Empresa;

See in SQLFiddle: link

24.08.2017 / 21:37

My dropdown does not matter bank options Magic methods of python in javascript

score 3 · Accepted Answer

From what I understood from your question, it would be this answer:

SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) Total
 FROM 
    Contrato A
 WHERE 
    A.Registro='Ativo'
    AND A.ID_Contrato IN ( SELECT ID_Contrato  FROM Recebimentos  )
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa

Contract Comparison and Received:

 SELECT
    A.ID_Empresa,
    A.ID_Produto,
    SUM(CAST(A.Quantidade AS int)) AS TotalContrato,
    ValorRecebido
 FROM 
    Contrato A
 LEFT JOIN (
    SELECT 
        ID_Empresa,
        ID_Produto,
        SUM(CAST(Quantidade as int)) as ValorRecebido
    FROM RECEBIMENTOS
    GROUP BY
        ID_Empresa,
        ID_Produto
 ) AS B
 ON B.ID_Contrato=A.ID_Contrato 

 WHERE 
    A.Registro='Ativo'
GROUP BY
    A.ID_Empresa,
    A.ID_Produto
ORDER BY
    A.ID_Empresa