DARTS : UM_TEST_SCORE_V

This table is view of STDNT_TEST_COMP - Student Test Components. It has the most recent maximum score for the student by test.

Key	Record.Fieldname	Format	XLAT	Heading Text
→	EMPLID - Empl ID	Char11		ID
	LS_DATA_SOURCE - Data Source	Char3	N	Data Srce
	SCORE - Test Score	Num5.2		Score
	SCORE_LETTER - Letter Score	Char4		Ltr Score
→	TEST_COMPONENT - Test Component	Char5		Component
	TEST_DT - Test Date	Date		Test Dt
→	TEST_ID - Test ID	Char11		Test ID

SQL code for UM_TEST_SCORE_V

SELECT A.EMPLID,
       A.TEST_ID,
       A.TEST_COMPONENT,
       A.TEST_DT,
       A.LS_DATA_SOURCE,
       A.SCORE,
       A.SCORE_LETTER
  FROM SYSADM.PS_STDNT_TEST_COMP A
 WHERE A.SCORE = (SELECT MAX(B.SCORE)
                    FROM SYSADM.PS_STDNT_TEST_COMP B
                   WHERE B.EMPLID = A.EMPLID
                     AND B.TEST_ID = A.TEST_ID
                     AND B.TEST_COMPONENT = A.TEST_COMPONENT)
   AND A.TEST_DT = (SELECT MAX(BB.TEST_DT)
                      FROM SYSADM.PS_STDNT_TEST_COMP BB
                     WHERE BB.EMPLID = A.EMPLID
                       AND BB.TEST_ID = A.TEST_ID
                       AND BB.TEST_COMPONENT = A.TEST_COMPONENT
                       AND BB.SCORE = A.SCORE)
UNION
SELECT C.EMPLID,
       C.TEST_ID,
       ' ',
       TO_DATE('01-JAN-1901', 'DD-MON-YYYY'),
       ' ',
       0,
       ' '
  FROM SYSADM.PS_STDNT_TEST C
 WHERE NOT EXISTS (SELECT 'X'
          FROM SYSADM.PS_STDNT_TEST_COMP Q
         WHERE Q.EMPLID = C.EMPLID
           AND Q.TEST_ID = C.TEST_ID);