The only thing I want to add to the great comments/recommendations already given is to recall that TERM_TBL has the SSR_TRMAC_LAST_DT field, which is labeled "Max Program Effdt for Term". Depending on how your institution utilizes this (required) field, it would, in theory, provide the last Effdt that should be considered in a given term.
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the
.
Original Message:
Sent: 07-02-2025 02:19 PM
From: Pheng Xiong
Subject: Query Only Returns Results for Prior Term
Thanks for the helpful suggestions Daniel and Mark. Upon closer look at the query I did find that I did an incorrect join on the instructor view table and did not do a left join the academic standing table. Since this query as an update to an older query written in 2018, I decided to leave the first set of record tables as is after finding the incorrect joins.
Mark - I incorporated your suggestion regarding the CASE statement and this further enhanced the query as you noted in your reply. For instance, this is what I observed when validating a student. The query used to pull in the most recent program plan and with the CASE statement, it is now pulling in the program plan based on the most recent effective date for the specified term.
Older Version Query-
Revised Version of Query-
------------------------------
Pheng Xiong
Senior Associate Registrar
Rochester Institute of Technology
------------------------------
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: 07-02-2025 08:26 AM
From: Mark Miszewski
Subject: Query Only Returns Results for Prior Term
Daniel's response is a great way to ensure pulling the Max Effective Dated rows as of a Term Begin Date. I'd like to offer a few other suggestions:
- First thing I would do is move the join to PS_TERM_TBL out of the subquery and place at the main level, joining on INSTITUTION, ACAD_CAREER, and STRM from PS_STDNT_CAR_TERM. This allows a full join based on the Acad Career they are Term Activated in and only requires one version of PS_TERM_TBL versus having to use in multiple subqueries for other EFFDT clauses.
- I also recommend not using PS_ACAD_PLAN as the table for looking up Max EFFDT. PS_ACAD_PLAN is a child table and since plans can change, you may not want to be including it in your results for a Term that it is no longer associated with the Students Program/Plan stack. PS Query will always try to use Max Effdt clause for the table the EFFDT is on. But, when Parent/Child tables are involved, it is always best to use the Parent table. In this case, PS_ACAD_PROG. That ensures you are only pulling in the Acad_Plan values that are truly in effect as of a given date and not including ones they may have dropped as of a newer effective date.
- Since Academic Program/Plan stack can change mid-term, I would determine if you are only looking for the status as of the beginning of a term or the most current status within a term. If looking for the latter, I tend to use a Case Statement similar to this within the SubQuery assuming PS_TERM_TBL using an alias of T was joined outside the subquery already:
AND A_ED.EFFDT <= CASE WHEN T.TERM_END_DT <= SYSDATE THEN T.TERM_END_DT
WHEN T.TERM_BEGIN_DT >= SYSDATE THEN T.TERM_BEGIN_DT
ELSE SYSDATE END
This statement will pull the latest Effective Date that was in play for a term for any past terms up to the end of that term, use the most current effective dated row in place as of the start of a term for any future terms, and use the current date for any currently active terms.
------------------------------
Mark Miszewski
Senior PeopleSoft Developer
Cornell University
------------------------------
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: 07-01-2025 01:54 PM
From: Daniel Labrecque
Subject: Query Only Returns Results for Prior Term
Hi Pheng,
It looks like the issue you're encountering is related to how effective-dated rows are being pulled in your query specifically, how MAX(EFFDT) and MAX(EFFSEQ) subqueries are used. The current logic is likely filtering for only the most recent record overall for each student, instead of the correct record as of the term you're prompting for.
This is a common issue when querying historical or future data in PeopleSoft. Many effective-dated tables (like PS_ACAD_PLAN, PS_ACAD_PROG, PS_ACAD_STDNG_ACTN, etc.) store a student's progression over time, but unless you scope the effective-dated logic to a particular term, the query defaults to whatever is most current. That's why you're only getting the prior term and it's likely the most recent one with effective rows before today.
Let's take PS_ACAD_PLAN as an example. Instead of this:
AND A.EFFDT = (
SELECT MAX(EFFDT)
FROM PS_ACAD_PLAN
WHERE EMPLID = A.EMPLID
AND ACAD_CAREER = A.ACAD_CAREER
AND STDNT_CAR_NBR = A.STDNT_CAR_NBR
)
Use logic like this instead:
AND A.EFFDT = (
SELECT MAX(EFFDT)
FROM PS_ACAD_PLAN A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= (
SELECT MIN(TERM_BEGIN_DT)
FROM PS_TERM_TBL
WHERE STRM = B.STRM
AND INSTITUTION = B.INSTITUTION
)
)
AND A.EFFSEQ = (
SELECT MAX(EFFSEQ)
FROM PS_ACAD_PLAN A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT
)
This version ensures you're pulling the latest row as of the beginning of the term passed in via your prompt. It corrects the issue where the query would otherwise skip over earlier or future term records just because their effective dates aren't the latest.
I recommend reviewing all the effective-dated joins in your query and replace MAX(EFFDT) logic that is scoped to SYSDATE or global MAX() with logic tied to the STRM you're prompting for. This includes tables like:
- PS_ACAD_PLAN
- PS_ACAD_PROG
- PS_ACAD_STDNG_ACTN
- PS_RIT_SR_APPL_VW
- PS_STDNT_GRPS_HIST
By aligning the effective-dated logic with the term context, you should start seeing historical and future rows returned as expected.
------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada-Las Vegas
------------------------------
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: 07-01-2025 12:10 PM
From: Pheng Xiong
Subject: Query Only Returns Results for Prior Term
I'm working on a query to pull historical and current student course, academic standing, and academic program/plan information. The query prompts for term, but it only returns results for the prior term (i.e.: Spring 2025). It does not return results for older (Fall 2024) or future (Fall 2025) terms. I believe it has some thing to do with the effective dates in the tables that I use in the query. Any help identifying where the break down may be would be greatly appreciated.
Here's the SQL for the query:
SELECT DISTINCT A.EMPLID, G.LAST_NAME, G.FIRST_NAME, J.EMAIL_ADDR, K.EMAIL_ADDR, N.SEX, TO_CHAR(N.BIRTHDATE,'YYYY-MM-DD'), A.ACAD_CAREER, L.ADMIT_TERM, M.STRM_ENRL_MIN, F.CAMPUS, P.STDNT_GROUP, Q.DESCR, A.STDNT_CAR_NBR, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, F.ACAD_PROG, A.ACAD_PLAN, E.ACAD_PLAN_TYPE, O.RIT_MIN_ACAD_PLAN, B.STRM, B.ACAD_LEVEL_BOT, C.CLASS_NBR, D.SUBJECT, D.CATALOG_NBR, D.DESCR, D.CLASS_SECTION, C.UNT_TAKEN, C.STDNT_ENRL_STATUS, C.CRSE_GRADE_OFF, TO_CHAR(C.GRADE_DT,'YYYY-MM-DD'), H.NAME, H.EMPLID, I.EMAIL_ADDR, B.UNT_TAKEN_FA, B.UNT_TAKEN_PRGRSS, B.CUR_GPA, B.CUM_GPA, R.ACAD_STNDNG_ACTN, TO_CHAR(R.ACTION_DT,'YYYY-MM-DD') FROM ((PS_ACAD_PLAN A LEFT OUTER JOIN PS_RITX_EMAIL_ADRV J ON A.EMPLID = J.EMPLID ) LEFT OUTER JOIN PS_RITX_EMAIL_ADRV K ON A.EMPLID = K.EMPLID ), PS_STDNT_CAR_TERM B, PS_STDNT_ENRL C, PS_CLASS_TBL D, PS_ACAD_PLAN_TBL E, PS_ACAD_PROG F, PS_RITX_NAMES_VW G, PS_INSTR_CLASS_VW H, PS_RITX_EMAIL_ADRV I, PS_RIT_SR_APPL_VW L, PS_RIT_RET_DB_VW M, PS_PERS_DATA_SA_VW N, PS_RIT_PROG_PLAN O, PS_STDNT_GRPS_HIST P, PS_STDNT_GROUP_TBL Q, PS_ACAD_STDNG_ACTN R WHERE ( F.EMPLID = A.EMPLID AND F.ACAD_CAREER = A.ACAD_CAREER AND F.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND F.EFFDT = A.EFFDT AND F.EFFSEQ = A.EFFSEQ AND ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PLAN A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PLAN A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.INSTITUTION = C.INSTITUTION AND B.STRM = C.STRM AND C.INSTITUTION = D.INSTITUTION AND C.STRM = D.STRM AND C.CLASS_NBR = D.CLASS_NBR AND A.ACAD_PLAN = E.ACAD_PLAN AND A.EMPLID = G.EMPLID AND B.STRM = :1 AND C.STDNT_ENRL_STATUS <> 'D' AND D.SESSION_CODE = C.SESSION_CODE AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_RITX_NAMES_VW G_ED WHERE G.EMPLID = G_ED.EMPLID AND G.NAME_TYPE = G_ED.NAME_TYPE) AND F.CAMPUS LIKE 'CROAT' AND E.ACAD_PLAN_TYPE IN ('MAJ','CT','NON') AND G.NAME_TYPE = 'PRI' AND D.CRSE_ID = H.CRSE_ID AND D.CRSE_OFFER_NBR = H.CRSE_OFFER_NBR AND D.STRM = H.STRM AND D.SESSION_CODE = H.SESSION_CODE AND D.CLASS_SECTION = H.CLASS_SECTION AND H.CLASS_NBR = D.CLASS_NBR AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_INSTR_CLASS_VW H_ED WHERE H.STRM = H_ED.STRM AND H.EMPLID = H_ED.EMPLID AND H.CLASS_NBR = H_ED.CLASS_NBR AND H_ED.EFFDT <= SYSDATE) AND H.EMPLID = I.EMPLID AND I.E_ADDR_TYPE = 'RIT' AND J.E_ADDR_TYPE = 'RITS' AND K.E_ADDR_TYPE = 'HOME' AND F.EMPLID = L.EMPLID AND F.ACAD_CAREER = L.ACAD_CAREER AND F.STDNT_CAR_NBR = L.STDNT_CAR_NBR AND L.EFFDT = (SELECT MAX(L_ED.EFFDT) FROM PS_RIT_SR_APPL_VW L_ED WHERE L.INSTITUTION = L_ED.INSTITUTION AND L.EMPLID = L_ED.EMPLID AND L.ADM_APPL_NBR = L_ED.ADM_APPL_NBR AND L_ED.EFFDT <= SYSDATE) AND L.EFFSEQ = (SELECT MAX(L_ES.EFFSEQ) FROM PS_RIT_SR_APPL_VW L_ES WHERE L.INSTITUTION = L_ES.INSTITUTION AND L.EMPLID = L_ES.EMPLID AND L.ADM_APPL_NBR = L_ES.ADM_APPL_NBR AND L.EFFDT = L_ES.EFFDT) AND A.EMPLID = M.EMPLID AND A.ACAD_CAREER = M.ACAD_CAREER AND G.EMPLID = N.EMPLID AND F.EMPLID = O.EMPLID AND F.ACAD_CAREER = O.ACAD_CAREER AND F.STDNT_CAR_NBR = O.STDNT_CAR_NBR AND O.EFFDT = (SELECT MAX(O_ED.EFFDT) FROM PS_RIT_PROG_PLAN O_ED WHERE O.EMPLID = O_ED.EMPLID AND O.ACAD_CAREER = O_ED.ACAD_CAREER AND O.STDNT_CAR_NBR = O_ED.STDNT_CAR_NBR AND O_ED.EFFDT <= SYSDATE) AND A.EMPLID = P.EMPLID AND P.EFFDT = (SELECT MAX(P_ED.EFFDT) FROM PS_STDNT_GRPS_HIST P_ED WHERE P.EMPLID = P_ED.EMPLID AND P.INSTITUTION = P_ED.INSTITUTION AND P.STDNT_GROUP = P_ED.STDNT_GROUP AND P_ED.EFFDT <= SYSDATE) AND P.STDNT_GROUP IN ('R09','R10') AND P.INSTITUTION = Q.INSTITUTION AND P.STDNT_GROUP = Q.STDNT_GROUP AND Q.EFFDT = (SELECT MAX(Q_ED.EFFDT) FROM PS_STDNT_GROUP_TBL Q_ED WHERE Q.INSTITUTION = Q_ED.INSTITUTION AND Q.STDNT_GROUP = Q_ED.STDNT_GROUP AND Q_ED.EFFDT <= SYSDATE) AND B.EMPLID = R.EMPLID AND B.ACAD_CAREER = R.ACAD_CAREER AND B.INSTITUTION = R.INSTITUTION AND B.STRM = R.STRM AND R.EFFDT = (SELECT MAX(R_ED.EFFDT) FROM PS_ACAD_STDNG_ACTN R_ED WHERE R.EMPLID = R_ED.EMPLID AND R.ACAD_CAREER = R_ED.ACAD_CAREER AND R.INSTITUTION = R_ED.INSTITUTION AND R.STRM = R_ED.STRM AND R_ED.EFFDT <= SYSDATE) AND R.EFFSEQ = (SELECT MAX(R_ES.EFFSEQ) FROM PS_ACAD_STDNG_ACTN R_ES WHERE R.EMPLID = R_ES.EMPLID AND R.ACAD_CAREER = R_ES.ACAD_CAREER AND R.INSTITUTION = R_ES.INSTITUTION AND R.STRM = R_ES.STRM AND R.EFFDT = R_ES.EFFDT) )) ORDER BY 1
------------------------------
Pheng Xiong
Senior Associate Registrar
Rochester Institute of Technology
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------