SQL Sum by periods

Question

I’m having a question to assemble a query, following:

In my sales chart I would like to return the sum per product period. Today I am doing 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 and not per period. But in my case, I need to make the sum in the 30-day period, for example 05.09.2015 and the search was made 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 carry out this select, I would like an opinion.

Thank you!

Answer 1

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 as well:

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 better.