SQL Sum for periods

Navigation
2

I'm having a question for the following query:

In my sales table I would like to return the sum by period of a given product. Today I'm doing it this way: 

select YEAR(venda.data_venda) as Ano,
venda.codprod_venda as CodigoProduto,
produtos.descricao_prod,
MONTH(venda.data_venda) as Mes,
SUM(venda.qntdporprod_venda) as QtdVendida

FROM venda, produtos WHERE venda.codprod_venda = produtos.codigo_prod and produtos.codigo_prod=1
GROUP BY YEAR(data_venda), cod_prod_venda, MONTH(data_venda)
ORDER BY YEAR(data_venda), cod_prod_venda, MONTH(data_venda)

This select returns the sum of a given product per month, not per period. But in my case, I need to make the sum in the period of 30 days, for example 05.09.2015 and the search was done from 04.09.2015 until 03.10.2015 and then 04.10.2015 until 03.11.2015 and so on. I do not know if it is possible to perform this select, I would like an opinion.

Thank you!

    
asked by anonymous 05.09.2015 / 21:56

1 answer

0

Try including this command in your where clause: 

V.data >= '01/09/2015' and V.data <= DATEADD(DAY,30, '01/09/2015')

Your query would look like this, I made an improvement using INNER JOIN too: 

select YEAR(V.data_venda) as Ano,
V.codprod_venda as CodigoProduto,
P.descricao_prod,
MONTH(V.data_venda) as Mes,
SUM(V.qntdporprod_venda) as QtdVendida

FROM venda V join produtos P 
on V.codprod_venda = P.codigo_prod
WHERE  P.codigo_prod = 1 and

       V.data >= '01/09/2015' and V.data <= DATEADD(DAY,30, '01/09/2015')

GROUP BY YEAR(V.data_venda), cod_prod_venda, MONTH(V.data_venda)
ORDER BY YEAR(V.data_venda), cod_prod_venda, MONTH(V.data_venda)

I hope this solves, if you still need help, create a sqlfiddle.com as an example to help you better.

    
11.09.2015 / 15:58
How to open another java program in java and get all the information it sends on the console [closed] How to fix encoding problems in a web application page?