Home » SQL & PL/SQL » SQL & PL/SQL » Effective Date/Min Eff Seq subquery issues (PS Student Administration)
Effective Date/Min Eff Seq subquery issues [message #659560] Sat, 21 January 2017 15:49 Go to next message
Lasmith76
Messages: 3
Registered: January 2017
Junior Member
I'm new to query building and trying to write a query to determine the timespan between when an applicant is offered a seat in a program until they make a decision or are withdrawn for inaction. The two dates I need to pull are the last incidence of an ADMT Program Action in the history and the first incidence of a DEFR/DEIN/WADM/WAPP Program Action after that last ADMT row.
Examples:

1)
• ADMT row with eff date of 3/17/2016
• WADM row with eff date of 4/1/2016
• ADMT Row with eff date of 6/20/2016
• DEIN row with eff date of 7/3/2016
• WADM row with eff date of 8/1/2016

Query should pull the ADMT row from 6/20/2016 and DEIN row of 7/3/2016

2)
• ADMT row with eff date of 6/15/2016
• APPL COMP row with eff date on 7/1/2016 (effseq 1)
• DEFR row with eff date on 7/1/2016 (effseq 2)
• WAPP row with eff date on 7/1/2016 (eff seq 3)

Query will pull the ADMT row from 6/15/2016 and DEFR row from 7/1/2016

My query works exactly as I would expect it to for the ADMT rows, but it is returning the last DEFR/DEIN/WADM/WAPP row for those Program Actions. So, for Example 1, it is pulling the WADM row from 8/1/2016 and for Example 2, it is pulling the WAPP row from 7/1/2016.

The functional analyst who has been assisting me instructed me that I needed to create subqueries using min EFFDT and min EFFSEQ values for the DEIN/DEFR/WADM/WAPP program actions after the effective date for the last ADMT row. I have created subqueries that replicate the SQL she gave me, but it is not working. Can someone please tell me what I need to do to make this work? Here is the SQL from my current query:
SELECT A.EMPLID, A.ACAD_PROG, A.CAMPUS, A.ADM_APPL_NBR, A.APPL_PROG_NBR, A.PROG_ACTION, (CONVERT(CHAR(10),A.EFFDT,121)), D.PROG_ACTION, D.PROG_REASON, (CONVERT(CHAR(10),D.EFFDT,121)) 
  FROM PS_ADM_APPL_PROG A, PS_ADM_APPL_PROG D 
  WHERE ( A.ADMIT_TERM = :1 
     AND A.PROG_ACTION = 'ADMT' 
     AND A.ACAD_PROG LIKE :2 
     AND A.EFFDT <= :3 
     AND A.EFFDT = (SELECT MAX( B.EFFDT) 
  FROM PS_ADM_APPL_PROG B 
  WHERE A.EMPLID = B.EMPLID 
     AND A.ACAD_CAREER = B.ACAD_CAREER 
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR 
     AND A.ADM_APPL_NBR = B.ADM_APPL_NBR 
     AND A.APPL_PROG_NBR = B.APPL_PROG_NBR 
     AND B.PROG_ACTION = 'ADMT' 
  HAVING ( MAX( B.EFFDT) <= :3)) 
     AND A.EFFSEQ = (SELECT MAX( C.EFFSEQ) 
  FROM PS_ADM_APPL_PROG C 
  WHERE 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 A.EFFDT = C.EFFDT 
     AND C.PROG_ACTION = 'ADMT') 
     AND A.EMPLID = D.EMPLID 
     AND A.ACAD_PROG = D.ACAD_PROG 
     AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR 
     AND A.ADM_APPL_NBR = D.ADM_APPL_NBR 
     AND A.APPL_PROG_NBR = D.APPL_PROG_NBR 
     AND D.PROG_ACTION IN ('DEIN','DEFR','WADM','WAPP') 
     AND D.EFFDT >= (SELECT MIN( E.EFFDT) 
  FROM PS_ADM_APPL_PROG E 
  WHERE D.EMPLID = E.EMPLID 
     AND D.ACAD_CAREER = E.ACAD_CAREER 
     AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR 
     AND D.ADM_APPL_NBR = E.ADM_APPL_NBR 
     AND D.APPL_PROG_NBR = E.APPL_PROG_NBR 
     AND D.PROG_ACTION IN ('DEIN','DEFR','WADM','WAPP') 
     AND D.EFFDT >= A.EFFDT) 
     AND D.EFFSEQ = (SELECT MIN( F.EFFSEQ) 
  FROM PS_ADM_APPL_PROG F 
  WHERE D.EMPLID = F.EMPLID 
     AND D.ACAD_CAREER = F.ACAD_CAREER 
     AND D.STDNT_CAR_NBR = F.STDNT_CAR_NBR 
     AND D.APPL_PROG_NBR = F.APPL_PROG_NBR 
     AND D.APPL_PROG_NBR = F.APPL_PROG_NBR 
     AND D.EFFDT = F.EFFDT))
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Sat, 21 January 2017 16:09] by Moderator

Report message to a moderator

