Versions Compared

Old Version 11

changes.mady.by.user Tabassum Lodhi

Saved on

compared with

New Version Current

changes.mady.by.user Tabassum Lodhi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

For a original query detail; see the link CU_BAR_AD_00006A_1 and CU_BAR_AD_00006B_2

The new query is queries CU_BAR_AD_00006A_3 and    AnchorCU_BAR_AD_00006A_3CU_BAR_AD_00006A_3

 

AnchorCU_BAR_AD_00006B_4CU_BAR_AD_00006B_4   have described below:


Final Report Delivery with requirements:

The queries have delivered to Gary and Dennyse.  Initially we have submitted the report based on their requirements and it has been approved by them.  Please see the detail in kace system: https://helpdesk.baruch.cuny.edu/admin The ticket number is 46135 .

---------

...

Query Development:

...

No Format
AND D.TEST_DT = (SELECT max ( J.TEST_DT) 
  FROM PS_STDNT_TEST_COMP J 
  
WHERE J.EMPLID = D.EMPLID 
     AND J.TEST_DT > SYSDATE - 730 
     AND J.TEST_COMPONENT IN ('MPL2','MPL3','MPL5') 
     AND J.LS_DATA_SOURCE = D.LS_DATA_SOURCE)

Image of CU_BAR_AD_00006A_3
Anchor
CU_BAR_AD_00006A_3
CU_BAR_AD_00006A_3

Query SQL:

Panel

SELECT CASE

WHEN SUM ( D.SCORE ) > 240 THEN 'M261'

WHEN SUM ( D.SCORE) BETWEEN 211 AND 240 THEN 'M227'

WHEN SUM ( D.SCORE) BETWEEN 148 AND 210 THEN 'M203'

WHEN SUM ( D.SCORE) BETWEEN 66 AND 147 THEN 'M103'

WHEN SUM ( D.SCORE) BETWEEN 24 AND 65 THEN 'M120'

WHEN SUM ( D.SCORE) BETWEEN 0 AND 23 THEN 'MATH'

END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, D.TEST_ID, TO_CHAR(D.TEST_DT,'YYYY-MM-DD'), D.LS_DATA_SOURCE, SUM ( D.SCORE), TO_CHAR(SYSDATE,'YYYY-MM-DD')

  FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP D

  WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ED.ADM_APPL_NBR

          AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR

          AND A_ED.EFFDT <= SYSDATE)

    AND A.EFFSEQ =

        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ES.ADM_APPL_NBR

          AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR

          AND A.EFFDT = A_ES.EFFDT)

     AND A.INSTITUTION = :1

     AND A.ACAD_CAREER = :2

     AND A.ADMIT_TERM BETWEEN :3 AND :4

     AND ( A.ADMIT_TYPE = :5

     OR :5 IS NULL)

     AND A.ACAD_PROG = 'UGRD'

     AND A.EMPLID = C.EMPLID

     AND A.EMPLID = K.EMPLID

     AND A.EMPLID = D.EMPLID

     AND D.TEST_ID = 'ACT-MATH-3'

     AND D.LS_DATA_SOURCE = 'S02'

     AND D.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')

     AND D.TEST_DT = (SELECT max ( G.TEST_DT)

  FROM PS_STDNT_TEST_COMP G

  WHERE G.EMPLID = D.EMPLID

     AND G.TEST_DT > SYSDATE - 730

     AND G.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')

     AND G.LS_DATA_SOURCE = D.LS_DATA_SOURCE)

     AND NOT EXISTS (SELECT B.EMPLID

  FROM PS_STDNT_GRPS_HIST B

  WHERE B.EFFDT =

        (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED

        WHERE B.EMPLID = B_ED.EMPLID

          AND B.INSTITUTION = B_ED.INSTITUTION

          AND B.STDNT_GROUP = B_ED.STDNT_GROUP

          AND B_ED.EFFDT <= SYSDATE)

     AND B.EMPLID = A.EMPLID

     AND B.INSTITUTION = A.INSTITUTION

     AND ( substr ( B.STDNT_GROUP, 1, 2) IN ('M1','M2')

     OR B.STDNT_GROUP = 'NTMT'

     OR B.STDNT_GROUP = 'S100')

     AND B.EFF_STATUS = 'A')

     AND NOT EXISTS (SELECT 'X'

  FROM PS_STDNT_TEST_COMP E

  WHERE E.EMPLID = A.EMPLID

     AND E.TEST_ID = 'RGNTS'

     AND E.TEST_COMPONENT IN ('SMA3','MATHB'))

     AND ( EXISTS (SELECT F.EMPLID

  FROM PS_STDNT_TEST_COMP F

  WHERE F.EMPLID = A.EMPLID

     AND F.TEST_ID = 'MATH EXEMPT')

     OR EXISTS (SELECT I.EMPLID

  FROM PS_STDNT_TEST_COMP I

  WHERE I.EMPLID = A.EMPLID

     AND I.TEST_COMPONENT = 'MAF'

     AND I.SCORE >= 25)

     OR ( EXISTS (SELECT H.EMPLID

  FROM PS_STDNT_TEST_COMP H

  WHERE H.EMPLID = A.EMPLID

     AND H.TEST_COMPONENT = 'M2'

     AND H.SCORE >= 40)))

  GROUP BY  A.EMPLID,  C.LAST_NAME,  C.FIRST_NAME,  C.MIDDLE_NAME,  K.NATIONAL_ID,  A.ADMIT_TYPE,  C.PHONE,  C.EMAIL_ADDR,  D.TEST_ID,  TO_CHAR(D.TEST_DT,'YYYY-MM-DD'),  D.LS_DATA_SOURCE

  ORDER BY 1, 3, 4, 5

 

