-1
Good evening guys. I’m on a quest that has been depleting my neurons for a week and I still can’t find the solution.
In an ERP used by a client, the demand to visualize purchase value vs sale, grouped by section (market classification. Ex.: Cereals, Laticinios, Matins, Cold and etc).
I can make separate inquiries. One for purchase and one for sale:
Query buys:
SELECT
A.SECCOD, B.SECDES, SUM(C.ITEVLRTOT) AS COMPRA FROM ITEM_ENTRADA AS C
JOIN
PRODUTO AS A ON C.PROCOD = A.PROCOD
JOIN
SECAO AS B ON A.SECCOD = B.SECCOD
JOIN
ENTRADA AS D ON C.ENTDOC = D.ENTDOC
WHERE D.ENTDAT BETWEEN '2020-01-01' AND '2020-07-30' GROUP BY A.SECCOD,B.SECDES
Query Venda
SELECT A.SECCOD, B.SECDES, SUM (C.ITVVLRTOT) AS VENDA FROM ITEVDA AS C
JOIN
PRODUTO AS A ON C.PROCOD = A.PROCOD
JOIN
SECAO AS B ON A.SECCOD = B.SECCOD
JOIN TRANSACAO AS D ON (D.TRNSEQ = C.TRNSEQ AND D.CXANUM = C.CXANUM AND D.TRNDAT = C.TRNDAT)
AND (D.TRNDAT BETWEEN '2020-01-01' AND '2020-01-31')
AND (D.TRNTIP = '1') AND (C.ITVTIP <> '2')
WHERE 1=1 GROUP BY A.SECCOD, B.SECDES ORDER BY
A.SECCOD ASC
What I need is SECCOD, SECDES, TOTAL_COMPRA, TOTAL_VENDA. But I can’t. The closest I got, the date filter was applied either on purchase, or on sale.
Is there any possibility of doing this consultation as I wish?
I recommend putting the expected output and SQL on the site http://sqlfiddle.com/ so we can help you better
– Tiedt Tech
Try Union, see if this helps https://forum.imasters.com.br/topic/586346-select-abstract_em-v%C3%A1rias-tables/? do=findComment&comment=2287737
– Motta
@Motta, I tried with 'UNION ALL', but he puts the results just below the first select. It looks like this: (columns displayed) 'Select1: SECCOD, SECDES, COMPRA' Select2: 'SECCOD, SECDES, VENDA'. I wanted the result like this: 'SECCOD, SECDES, BUY, SALE'
– Joao Ribeiro