Returning all rows from table that match certain criteria

I need to retrieve information from multiple rows in a table based on certain criteria (date compared to today's date, existence of "no" in a column).

Problem and solution I would like attached. Many thanks.