The image of CU_BAR_AD_00006B_4
Anchor
CU_BAR_AD_00006B_4
CU_BAR_AD_00006B_4

 

Query SQL:

Panel

SELECT CASE

WHEN  J.SCORE > 95 THEN 'M261'

WHEN  J.SCORE BETWEEN 85 AND 95

AND SUM ( D.SCORE) BETWEEN 211 AND 300 THEN 'M261'

WHEN  J.SCORE BETWEEN 85 AND 95

AND SUM ( D.SCORE) BETWEEN 0 AND 210 THEN 'M227'

WHEN  J.SCORE BETWEEN 70 AND 84

AND SUM ( D.SCORE) BETWEEN 185 AND 300 THEN 'M227'

WHEN  J.SCORE BETWEEN 70 AND 84

AND SUM ( D.SCORE) BETWEEN 0 AND 184 THEN 'M203'

WHEN  J.SCORE BETWEEN 60 AND 69

AND SUM ( D.SCORE) BETWEEN 148 AND 300 THEN 'M203'

WHEN  J.SCORE BETWEEN 60 AND 69

AND SUM ( D.SCORE) BETWEEN 0 AND 147 THEN 'M103'

WHEN  J.SCORE BETWEEN 0 AND 59

AND SUM ( D.SCORE) BETWEEN 241 AND 300 THEN 'M261'

WHEN  J.SCORE BETWEEN 0 AND 59

AND SUM ( D.SCORE) BETWEEN 211 AND 240 THEN 'M227'

WHEN  J.SCORE BETWEEN 0 AND 59

AND SUM ( D.SCORE) BETWEEN 148 AND 210 THEN 'M203'

WHEN  J.SCORE BETWEEN 0 AND 59

AND SUM ( D.SCORE) BETWEEN 66 AND 147 THEN 'M103'

WHEN  J.SCORE BETWEEN 0 AND 59

AND SUM ( D.SCORE) BETWEEN 24 AND 65 THEN 'M120'

ELSE 'MATH'

