MySQL query and PHP coding

I need some help to write a complex MySQL query that will interrogate an asset tracking database.

I've attached details of the database. In summary it is a database of a company's assets. The condition, status, value etc of the asset will be tracked over time. What I'm trying to do is find the most recent status of the assets.

The spreadsheet attached includes all the details of the database along with some sample data and details of what I would expect to see following the SQL query.

Also attached is a screenshot of the design - just so that you can get a clearer picture of what I'm building.

Finally, I've attached the code that I've written to date (that doesn't work!)

What I would like is for you to supply me with the correct php and mySQL code please to that I can display the correct results.