PS Query & SQL

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

Minimum Plan Sequence

  • 1.  Minimum Plan Sequence

    Posted 02-24-2025 11:21 AM

    Hello –

     

    I'm not sure what I'm missing here, or if I am just having a bad day πŸ˜‰. Can anyone share how to only return a plan from the ACAD_PLAN table on a student record that has the minimum sequence value? Thanks in advance!

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     

    Alliance 2026 Registration is Open!


  • 2.  RE: Minimum Plan Sequence

    Posted 02-24-2025 12:43 PM

    Hello Rachel,

    I think you can change the EFFDT criteria to:

    By default it is set to Current Date (EffSeq = Last).



    ------------------------------
    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
    Alliance 2026 Registration is Open!


  • 3.  RE: Minimum Plan Sequence

    Posted 02-24-2025 12:51 PM

    Sorry, I should have been more clear. If a student has more than one plan, I want the one where, for this single career number, the PLAN_SEQUENCE is the minimum sequence. The dates there (for declare date or eff date) don't always equate the minimum plan sequence number. For example, I want to return ONLY the ACCBBA2 plan code, because it's the minimum sequence number.

     

     

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 4.  RE: Minimum Plan Sequence

    Posted 02-24-2025 01:01 PM

    I think what you are wanting to use is a subquery on PLAN_SEQUENCE.   In the subquery you will do your joins as usual, but will select the PLAN_SEQUENCE field and make that minimum.



    ------------------------------
    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
    Alliance 2026 Registration is Open!


  • 5.  RE: Minimum Plan Sequence

    Posted 02-24-2025 01:13 PM

    And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out.

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 6.  RE: Minimum Plan Sequence

    Posted 02-24-2025 01:22 PM

    Rachel,

    I built a version using my subquery and did not get the error.    Here the SQL for it:

    SELECT A.emplid,
       A.acad_career,
       A.stdnt_car_nbr,
       To_char(A.effdt, 'YYYY-MM-DD'),
       A.effseq,
       A.acad_plan,
       To_char(A.declare_dt, 'YYYY-MM-DD'),
       A.plan_sequence