END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, D.TEST_ID, TO_CHAR(D.TEST_DT,'YYYY-MM-DD'), D.LS_DATA_SOURCE, SUM ( D.SCORE), J.TEST_ID, J.TEST_COMPONENT, J.SCORE, TO_CHAR(SYSDATE,'YYYY-MM-DD')

  FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP D, PS_STDNT_TEST_COMP J

  WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ED.ADM_APPL_NBR

          AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR

          AND A_ED.EFFDT <= SYSDATE)

    AND A.EFFSEQ =

        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW 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.ADM_APPL_NBR = A_ES.ADM_APPL_NBR

          AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR

          AND A.EFFDT = A_ES.EFFDT)

     AND A.INSTITUTION = :1

     AND A.ACAD_CAREER = :2

     AND A.ADMIT_TERM BETWEEN :3 AND :4

     AND ( A.ADMIT_TYPE = :5

     OR :5 IS NULL)

     AND A.ACAD_PROG = 'UGRD'

     AND A.EMPLID = C.EMPLID

     AND A.EMPLID = K.EMPLID

     AND A.EMPLID = D.EMPLID

     AND D.TEST_ID = 'ACT-MATH-3'

     AND D.LS_DATA_SOURCE = 'S02'

     AND D.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')

     AND D.TEST_DT = (SELECT max ( G.TEST_DT)

  FROM PS_STDNT_TEST_COMP G

  WHERE G.EMPLID = D.EMPLID

     AND G.TEST_DT > SYSDATE - 730

     AND G.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')

     AND G.LS_DATA_SOURCE = D.LS_DATA_SOURCE)

     AND NOT EXISTS (SELECT B.EMPLID

  FROM PS_STDNT_GRPS_HIST B

  WHERE B.EFFDT =

        (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED

        WHERE B.EMPLID = B_ED.EMPLID

          AND B.INSTITUTION = B_ED.INSTITUTION

          AND B.STDNT_GROUP = B_ED.STDNT_GROUP

          AND B_ED.EFFDT <= SYSDATE)

     AND B.EMPLID = A.EMPLID

     AND B.INSTITUTION = A.INSTITUTION

     AND B.STDNT_GROUP IN ('S100','M120','M103','M203','M227','M261','M100','NTMT')

     AND B.EFF_STATUS = 'A')

     AND EXISTS (SELECT 'X'

  FROM PS_STDNT_TEST_COMP E

  WHERE E.EMPLID = A.EMPLID

     AND E.TEST_ID = 'RGNTS'

     AND E.TEST_COMPONENT IN ('SMA3','MATHB'))

     AND ( EXISTS (SELECT F.EMPLID

  FROM PS_STDNT_TEST_COMP F

  WHERE F.EMPLID = A.EMPLID

     AND F.TEST_ID = 'MATH EXEMPT')

     OR EXISTS (SELECT I.EMPLID

  FROM PS_STDNT_TEST_COMP I

  WHERE I.EMPLID = A.EMPLID

     AND I.TEST_COMPONENT = 'MAF'

     AND I.SCORE >= 25)

     OR ( EXISTS (SELECT H.EMPLID

  FROM PS_STDNT_TEST_COMP H

  WHERE H.EMPLID = A.EMPLID

     AND H.TEST_COMPONENT = 'M2'

     AND H.SCORE >= 40)))

     AND A.EMPLID = J.EMPLID

     AND J.TEST_ID = 'RGNTS'

     AND J.TEST_COMPONENT IN ('SMA3','MATHB')

     AND J.TEST_DT = (SELECT max ( L.TEST_DT)

  FROM PS_STDNT_TEST_COMP L

  WHERE L.EMPLID = J.EMPLID

     AND L.TEST_ID = J.TEST_ID

     AND L.TEST_COMPONENT = J.TEST_COMPONENT)

  GROUP BY  A.EMPLID,  C.LAST_NAME,  C.FIRST_NAME,  C.MIDDLE_NAME,  K.NATIONAL_ID,  A.ADMIT_TYPE,  C.PHONE,  C.EMAIL_ADDR,  D.TEST_ID,  TO_CHAR(D.TEST_DT,'YYYY-MM-DD'),  D.LS_DATA_SOURCE,  J.TEST_ID,  J.TEST_COMPONENT,  J.SCORE

  ORDER BY 1, 3, 4, 5

...