Re: Effective Date/Min Eff Seq subquery issues [message #659561 is a reply to message #659560] Sat, 21 January 2017 16:12 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Please realize that since we don't have your tables, we can't run, test, debug, or improve the posted SQL.

I am unclear what exactly is desired or expected to be considered "working".
Re: Effective Date/Min Eff Seq subquery issues [message #659562 is a reply to message #659561] Sat, 21 January 2017 17:52 Go to previous messageGo to next message
Lasmith76
Messages: 3
Registered: January 2017
Junior Member
Hello,

Thank you for your response! "Working" for me would mean that the "Subsequent Effective Date" column in my query (created via the Query Manager module within PS Student Administration), which uses D.PROG_ACTION, is populating with a date representing the minimum EFFDT with the minimum EFFSEQ for listed statuses for D.PROG_ACTION, which also has to be greater than or equal to the maximum EFFDT of a specific status for A.PROG_ACTION.

To explain using more general business process, if I admit someone for the final time on X date, my query needs to pull the date when they subsequently paid their tuition deposit (which will always be equal to or later than the admit date), not the day they were withdrawn for failing to pay the balance of that tuition (will always be greater than or equal to the deposit date). For this scenario, my query is currently pulling the row for day the person was withdrawn for failing to pay tuition, not the day of the tuition deposit. This is happening despite the presence of the MIN attributes through subquery, which was recommended (and written up for me to replicate via Query Manager) by my functional resource person, which is why I am perplexed.

Re: Effective Date/Min Eff Seq subquery issues [message #659563 is a reply to message #659562] Sat, 21 January 2017 18:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8804
Registered: November 2002
Location: California, USA
Senior Member
You still have not provided create table or insert statements for your sample data. Since you are new, I have provided an example of that below with the minimal data that you posted. I have also provided a sample query based on that data that provides the results that your initial post requested based on that data. I have tried to make it as generic as possible, so that you can add whatever other columns that you need to join by or wish to display, without affecting the rest of the functionality. I have also indicated where you would need to provide some column aliases, such as using a prefix for same column names in the inner sub-query resulting from the self-join, then use those same aliases in the outer query. This is just one method. There are many other ways that you could achieve the same results.

-- create table and insert statements that you should have provided:
CREATE TABLE PS_ADM_APPL_PROG
  (emplid       VARCHAR2(6),
   prog_action  VARCHAR2(11),
   effdt        DATE,
   effseq       NUMBER)
/
INSERT ALL
INTO ps_adm_appl_prog VALUES ('EMP1', 'ADMT', TO_DATE ('3/17/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP1', 'WADM', TO_DATE ('4/1/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP1', 'ADMT', TO_DATE ('6/20/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP1', 'DEIN', TO_DATE ('7/3/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP1', 'WADM', TO_DATE ('8/1/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP2', 'ADMT', TO_DATE ('6/15/2016', 'MM/DD/YYYY'), NULL)  
INTO ps_adm_appl_prog VALUES ('EMP2', 'APPL COMP', TO_DATE ('7/1/2016', 'MM/DD/YYYY'), 1)  
INTO ps_adm_appl_prog VALUES ('EMP2', 'DEFR', TO_DATE ('7/1/2016', 'MM/DD/YYYY'), 2)  
INTO ps_adm_appl_prog VALUES ('EMP2', 'WAPP', TO_DATE ('7/1/2016', 'MM/DD/YYYY'), 3)  
SELECT * FROM DUAL
/

-- resulting sample data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM ps_adm_appl_prog ORDER BY emplid, effdt, effseq
  2  /

EMPLID PROG_ACTION EFFDT          EFFSEQ
------ ----------- ---------- ----------
EMP1   ADMT        03/17/2016
EMP1   WADM        04/01/2016
EMP1   ADMT        06/20/2016
EMP1   DEIN        07/03/2016
EMP1   WADM        08/01/2016
EMP2   ADMT        06/15/2016
EMP2   APPL COMP   07/01/2016          1
EMP2   DEFR        07/01/2016          2
EMP2   WAPP        07/01/2016          3

9 rows selected.

-- suggested query:
SCOTT@orcl_12.1.0.2.0> SELECT a_emplid, a_prog, a_effdt, d_prog, d_effdt,
  2  	    -- add any other columns here using same aliases from sub-query below
  3  	    d_effdt - a_effdt AS days_between
  4  FROM   (SELECT a.emplid AS a_emplid, a.prog_action AS a_prog, a.effdt AS a_effdt,
  5  		    d.prog_action AS d_prog, d.effdt AS d_effdt,
  6  		    -- add any other columns here using aliases to avoid duplicate names
  7  		    ROW_NUMBER () OVER (PARTITION BY a.emplid ORDER BY d.effdt, d.effseq) rn2
  8  	     FROM   (SELECT *
  9  		     FROM   (SELECT p.*, ROW_NUMBER () OVER (PARTITION BY p.emplid ORDER BY effdt DESC) rn1
 10  			     FROM   ps_adm_appl_prog p
 11  			     WHERE  p.prog_action = 'ADMT')
 12  		     WHERE  rn1 = 1) a,
 13  		     (SELECT *
 14  		      FROM   ps_adm_appl_prog p
 15  		      WHERE  prog_action IN ('DEFR', 'DEIN', 'WADM', 'WAPP')) d
 16  	     WHERE  d.effdt >= a.effdt
 17  	     AND    a.emplid = d.emplid
 18  	     -- add any other join conditions here
 19  	     ORDER  BY a.emplid)
 20  WHERE  rn2 = 1
 21  /

A_EMPL A_PROG      A_EFFDT    D_PROG      D_EFFDT    DAYS_BETWEEN
------ ----------- ---------- ----------- ---------- ------------
EMP1   ADMT        06/20/2016 DEIN        07/03/2016           13
EMP2   ADMT        06/15/2016 DEFR        07/01/2016           16

2 rows selected.
Re: Effective Date/Min Eff Seq subquery issues [message #659565 is a reply to message #659563] Sun, 22 January 2017 10:10 Go to previous message
Lasmith76
Messages: 3
Registered: January 2017
Junior Member
Thanks so much for your advice and examples, Barbara. Much appreciated!
Previous Topic: How to create an object table
Next Topic: Oracle Outer join Operator(+) with operand of OR or IN
Goto Forum:
  


Current Time: Sat Feb 17 23:33:55 CST 2018

Total time taken to generate the page: 0.20527 seconds