Selecting the most recent date from a table in PeopleSoft using Peoplesoft Query (Max() doesn't work)

Question

I am building a query in people soft using Peoplesoft query manager.

I am trying to pull the most recent date from the date column. I have tried using max() as an expression, however, the query doesn't pull any records. I have checked with another co-worker and they have never been able to pull records using max().

Is there any other way or workaround to pull the most recent record?

Answer 1

So I figured out why no results were returned when using Max in a subquery. It was more from a lack of understanding PeopleSoft and SQL since I am relatively new to it. When I was setting the date column in the subquery as max for the aggregate to be used as criteria to compare to the date column in the main query I didn't make any criteria in the subquery. This meant that the subquery would go through all dates for all employees except for the employee that I was specifying in a prompt and returning a value that didn't match any of the dates for the employee in the main query and returning no one. This was fixed by setting a criteria in the subquery that the employee ID that had to be searched in the subquery matched the one that was typed into the prompt in the main query

Answer 2

Use effective date for doing such searches while using PSQuery.

Answer 3

Use Effective date in order to get the most recent date, max may not work properly in PeopleSoft. Query should be effective dated

Answer 4

PS Query has built in filters for EFFDT tables. When you add a criteria on the EFFDT field, there are some additional drop down choices on the "condition type" field like 'Eff Date <' and 'Eff Date <=', etc. Usually, when you create a query for an Effective dated table, PS Query will automatically add the subquery based on the 'Eff Date <=' condition type.