Geeks on Campus

Sometimes you need to find which row of data in a table would have been the latest/greatest/current/effective row as-of a specified date or term.  Describing this in plain language would be something like:

Show me the row with the latest date that is prior to August 17, 2019.
Or
Which row would have been in effect on August 17, 2019?

Here is some example data from the FA Term table:

So, given the question above, we would be looking for the 8/15/19 row.  Of course FA term has delivered MAX Effective Date logic, but that isn’t helpful here and lots of other tables have dates that are not Effective Dates.  Typically when you want to find the MAX date on a non-effective date table you just filter on the date and specify a criteria of equal to Subquery. Then in the subquery you Select the date field and use the Max Aggregate.  But if you try to add a HAVING of Not Greater Than 8/17/19 it won’t return any results. If you try to add that same logic as a criteria at the top level you won’t get any results. So the way that I have found to do this is as follows:

1. Delete automatic effective date criteria if there is any.
2. At the top level of the query filter on the date field and set it Equal to Subquery.
3. In the subquery use the same record as the top level.
4. Specify whichever criteria are needed to limit the selection the the correct group (ID, Term, ect.)
5. Include a criteria on the date (or term) field such as Not Greater Than and specify a constant, a prompt, or another field.
6. Create an expression in the subquery that is the same type and length as the field you are filtering at the top level (ex. date, term).
7. Select the Aggregate Function checkbox.
8. The Expression Text will be “MAX(field)” where field is the field in the subquery record that mirrors the field being filtered at the top level. So for this example it would be “MAX(B.EFFDT)”.
9. Select “Use as Field” for this expression.

The query should now return just the row of data that you are looking for.  Looking at the SQL generated by the initial attempt using the standard Select MAX and the revised query using an expression, the only difference is the final (outer) right parenthesis on the A.EFFDT subquery moves down one line to also encompass the Not Greater Than criteria.

I am 100% sure there are other ways to accomplish this.  I’m pretty sure that I had gotten this to work in the past using another method. But this is what I got to work now and I wanted to document it for the future (and others).

All the best. I hope this helps someone.

Cheers, John @ BGSU

Update: If looking for the effective dated row just prior to the Max effective row try this from Michael K. on HEUG.

To get the second highest effective date, add another copy of the same table to the query (this one would be labeled as B) it will auto add the same effective date and effective sequence criteria. Go to update the B.EFFDT criteria to be EFFDT < (not just < ) A.EFFDT. If done right when looking at the sql it should have the max criteria just like the one above but instead of being < SYSDATE it’ll be < A.EFFDT[.