Sounds like homework...

Would you please show us what you've tried so far and where you get stuck?

To refer to the most recent row, you could either use an outer join or try the CROSS APPLY approach (if you're familiar with it).

A good starting point would be ready to use table definition and some sample data as described in the first link in my signature...