Orders greater than 30 days

Asked

Viewed 36 times

-1

good afternoon.

I am putting together a report in which I need to report in a column, products that have not had movement for more than 30 days. The field I use is of DATETIME pattern. Every time some drive information is inserted, it is added day and time. Example:

Produto A | OCORRÊNCIA | 01/06/2019 10:00:00
Produto A | OCORRÊNCIA | 01/06/2019 12:00:00
Produto A | OCORRÊNCIA | 01/06/2019 16:00:00

Produto A | OCORRÊNCIA | 10/07/2019 10:00:00

In this case I would need to inform you that product A was more than 30 days without movement. I do not know what I can use to verify this logic, since I have only one field that field to do the manipulation. I tried with having but I couldn’t.

sql

  • Use the MAX aggregation function, together with the GROUP BY product clause, and in the WHERE clause only the maximum dates that are less than the current date - 30 days.

  • It worked, thank you very much!

1 answer

1


SELECT produto FROM movimentacao WHERE data < DATEADD(Day, -30, GETDATE());

I kicked the names of the columns and the table, but here’s the idea... and I don’t know which database you’re using, so the date-summing function might be slightly different.

  • Oops, it worked. Thank you very much. I was wondering if I would need another date field. But now it was!

Browser other questions tagged sql

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