PS Query & SQL

Good afternoon.  I am reaching out for the first time, in hopes that someone might be able to help.  I am still working on getting through the HEUG series on Peoplesoft Queries.  I was tasked with creating/revising a query that would include the last term a class ran.  Does anyone know what the query is that would have this field? 

Thank you. 

------------------------------
Katie Rolfes
Asst Director, Registration & Academic Planning
University of Baltimore
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hi Katie,

I'm not sure if you mean you are looking to create a new query or if you are looking for a query that has already been written at your school.  There are (I think) multiple tables (records) that have course level data. 

If you are creating from scratch and are just looking for last time a class was offered - you could something like this

SELECT MAX( A.STRM), A.SUBJECT, A.CATALOG_NBR, A.DESCR   FROM PS_CLASS_TBL A   GROUP BY  A.SUBJECT,  A.CATALOG_NBR,  A.DESCR

If you are trying to add the last time a course was offered into an existing query - you might want to look at doing a subquery within your query. This would go to a table ... find the last time it was used and bring it back into your query - bringing only 1 row per course... then you could limit your data further. Example below

SELECT A.STRM, A.SUBJECT, A.CATALOG_NBR, A.DESCR   FROM PS_CLASS_TBL A   WHERE ( A.STRM = (SELECT MAX( B.STRM)   FROM PS_CLASS_TBL B   WHERE A.SUBJECT = B.SUBJECT      AND A.CATALOG_NBR = B.CATALOG_NBR))

I am happy to meet up with you to discuss in more detail if you would like.... 

Cheers!

Steph

------------------------------
Steph Zimmerman
IT Tech Associate
Illinois State 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: 3/18/2025 12:32:00 PM
From: Steph Zimmerman
Subject: RE: Specific Query

Hi Katie,

I'm not sure if you mean you are looking to create a new query or if you are looking for a query that has already been written at your school.  There are (I think) multiple tables (records) that have course level data. 

If you are creating from scratch and are just looking for last time a class was offered - you could something like this

SELECT MAX( A.STRM), A.SUBJECT, A.CATALOG_NBR, A.DESCR   FROM PS_CLASS_TBL A   GROUP BY  A.SUBJECT,  A.CATALOG_NBR,  A.DESCR

If you are trying to add the last time a course was offered into an existing query - you might want to look at doing a subquery within your query. This would go to a table ... find the last time it was used and bring it back into your query - bringing only 1 row per course... then you could limit your data further. Example below

SELECT A.STRM, A.SUBJECT, A.CATALOG_NBR, A.DESCR   FROM PS_CLASS_TBL A   WHERE ( A.STRM = (SELECT MAX( B.STRM)   FROM PS_CLASS_TBL B   WHERE A.SUBJECT = B.SUBJECT      AND A.CATALOG_NBR = B.CATALOG_NBR))

I am happy to meet up with you to discuss in more detail if you would like.... 

Cheers!

Steph

------------------------------
Steph Zimmerman
IT Tech Associate
Illinois State University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------