PS Query & SQL

 View Only
Alliance 2026 | March 8-11, 2026 | Orlando, FL
Back to discussions
Expand all | Collapse all

2nd most recent row for a table with effective date and effective sequence

  • 1.  2nd most recent row for a table with effective date and effective sequence

    Posted 18 days ago

    Does anyone have a good way to get the the most recent and the 2nd most recent row of a table with effective date and effective sequence?  It would be easy if they were different effective dates (and some are), but some also are the same effective date and have a different effective sequence.



    ------------------------------
    Scott Cho
    ------------------------------
    Alliance 2026 Recordings are here!


  • 2.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 18 days ago
    Hi Scott,

    I am sure there are more sophisticated ways to go about doing this, but I have had some success by converting the EFFDATE to a Julian value, then concatenating the EFFSEQ at the end of if after a decimal point. So something like this:

    TO_CHAR(TO_DATE(B.EFFDT),'J') | | '.' | | B.EFFSEQ

    You end up having to create the expression 3 times though:
    • Once for the MAX eff/seq row
    • Once for a second table that will be used to determine the desired penultimate eff date/seq
    • Once for a subquery to compare to the MAX effective dated/seq row indicated above
    Here is an example of how it works at the SQL level using STDNT_TERM_BDGT:

    SELECT A.EMPLID, A.AID_YEAR, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.EFFSEQ
      FROM PS_STDNT_TERM_BDGT A, PS_STDNT_TERM_BDGT B
      WHERE ( A.EFFDT =
            (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_TERM_BDGT A_ED
            WHERE A.EMPLID = A_ED.EMPLID
              AND A.INSTITUTION = A_ED.INSTITUTION
              AND A.AID_YEAR = A_ED.AID_YEAR
              AND A.ACAD_CAREER = A_ED.ACAD_CAREER
              AND A.STRM = A_ED.STRM
              AND A_ED.EFFDT <= SYSDATE)
        AND A.EFFSEQ =
            (SELECT MAX(A_ES.EFFSEQ) FROM PS_STDNT_TERM_BDGT A_ES
            WHERE A.EMPLID = A_ES.EMPLID
              AND A.INSTITUTION = A_ES.INSTITUTION
              AND A.AID_YEAR = A_ES.AID_YEAR
              AND A.ACAD_CAREER = A_ES.ACAD_CAREER
              AND A.STRM = A_ES.STRM
              AND A.EFFDT = A_ES.EFFDT)
         AND A.EMPLID = B.EMPLID
         AND A.INSTITUTION = B.INSTITUTION
         AND A.AID_YEAR = B.AID_YEAR
         AND A.ACAD_CAREER = B.ACAD_CAREER
         AND A.STRM = B.STRM
         AND TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ = (SELECT MAX(TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ)
            FROM PS_STDNT_TERM_BDGT C
            WHERE C.EMPLID = A.EMPLID
                AND C.INSTITUTION = A.INSTITUTION
                AND C.AID_YEAR = A.AID_YEAR
                AND C.ACAD_CAREER = A.ACAD_CAREER
                AND C.STRM = A.STRM
                AND TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ < TO_CHAR(TO_DATE( A.EFFDT),'J') | | '.' | |  A.EFFSEQ))

    Let me know if you have any questions!

    Jeffrie
    --
    Jeffrie Brooks | BUSINESS SYSTEM ANALYST
    UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
    734-647-8763 | jedobr@umich.edu



    Original Message
    Alliance 2026 Recordings are here!


  • 3.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 18 days ago
    I will also add that the conversion to Julian might be totally unnecessary, but it helped me to visualize and conceptualize the solution.

    Jeffrie Brooks | BUSINESS SYSTEM ANALYST
    UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
    734-647-8763 | jedobr@umich.edu



    Original Message
    Alliance 2026 Recordings are here!


  • 4.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago

    Thanks Jeffrie,

     

    FYI – I think there is a typo in your SQL:

     

     

    SELECT A.EMPLID, A.AID_YEAR, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), B.EFFSEQ
      FROM PS_STDNT_TERM_BDGT A, PS_STDNT_TERM_BDGT B
      WHERE ( A.EFFDT =
            (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_TERM_BDGT A_ED
            WHERE A.EMPLID = A_ED.EMPLID
              AND A.INSTITUTION = A_ED.INSTITUTION
              AND A.AID_YEAR = A_ED.AID_YEAR
              AND A.ACAD_CAREER = A_ED.ACAD_CAREER
              AND A.STRM = A_ED.STRM
              AND A_ED.EFFDT <= SYSDATE)
        AND A.EFFSEQ =
            (SELECT MAX(A_ES.EFFSEQ) FROM PS_STDNT_TERM_BDGT A_ES
            WHERE A.EMPLID = A_ES.EMPLID
              AND A.INSTITUTION = A_ES.INSTITUTION
              AND A.AID_YEAR = A_ES.AID_YEAR
              AND A.ACAD_CAREER = A_ES.ACAD_CAREER
              AND A.STRM = A_ES.STRM
              AND A.EFFDT = A_ES.EFFDT)
         AND A.EMPLID = B.EMPLID
         AND A.INSTITUTION = B.INSTITUTION
         AND A.AID_YEAR = B.AID_YEAR
         AND A.ACAD_CAREER = B.ACAD_CAREER
         AND A.STRM = B.STRM
         AND TO_CHAR(TO_DATE( B.EFFDT),'J') | | '.' | |  B.EFFSEQ = (SELECT MAX(TO_CHAR(TO_DATE(     C.EFFDT),'J') | | '.' | |  C.EFFSEQ)
            FROM PS_STDNT_TERM_BDGT C
            WHERE C.EMPLID = A.EMPLID
                AND C.INSTITUTION = A.INSTITUTION
                AND C.AID_YEAR = A.AID_YEAR
                AND C.ACAD_CAREER = A.ACAD_CAREER
                AND C.STRM = A.STRM
                AND TO_CHAR(TO_DATE(     C.EFFDT),'J') | | '.' | |  C.EFFSEQ < TO_CHAR(TO_DATE( A.EFFDT),'J') | | '.' | |  A.EFFSEQ))

     

     

     

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 5.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago
    Oops, good catch - your correction is 100% what I intended to write. 

    Alas, Taco Tuesday was beckoning me last night, and in my haste to consume seasoned beef, I missed that crucial alias.

    Sorry about that!

    Jeffrie

    --
    Jeffrie Brooks | BUSINESS SYSTEM ANALYST
    UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES
    734-647-8763 | jedobr@umich.edu



    Original Message
    Alliance 2026 Recordings are here!


  • 6.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago

    No worries.  Taco Tuesday is always going to get the best of us!  I had a similar idea to yours but I couldn't get the formatting of the effective dates / effective seq in query to work, but I didn't even know about the Julian conversion.  That was genius!

     

    Thanks again.

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 7.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 16 days ago

    I just used NTH value to try to get multiple degrees onto our NameCards for Commencement.  Seems to be working.  Here are my expressions:



    ------------------------------
    Lee Raubolt
    Associate Director of Admissions and Records
    Truckee Meadows Community College
    ------------------------------

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

    Original Message
    Alliance 2026 Recordings are here!


  • 8.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 18 days ago
    This is the SQL from a PS Query I have which checks the current row and the immediately previous row for particular values.  Following the SQL are screen shots of the run control record and parameters which I am using.  The term range referred to is coming from the File Parser Conversion Definition table.  Given some of the complexity, we built a view (DU_ADMA_CURPREV) to assist and that SQL follows the screen shots.

    Hope this helps you.

    SELECT A.EMPLID, A.INSTITUTION, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ADM_APPL_NBR, A.APPL_PROG_NBR, B.NAME
      FROM PS_SAD_APL_PRG_BND A, PS_SCC_NAMES_QVW B, PS_DU_ADMA_CURPREV C, PS_TERM_TBL F
      WHERE ( A.EMPLID = B.EMPLID
         AND B.EFFDT =
            (SELECT MAX(B_ED.EFFDT) FROM PS_SCC_NAMES_QVW B_ED
            WHERE B.EMPLID = B_ED.EMPLID
              AND B.NAME_TYPE = B_ED.NAME_TYPE
              AND B_ED.EFFDT <= SYSDATE)
         AND B.NAME_TYPE = 'PRI'
         AND A.EMPLID = C.EMPLID
         AND A.ACAD_CAREER = C.ACAD_CAREER
         AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR
         AND A.ADM_APPL_NBR = C.ADM_APPL_NBR
         AND A.APPL_PROG_NBR = C.APPL_PROG_NBR
         AND C.EFFDT =
            (SELECT MAX(C_ED.EFFDT) FROM PS_DU_ADMA_CURPREV C_ED
            WHERE C.EMPLID = C_ED.EMPLID
              AND C.ACAD_CAREER = C_ED.ACAD_CAREER
              AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR
              AND C.ADM_APPL_NBR = C_ED.ADM_APPL_NBR
              AND C.APPL_PROG_NBR = C_ED.APPL_PROG_NBR
              AND C_ED.EFFDT <= SYSDATE)
        AND C.EFFSEQ =
            (SELECT MAX(C_ES.EFFSEQ) FROM PS_DU_ADMA_CURPREV C_ES
            WHERE C.EMPLID = C_ES.EMPLID
              AND C.ACAD_CAREER = C_ES.ACAD_CAREER
              AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR
              AND C.ADM_APPL_NBR = C_ES.ADM_APPL_NBR
              AND C.APPL_PROG_NBR = C_ES.APPL_PROG_NBR
              AND C.EFFDT = C_ES.EFFDT)
         AND A.INSTITUTION = :1
         AND A.ACAD_CAREER = :2
         AND C.ADMIT_TERM >= (SELECT D.SCCFP_PSFT_VALUE
      FROM PS_SCCFP_CNVR_VAL D
      WHERE D.SCCFP_CNVR_ID = 10005
         AND D.SCCFP_CNVR_FIELD = 'AUTO_MATRIC_TERM_RANGE'
         AND D.SCCFP_FILE_VALUE = 'START_TERM')
         AND C.ADMIT_TERM <= (SELECT E.SCCFP_PSFT_VALUE
      FROM PS_SCCFP_CNVR_VAL E
      WHERE E.SCCFP_CNVR_ID = 10005
         AND E.SCCFP_CNVR_FIELD = 'AUTO_MATRIC_TERM_RANGE'
         AND E.SCCFP_FILE_VALUE = 'END_TERM')
         AND A.INSTITUTION = :3
         AND C.PROG_ACTION = :4
         AND C.PROG_REASON = :5
         AND C.PREV_PROG_ACTION = :6
         AND C.PREV_PROG_REASON = :7
         AND A.INSTITUTION = F.INSTITUTION
         AND A.ACAD_CAREER = F.ACAD_CAREER
         AND C.ADMIT_TERM = F.STRM
         AND SYSDATE <= F.TERM_BEGIN_DT)




    SQL from View DU_ADMA_CURPREV

    SELECT emplid
     ,acad_career
     ,stdnt_car_nbr
     ,adm_appl_nbr
     ,appl_prog_nbr
     ,effdt
     ,effseq
     ,admit_term
     ,prog_action
     ,prog_reason
     ,prev_effdt
     ,prev_effseq
     ,prev_prog_action
     ,prev_prog_reason
     ,therownbr
      FROM (
     SELECT emplid
     ,acad_career
     ,stdnt_car_nbr
     ,adm_appl_nbr
     ,appl_prog_nbr
     ,effdt
     ,effseq
     ,admit_term
     ,prog_action
     ,prog_reason
     ,lead (effdt
     , 1) over (partition BY emplid
     , acad_career
     , stdnt_car_nbr
     , adm_appl_nbr
     , appl_prog_nbr
      ORDER BY effdt DESC
      , effseq DESC) AS prev_effdt
      ,lead (effseq
      , 1
      , 0) over (partition BY emplid
      , acad_career
      , stdnt_car_nbr
      , adm_appl_nbr
      , appl_prog_nbr
      ORDER BY effdt DESC
      , effseq DESC) AS prev_effseq
      ,lead (prog_action
      , 1
      , 0) over (partition BY emplid
      , acad_career
      , stdnt_car_nbr
      , adm_appl_nbr
      , appl_prog_nbr
      ORDER BY effdt DESC
      , effseq DESC) AS prev_prog_action
      ,lead (prog_reason
      , 1
      , 0) over (partition BY emplid
      , acad_career
      , stdnt_car_nbr
      , adm_appl_nbr
      , appl_prog_nbr
      ORDER BY effdt DESC
      , effseq DESC) AS prev_prog_reason
      ,row_number() over (partition BY emplid
      , acad_career
      , stdnt_car_nbr
      , adm_appl_nbr
      , appl_prog_nbr
      ORDER BY effdt DESC
      , effseq DESC) AS therownbr
      FROM ps_adm_appl_prog)

    David Ehrlich | Senior Business System Analyst

    Duke University | Student Information Services & System (SISS)

    david.ehrlich@duke.edu | 919-684-1206

    www.sissoffice.duke.edu





    Original Message
    Alliance 2026 Recordings are here!


  • 9.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago

    Thanks David.  Am I reading this right and the logic for the comparison of most recent and the 2nd most recent effective date/eff seq tables are the in view: PS_DU_ADMA_CURPREV?

     

     

    Scott Cho

    PeopleSoft Campus Solutions Consultant

    630/384-9711

     

     




    Original Message
    Alliance 2026 Recordings are here!


  • 10.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago
    That view brings back data from the current row and the previous row - flattening it into one row, thru the entire history of the AD stack.  Screen shot of one entry is below.  You can see that as it goes back in time that the initial stack entry has "nothing" for a previous (row 5 in this example).

    The evaluation of what I need for my process to matric happens in the first set of SQL in my initial email.



    David Ehrlich | Senior Business System Analyst

    Duke University | Student Information Services & System (SISS)

    david.ehrlich@duke.edu | 919-684-1206

    www.sissoffice.duke.edu





    Original Message
    Alliance 2026 Recordings are here!


  • 11.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 17 days ago

    Scott, do you want them as individual rows or flattened?  The NTH_VALUE() function could be quite useful here. 

    NTH_VALUE(YOUR_FIELD, 2) OVER(PARTITION BY <bunch of stuff, probably the keys of all other records> ORDER BY EFFDT DESC, EFFSEQ DESC) would get you the values in YOUR_FIELD on the 2nd EFFDT/SEQ combination.  



    ------------------------------
    Scott Nishizaki
    Connected Campus Community of Practice
    Developer/Analyst
    Azusa Pacific 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
    Alliance 2026 Recordings are here!


  • 12.  RE: 2nd most recent row for a table with effective date and effective sequence

    Posted 16 days ago

    Once upon a time I found this sql function that I only used for fun and I am not an SQL expert. Hope this helps

    The LAG() Function

    https://www.datacamp.com/tutorial/sql-lag



    ------------------------------
    Sofiane Benzaza
    Peoplesoft Analyst
    HEC Montreal
    ------------------------------

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

    Original Message
    Alliance 2026 Recordings are here!


Related Content