I'm new to query building and trying to write a query to determine the timespan between when an applicant is offered a seat in a program until they make a decision or are withdrawn for inaction. The two dates I need to pull are the last incidence of an ADMT Program Action in the history and the first incidence of a DEFR/DEIN/WADM/WAPP Program Action after that last ADMT row.
Examples:

Query will pull the ADMT row from 6/15/2016 and DEFR row from 7/1/2016

My query works exactly as I would expect it to for the ADMT rows, but it is returning the last DEFR/DEIN/WADM/WAPP row for those Program Actions. So, for Example 1, it is pulling the WADM row from 8/1/2016 and for Example 2, it is pulling the WAPP row from 7/1/2016.

The functional analyst who has been assisting me instructed me that I needed to create subqueries using min EFFDT and min EFFSEQ values for the DEIN/DEFR/WADM/WAPP program actions after the effective date for the last ADMT row. I have created subqueries that replicate the SQL she gave me, but it is not working. Can someone please tell me what I need to do to make this work? Here is the SQL from my current query:

Thank you for your response! "Working" for me would mean that the "Subsequent Effective Date" column in my query (created via the Query Manager module within PS Student Administration), which uses D.PROG_ACTION, is populating with a date representing the minimum EFFDT with the minimum EFFSEQ for listed statuses for D.PROG_ACTION, which also has to be greater than or equal to the maximum EFFDT of a specific status for A.PROG_ACTION.

To explain using more general business process, if I admit someone for the final time on X date, my query needs to pull the date when they subsequently paid their tuition deposit (which will always be equal to or later than the admit date), not the day they were withdrawn for failing to pay the balance of that tuition (will always be greater than or equal to the deposit date). For this scenario, my query is currently pulling the row for day the person was withdrawn for failing to pay tuition, not the day of the tuition deposit. This is happening despite the presence of the MIN attributes through subquery, which was recommended (and written up for me to replicate via Query Manager) by my functional resource person, which is why I am perplexed.

You still have not provided create table or insert statements for your sample data. Since you are new, I have provided an example of that below with the minimal data that you posted. I have also provided a sample query based on that data that provides the results that your initial post requested based on that data. I have tried to make it as generic as possible, so that you can add whatever other columns that you need to join by or wish to display, without affecting the rest of the functionality. I have also indicated where you would need to provide some column aliases, such as using a prefix for same column names in the inner sub-query resulting from the self-join, then use those same aliases in the outer query. This is just one method. There are many other ways that you could achieve the same results.