I do an equal to subquery and select the date field, select max on the edit for the field, do your normal joins.
Here's an example for STDNT_AWRD_ACTV:
SELECT A.EMPLID, TO_CHAR(CAST((A.ACTION_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.ITEM_TYPE, A.ACAD_CAREER
FROM PS_STDNT_AWRD_ACTV A
WHERE ( A.ACTION_DTTM = (SELECT MAX( B.ACTION_DTTM)
FROM PS_STDNT_AWRD_ACTV B
WHERE B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.AID_YEAR = A.AID_YEAR
AND B.ITEM_TYPE = A.ITEM_TYPE
AND B.ACAD_CAREER = A.ACAD_CAREER)
AND A.AID_YEAR = '2025')
------------------------------
Dana Pawlowicz
Business Systems Analyst Sr - ERP
University of Cincinnati
------------------------------
Message from the HEUG Marketplace:------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the
HEUG Marketplace.
------------------------------
Original Message:
Sent: 01-16-2025 05:09 PM
From: Shawn Massey
Subject: How do you find a "Max date"?
The issue arises when something that doesn't have an effective date field has multiple rows returned with same information but different dates.
What PeopleSoft Friendly expression or solution have you found usable in Query Manager?
------------------------------
Shawn Massey
Financial System Analyst
University of Oklahoma
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------