Hey everyone... I need your help writting a query to get the following outcome. Please see the jpg attatched. All your help is very much appreciated!

The EVAL, SUSP, CANC, and WO tables might not be needed. They are generated from the MASTER so if you can do it with just the MASTER, whatever is easier.
Attached Images
File Type: jpg Table Diagram.JPG (50.6 KB, 10 views)