FROM   ps_acad_plan A
WHERE  ( 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_ED.effdt <= sysdate)
         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.plan_sequence = (SELECT Min(B.plan_sequence)
                                FROM   ps_acad_plan B
                                WHERE  B.effdt = (SELECT Max(B_ED.effdt)
                                                  FROM   ps_acad_plan B_ED
                                                  WHERE  B.emplid = B_ED.emplid
                                                         AND B.acad_career =
                                                             B_ED.acad_career
                                                         AND B.stdnt_car_nbr =
                                                             B_ED.stdnt_car_nbr
                                                         AND B_ED.effdt <=
                                                             sysdate)
                                       AND B.effseq = (SELECT Max(B_ES.effseq)
                                                       FROM   ps_acad_plan B_ES
                                                       WHERE
                                           B.emplid = B_ES.emplid
                                           AND B.acad_career =
                                               B_ES.acad_career
                                           AND B.stdnt_car_nbr =
                                               B_ES.stdnt_car_nbr
                                           AND B.effdt = B_ES.effdt)
                                       AND B.emplid = A.emplid
                                       AND B.acad_career = A.acad_career
                                       AND B.stdnt_car_nbr = A.stdnt_car_nbr
                                       AND B.effdt = A.effdt
                                       AND B.effseq = A.effseq) )

    If you need screenshots, I can provide those as well.



    ------------------------------
    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
    Alliance 2026 Registration is Open!


  • 7.  RE: Minimum Plan Sequence

    Posted 02-24-2025 03:12 PM

    In case the other SQL doesn't format correctly:

    SELECT 
      A.emplid, 
      A.acad_career, 
      A.stdnt_car_nbr, 
      To_char(A.effdt, 'YYYY-MM-DD'), 
      A.effseq, 
      A.acad_plan, 
      To_char(A.declare_dt, 'YYYY-MM-DD'), 
      A.plan_sequence 
    FROM 
      ps_acad_plan A 
    WHERE 
      (
        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_ED.effdt <= sysdate
        ) 
        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.plan_sequence = (
          SELECT 
            Min(B.plan_sequence) 
          FROM 
            ps_acad_plan B 
          WHERE 
            B.effdt = (
              SELECT 
                Max(B_ED.effdt) 
              FROM 
                ps_acad_plan B_ED 
              WHERE 
                B.emplid = B_ED.emplid 
                AND B.acad_career = B_ED.acad_career 
                AND B.stdnt_car_nbr = B_ED.stdnt_car_nbr 
                AND B_ED.effdt <= sysdate
            ) 
            AND B.effseq = (
              SELECT 
                Max(B_ES.effseq) 
              FROM 
                ps_acad_plan B_ES 
              WHERE 
                B.emplid = B_ES.emplid 
                AND B.acad_career = B_ES.acad_career 
                AND B.stdnt_car_nbr = B_ES.stdnt_car_nbr 
                AND B.effdt = B_ES.effdt
            ) 
            AND B.emplid = A.emplid 
            AND B.acad_career = A.acad_career 
            AND B.stdnt_car_nbr = A.stdnt_car_nbr 
            AND B.effdt = A.effdt 
            AND B.effseq = A.effseq
        )
      )



    ------------------------------
    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
    Alliance 2026 Registration is Open!


  • 8.  RE: Minimum Plan Sequence

    Posted 02-24-2025 01:25 PM
    Hi Rachael,

    That error usually comes from having criteria set in the CRITERIA tab for a field that you have defined as an aggregate. If you look at your CRITERIA tab and see something like this:

    image.png

    That is not normally allowed. There are a couple of different ways that this usually happens:

    1. Check your expressions for the aggregate flag. If you are using one of the aggregate functions, its important that this flag is checked so that the query knows how to group things properly, and put your criteria in the HAVING clause.
    2. Check your FIELDS tab for any aggregate values. These create hidden errors and the MAX(), MIN(), SUM() etc. values aren't shown in the criteria tab. BUT if you view the SQL, you can see that they are written out in the SQL. Because of this, I tend not to define aggregates in the FIELDS tab, and instead define them in the expressions tab most of the time. This happens more often with Subqueries.
    TL;DR Try to define your MIN() sequence number in an expression and use that as your field returned in the subquery instead of defining the aggregate in the FIELDS tab.

    Thanks!

    Jeffrie

    On Mon, Feb 24, 2025 at 1:13β€―PM Rachael Daniel via Higher Education User Group <Mail@heug.org> wrote:
    And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out. Rachael Daniel ...
    Higher Education User Group

    PS Query & SQL

    Post New Discussion
    Re: Minimum Plan Sequence
    Reply to Thread
    Feb 24, 2025 1:13 PM
    Rachael Daniel

    And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out.

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     
      Reply to Community   Reply to Sender via Email   View Thread   Recommend  

    Original Message



     
    You are subscribed to "PS Query & SQL" as jedobr@umich.edu. To change your subscriptions, go to My Subscriptions. To remove yourself from this community discussion, you can unsubscribe at any time.
    Alliance 2025 Registration Still Open!



    Original Message:
    Sent: 2/24/2025 1:13:00 PM
    From: Rachael Daniel
    Subject: RE: Minimum Plan Sequence

    And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out.

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message:
    Sent: 2/24/2025 1:01:00 PM
    From: Daniel Labrecque
    Subject: RE: Minimum Plan Sequence

    I think what you are wanting to use is a subquery on PLAN_SEQUENCE.   In the subquery you will do your joins as usual, but will select the PLAN_SEQUENCE field and make that minimum.



    ------------------------------
    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: 02-24-2025 12:50 PM
    From: Rachael Daniel
    Subject: Minimum Plan Sequence

    Sorry, I should have been more clear. If a student has more than one plan, I want the one where, for this single career number, the PLAN_SEQUENCE is the minimum sequence. The dates there (for declare date or eff date) don't always equate the minimum plan sequence number. For example, I want to return ONLY the ACCBBA2 plan code, because it's the minimum sequence number.

     

     

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message:
    Sent: 2/24/2025 12:43:00 PM
    From: Daniel Labrecque
    Subject: RE: Minimum Plan Sequence

    Hello Rachel,

    I think you can change the EFFDT criteria to:

    By default it is set to Current Date (EffSeq = Last).



    ------------------------------
    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: 02-24-2025 11:20 AM
    From: Rachael Daniel
    Subject: Minimum Plan Sequence

    Hello –

     

    I'm not sure what I'm missing here, or if I am just having a bad day πŸ˜‰. Can anyone share how to only return a plan from the ACAD_PLAN table on a student record that has the minimum sequence value? Thanks in advance!

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     

    Alliance 2026 Registration is Open!


  • 9.  RE: Minimum Plan Sequence

    Posted 02-25-2025 06:10 AM

    Rachel,

    This error will occur when you have criteria on the field that you use in the min aggregation. You probably have something in your sub query criteria on X.PLAN_SEQUENCE. The work around is to create an expression of X.PLAN_SEQUENCE then use as field and edit to Min

    Hope this helps



    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania 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
    Alliance 2026 Registration is Open!


  • 10.  RE: Minimum Plan Sequence

    Posted 02-25-2025 07:34 AM

    This field is an aggregate field but is being used in non-having criteria error happens when the field being used in the sub query aggregate is being used in the criteria tab.

    The work around is to create the field as an expression,

     
    then use the expression as as field from the Expressions Tab
     
    then apply your aggregation ... you will notice that there is no description now from the Record.Fieldname because it is an expression


    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania 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
    Alliance 2026 Registration is Open!


  • 11.  RE: Minimum Plan Sequence

    Posted 02-25-2025 10:11 AM
    To add on to what Robert is saying, you could take this solution a step further and define the aggregate within the expression, instead of doing it at the FIELD tab. So it would look like this:

    image.png

    I strongly recommend doing it this way, instead of defining aggregates in the FIELD tab. This is because it can get real buggy if you try to use that same field for any criteria in the query later on. It's pretty insidious too, since the only way to really know why the error is happening is to look at the SQL! 

    The example below shows what is happens when this error occurs:

    image.png


    You could say that the PSQuery just isn't having it, when this error occurs... but then your friend will groan and call you a nerd...

    On flip-side, if you were to do the same thing, but instead define the aggregate within the expression (And NOT add the aggregate in the FIELDS tab), PSQuery will be smart enough to write the correct SQL statement and put your aggregated field nicely into the HAVING clause, and no error should be thrown.

    Ultimately, understanding what the heck is going on when working with aggregates is hard, and when PSQuery starts writing the underlying SQL in unexpected ways, it just makes it that much harder.

    Hoping this helps!

    Jeffrie






    On Tue, Feb 25, 2025 at 7:34β€―AM Robert Fogarty via Higher Education User Group <Mail@heug.org> wrote:
    This field is an aggregate field but is being used in non-having criteria error happens when the field being used in the sub query aggregate is... -posted to the "PS Query & SQL" group
    Higher Education User Group

    PS Query & SQL

    Post New Discussion
    Re: Minimum Plan Sequence
    Reply to Thread
    Feb 25, 2025 7:34 AM
    Robert Fogarty

    This field is an aggregate field but is being used in non-having criteria error happens when the field being used in the sub query aggregate is being used in the criteria tab.

    The work around is to create the field as an expression,

     
    then use the expression as as field from the Expressions Tab
     
    then apply your aggregation ... you will notice that there is no description now from the Record.Fieldname because it is an expression


    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania State University
    ------------------------------

    Message from the HEUG Marketplace:
    ------------------------------
    Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
    ------------------------------
      Reply to Community   Reply to Sender via Email   View Thread   Recommend  

    Original Message



     
    You are subscribed to "PS Query & SQL" as jedobr@umich.edu. To change your subscriptions, go to My Subscriptions. To remove yourself from this community discussion, you can unsubscribe at any time.
    Alliance 2025 Registration Still Open!


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



    Original Message:
    Sent: 2/25/2025 7:34:00 AM
    From: Robert Fogarty
    Subject: RE: Minimum Plan Sequence

    This field is an aggregate field but is being used in non-having criteria error happens when the field being used in the sub query aggregate is being used in the criteria tab.

    The work around is to create the field as an expression,

     
    then use the expression as as field from the Expressions Tab
     
    then apply your aggregation ... you will notice that there is no description now from the Record.Fieldname because it is an expression


    ------------------------------
    Robert Fogarty PMP
    Reporting Team Lead | LionPATH Development and Maintenance Office
    The Pennsylvania 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: 02-24-2025 01:12 PM
    From: Rachael Daniel
    Subject: Minimum Plan Sequence

    And every time I try to set that to MIN, I get this. So I'm sure I'm missing something in how I build that out.

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message:
    Sent: 2/24/2025 1:01:00 PM
    From: Daniel Labrecque
    Subject: RE: Minimum Plan Sequence

    I think what you are wanting to use is a subquery on PLAN_SEQUENCE.   In the subquery you will do your joins as usual, but will select the PLAN_SEQUENCE field and make that minimum.



    ------------------------------
    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: 02-24-2025 12:50 PM
    From: Rachael Daniel
    Subject: Minimum Plan Sequence

    Sorry, I should have been more clear. If a student has more than one plan, I want the one where, for this single career number, the PLAN_SEQUENCE is the minimum sequence. The dates there (for declare date or eff date) don't always equate the minimum plan sequence number. For example, I want to return ONLY the ACCBBA2 plan code, because it's the minimum sequence number.

     

     

     

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message:
    Sent: 2/24/2025 12:43:00 PM
    From: Daniel Labrecque
    Subject: RE: Minimum Plan Sequence

    Hello Rachel,

    I think you can change the EFFDT criteria to:

    By default it is set to Current Date (EffSeq = Last).



    ------------------------------
    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: 02-24-2025 11:20 AM
    From: Rachael Daniel
    Subject: Minimum Plan Sequence

    Hello –

     

    I'm not sure what I'm missing here, or if I am just having a bad day πŸ˜‰. Can anyone share how to only return a plan from the ACAD_PLAN table on a student record that has the minimum sequence value? Thanks in advance!

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     

    Alliance 2026 Registration is Open!


  • 12.  RE: Minimum Plan Sequence

    Posted 02-25-2025 10:13 AM

    Thanks everyone, I'm back to back meetings today but will give these a shot tomorrow. I really appreciate everyone's help!

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 13.  RE: Minimum Plan Sequence

    Posted 02-25-2025 11:08 AM

    I've tried these things multiple times, and maybe I'm just way off base and missing things. I've tried in subqueries, not subqueries, all over. I've not been able to only return one row for the student's plan code based on minimum sequence. Thanks for the help anyway, I'm always appreciative of everyone giving their time to reply.

     

     

     

    SELECT DISTINCT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN
      FROM PS_ACAD_PROG A, PS_ACAD_PLAN B
      WHERE ( A.EFFDT =
            (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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 <= SYSDATE)
        AND A.EFFSEQ =
            (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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.ACAD_CAREER = 'UGRD'
         AND A.PROG_STATUS = 'AC'
         AND A.ACAD_PROG = 'LAUG'
         AND A.EMPLID = B.EMPLID
         AND A.ACAD_CAREER = B.ACAD_CAREER
         AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
         AND A.EFFSEQ = B.EFFSEQ
         AND B.EFFDT =
            (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN B_ED
            WHERE B.EMPLID = B_ED.EMPLID
              AND B.ACAD_CAREER = B_ED.ACAD_CAREER
              AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
              AND B_ED.EFFDT <= SYSDATE)
        AND B.EFFSEQ =
            (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PLAN B_ES
            WHERE B.EMPLID = B_ES.EMPLID
              AND B.ACAD_CAREER = B_ES.ACAD_CAREER
              AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
              AND B.EFFDT = B_ES.EFFDT)
         AND EXISTS (SELECT MIN( C.PLAN_SEQUENCE)
      FROM PS_ACAD_PLAN C
      WHERE C.EFFDT =
            (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN 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_ED.EFFDT <= SYSDATE)
        AND C.EFFSEQ =
            (SELECT MAX(C_ES.EFFSEQ) FROM PS_ACAD_PLAN 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.EFFDT = C_ES.EFFDT)
         AND C.EMPLID = B.EMPLID
         AND C.ACAD_CAREER = B.ACAD_CAREER
         AND C.STDNT_CAR_NBR = B.STDNT_CAR_NBR
         AND C.EFFDT = B.EFFDT
         AND C.EFFSEQ = B.EFFSEQ
         AND C.ACAD_PLAN = B.ACAD_PLAN))
      ORDER BY 1

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 14.  RE: Minimum Plan Sequence

    Posted 02-25-2025 11:16 AM
    i do this sub query all the time successfully ... here is my sub query criteria where I is ACAD_PLAN in the sub query and H is ACAD_PLAN in the Top Level of Query

    Logical
    Expression1
    Condition Type
    Expression 2
      I.EMPLID - Empl ID equal to H.EMPLID - Empl ID
    AND I.ACAD_CAREER - Academic Career equal to H.ACAD_CAREER - Academic Career
    AND I.STDNT_CAR_NBR - Student Career Nbr equal to H.STDNT_CAR_NBR - Student Career Nbr
    AND I.EFFDT - Effective Date equal to H.EFFDT - Effective Date
    AND I.EFFSEQ - Effective Sequence equal to H.EFFSEQ - Effective Sequence


    Select the field and Edit to Min



    --
    Bob Fogarty
    LionPATH - Reporting Team Lead
    The Pennsylvania State University
    rtf12@psu.edu



    Original Message
    Alliance 2026 Registration is Open!


  • 15.  RE: Minimum Plan Sequence

    Posted 02-25-2025 11:20 AM

    Oh I see what I did, I joined acad_plan to acad_plan, that's why it wasn't working. I knew it was something simple I was missing... Thanks!

     

    Rachael Daniel

    Director, Systems & Data Analysis

    Division of Strategic Enrollment Management & Student Success

    University of Wisconsin-Milwaukee

    414-229-4277

    rdaniel@uwm.edu

    My pronouns are she/her/hers

     

     




    Original Message
    Alliance 2026 Registration is Open!


  • 16.  RE: Minimum Plan Sequence

    Posted 02-25-2025 12:42 PM

    In case this is helpful, for this topic or similar issues I often use a custom sort in place of using a set field like PLAN_SEQUENCE.  I cannot always rely on the MIN plan sequence being wanted in a situation with all the different people and processes updating the CPP.  I write the same subquery mentioned by others in this thread connecting the keys of the ACAD_PLAN record except for the ACAD_PLAN field.  Then write a matching case statement on both the the base query and the subquery.  In my below example, I use both parts of the acad_plan code and the plan type to create a custom order.  

     SELECT A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN
      FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_ACAD_PLAN_TBL C
      WHERE ( A.EFFDT =
            (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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 <= SYSDATE)
        AND A.EFFSEQ =
            (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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 A.EFFSEQ = B.EFFSEQ
         AND B.EFFDT = A.EFFDT
         AND A.ACAD_CAREER = 'UGRD'
         AND A.ACAD_PROG = 'AC'
         AND B.ACAD_PLAN = C.ACAD_PLAN
         AND C.EFFDT =
            (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PLAN_TBL C_ED
            WHERE C.INSTITUTION = C_ED.INSTITUTION
              AND C.ACAD_PLAN = C_ED.ACAD_PLAN
              AND C_ED.EFFDT <= SYSDATE)
         AND CASE
    WHEN Substr( B.ACAD_PLAN, 3, 3) IN ('10K', 'IND') THEN 2
    WHEN  C.ACAD_PLAN_TYPE IN ('MAJ', 'SS', 'SP', 'HS') THEN 1
    WHEN  C.ACAD_PLAN_TYPE = 'CRT' THEN 3
    WHEN  C.ACAD_PLAN_TYPE = 'MIN' THEN 4
    WHEN  C.ACAD_PLAN_TYPE = 'DE' THEN 5
    ELSE 6 END
    ||  B.PLAN_SEQUENCE = (SELECT MIN(
    CASE
    WHEN Substr( D.ACAD_PLAN, 3, 3) IN ('10K', 'IND') THEN 2
    WHEN  E.ACAD_PLAN_TYPE IN ('MAJ', 'SS', 'SP', 'HS') THEN 1
    WHEN  E.ACAD_PLAN_TYPE = 'CRT' THEN 3
    WHEN  E.ACAD_PLAN_TYPE = 'MIN' THEN 4
    WHEN  E.ACAD_PLAN_TYPE = 'DE' THEN 5
    ELSE 6 END ||  D.PLAN_SEQUENCE
    )
      FROM PS_ACAD_PLAN D, PS_ACAD_PLAN_TBL E
      WHERE D.EMPLID = B.EMPLID
         AND D.ACAD_CAREER = B.ACAD_CAREER
         AND D.STDNT_CAR_NBR = B.STDNT_CAR_NBR
         AND D.EFFDT = B.EFFDT
         AND D.EFFSEQ = B.EFFSEQ
         AND D.ACAD_PLAN = E.ACAD_PLAN
         AND E.EFFDT =
            (SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PLAN_TBL E_ED
            WHERE E.INSTITUTION = E_ED.INSTITUTION
              AND E.ACAD_PLAN = E_ED.ACAD_PLAN
              AND E_ED.EFFDT <= SYSDATE)))



    ------------------------------
    Ross Nolan
    Data and Reporting Analysis
    University of California, Berkeley
    rvnolan@berkeley.edu
    ------------------------------

    Original Message
    Alliance 2026 Registration is Open!


Related Content