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 |
|
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 #659562 is a reply to message #659561] |
Sat, 21 January 2017 17:52 |
|
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 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:50:47 CDT 2024
|