SQL Server - Select last 7 days from the last record

Question

Guys I’m beginner in SQL and it’s complicated to solve this problem that came up.. I need a select in SQL-Server that returns: the sales of the last seven days.

For example if the last day there was sale, was on 10/04/2019, then the query should show sales between 04/04/2019 and 10/04/2019.

The logic I believe is correct, but I do not know the proper commands to make this select... Thanks there who can help me!! : D

SELECT * FROM vendas
    where ven_data <= max(ven_data)
    and ven_data >= max(ven_data - 7)

Answer 1

Following your example, you can make this filter on where, using a subselect:

select *
  from vendas
 where ven_data <= ( select max(ven_data) from vendas )
   and ven_data >= ( select dateadd(day, -6, max(ven_data)) from vendas );

I used the max to get the last sale and again the max to obtain the date seven days before the last sale, with the help of the function dateadd.

Obs.: I put -6 in the date subtraction to be your example, because day 10 - 7, would be 3, but by your example, you want the day after that, this can also be changed as to the >= in the condition of where, thus maintaining 7 in subtraction but the condition is only >

---

See this online example: http://sqlfiddle.com/#! 18/680e7/3

---

Documentations:

https://docs.microsoft.com/pt-br/sql/t-sql/functions/max-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/pt-br/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15

Answer 2

The response of Daniel Mendes is correct, but could make a small adjustment in the query.

No need to do ven_data <= @max_ven_data for the @max_ven_data is already the largest possible. So for all table records the condition ven_data <= @max_ven_data would be true. That is, it would simplify the query in this way:

select * from vendas
where ven_data >= (select dateadd(day, -6, max(ven_data)) from vendas);