How to make a select if repeat integer 1 time for each month in a dynamic date range?

Asked

Viewed 85 times

0

Assuming I have sales that happened in the month of May. I could make the following select: SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 5

Assuming the month of June happened and now I have sales in June, could make the following select: SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 6

Assuming I wanted to see the May and June sales, I could make the following select: SELECT * FROM Vendas WHERE MONTH(Data_Venda) IN(5, 6)

But my case is more complex than this and I can’t take the "sales" of those 2 months the way I mentioned earlier, I could do it this way:

SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 5 
UNION 
SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 6

As I mentioned earlier, the most logical and sensible way would be to > 5 or IN(5,6), but the data that I need to search for is not sales and is not recorded in a way that I can do in the most normal way. The issue of sales was just an example to facilitate understanding.

My doubt is how I make the following select dynamically, ie I will spend an initial date (month and year) and a final (month and year) and the script was "added" UNION SELECT * FROM Vendas WHERE MONTH = @Mes dynamically?

 SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 5 
 UNION 
 SELECT * FROM Vendas WHERE MONTH(Data_Venda) = 6

My script is even small, has about 80 lines and hasSELECT INTO, DELETE in table #temporaria, UPDATE in table #temporaria and SELECT normal in the end.

  • https://social.msdn.microsoft.com/Forums/pt-BR/389b4c32-96b7-413b-9a25-858ad5835f20/pesquisa-sql-por-ms-e-ano?forum=520

  • Dude, I think you want a Builder query. If I understood the example, I wouldn’t do it with UNION, I would use BETWEEN in WHERE. This way Builder would make combinations of Betweens.

1 answer

0

From what I understand, you want to run the following query

SELECT * 
FROM vendas 
WHERE 
YEAR(Data_Venda) >= [ano do periodo inicial] AND 
MONTH(Data_Venda) >= [mes do periodo inicial] AND
YEAR(Data_Venda) <= [ano do periodo final] AND 
MONTH(Data_Venda) <= [mes do periodo final] 

I await reply :)

Browser other questions tagged

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