Query by Max Date Google Spreadsheet

Question

I'm working on a spreadsheet that records logistics reports via a google form. We have 100 locations and logistics personal visit and report on condition of each location, i.e. the location needs maintenance etc.

We have 3 categories of location: Priority locations that need to be visited daily, secondary locations that need to be visited a minimum every other day, and other locations that we aim to visit a minimum of every 3 days.

Each logistics visit is recorded in the spreadsheet via the form it captures:

Column A = Time Stamp,  
Column B = Location,  
Column C = Damage Yes/No,  
Column D = Damage Description,  
Column E = Personnel ID,  

I want to create a query that shows only the last visit for each location where that date is older than 3 days. If it's been visited to day or yesterday I don't want it to show up, and as we visit each location multiple times I only want to show 1 entry for each location, the latest entry.

This is what I've attempted:

=Query(LogisticsReports, " SELECT A, B, J WHERE A < TODAY()-3 ",1)

Answer 1

Short answer

Try

=QUERY(
   LogisticsReports,
   "SELECT A,B,J WHERE A < date '"&TEXT(TODAY()-3,"yyyy-mm-dd")&"'",
   1
)

Explanation

QUERY use Google Visualization API Query Language. It handles dates differently than Google Sheets, so the result of functions that returns a date should be convert to a string having yyyy-mm-dd date format and have to be preceded by the date keyword.