Select Comparing Periods

Asked

Viewed 468 times

1

I need to make one select comparing two periods of sales, totalling by product. Today I have this select that does this, but only for a period (01/09/2014 to 30/09/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 it select, but from another period (01/09/2015 to 30/09/2015) for the result to look 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

1 answer

1


I made both appointments separately and then with a LEFT JOIN I put the two together.

 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
  • Perfect friend !!! Exactly what I needed. Thank you very much for the help. It worked just right !!!

  • Glad it worked. Since the answer was helpful, if possible vote in favour and mark it as accepted ;) @Alvarovieira

  • where I do it. rsr I will do it with the greatest pleasure

  • @AlvaroVieira http://meta.pt.stackoverflow.com/questions/1078/como-e-por-que-aceitar-uma-resposta

Browser other questions tagged

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