I want to combine two datasets of clinical data. The first dataset, Master, has two key fields: OrdKey and StopKey. The second dataset, Orders, has three key fields; OrdKey, StopKey and Type. Type can be "O", "R" or "S".
The combined dataset is to be called OUTPUT. If the Type = "S", use the STOPKEY as the key field to combine the data from the two tables, otherwise use the ORDKEY.
I have attached a file depicting the the two source tables and the expected output table.
Can someone let me know how to write the join statement?