Select Comparing Periods

Question

Select Comparing Periods

Navigation

#1 by (1 votes)

1

I need to make a select that compares two sales periods, totaling by product. Today I have this select that does this, but only for a period (01/09/2014 to 09/30/2014)

SELECT ACC.PRODUTODESC  PRODUTO,
       Sum(ACC.VALORTARIFA) + 0.0 VALORA,
       Count(ACC.ID) + 0.0 TRANSACOESA
FROM BASEDADOSPADRAO ACC
WHERE ACC.DATAEMISSAO BETWEEN '20140901' and '20140930'
AND ACC.CLIENTE in (21333)
GROUP BY ACC.PRODUTODESC

which gives me the following result:

PRODUTO        VALORA     TRANSACOESA
Carro         4093.96             1.0
Miscelâneos  18806.35            15.0
Aéreo       378076.11           701.0
Hotel       230376.81           264.0

I need to add the result of the same select , but of another period (01/09/2015 to 09/30/2015) so that the result would be something like the one below

PRODUTO        VALORA     TRANSACOESA          VALORB     TRANSACOESB
Carro         4093.96             1.0            NULL            NULL     
Miscelâneos  18806.35            15.0         7182.23             3.0
Aéreo       378076.11           701.0       275637.66           443.0  
Hotel       230376.81           264.0       177826.98           204.0

mysql sql sql-server

asked by anonymous 05.02.2016 / 15:03

1 answer

Pass parameters in pythonic form JSON return for PHP with Ajax

score 1 · Accepted Answer

I made the two queries separately and then with a LEFT JOIN I joined the results of the two.

 SELECT T1.PRODUTO, T1.VALORA, T1.TRANSACOESA, T2.VALORB, T2.TRANSACOESB FROM 
(SELECT ACC.PRODUTODESC  PRODUTO,
           Sum(ACC.VALORTARIFA) + 0.0 VALORA,
           Count(ACC.ID) + 0.0 TRANSACOESA
    FROM BASEDADOSPADRAO ACC
    WHERE ACC.DATAEMISSAO BETWEEN '20140901' and '20140930'
    AND ACC.CLIENTE in (21333)
    GROUP BY ACC.PRODUTODESC) AS T1 LEFT JOIN

    (SELECT ACC.PRODUTODESC  PRODUTO,
           Sum(ACC.VALORTARIFA) + 0.0 VALORB,
           Count(ACC.ID) + 0.0 TRANSACOESB
    FROM BASEDADOSPADRAO ACC
    WHERE ACC.DATAEMISSAO BETWEEN '20150901' and '20150930'
    AND ACC.CLIENTE in (21333)
    GROUP BY ACC.PRODUTODESC) AS T2 ON T1.PRODUTO = T2.PRODUTO