I've got this script below that combines 5 tables into one to get yearly Assessment information for students. However I'm having trouble figuring out where and/or how to use the Max (Date) function to only return the most recent record entered for each person?
Any help is appreciated!
Daniel
STUDENTS: LOAD '7/1/'& YEAR(IF(num(Month(ENTRYDATE)>6),ENTRYDATE,AddYears(ENTRYDATE,-1))) AS FY_START, YEAR(IF(num(Month(Today())>6),ENTRYDATE,AddYears(Today(),-1))) & '-' & YEAR(IF(num(Month(Today())>6),ENTRYDATE,Today())) AS SchoolYr, YEAR(IF(num(Month(ENTRYDATE)>6),AddYears(ENTRYDATE,1),ENTRYDATE)) AS School_Year,
DCID AS STUDENTSDCID, ID AS STUDENTSID, LASTFIRST, STUDENT_NUMBER, SCHOOLID AS CUR_SCHOOLID_S,
GRADE_LEVEL AS CUR_GRADE_LEVEL, if(match([GRADE_LEVEL],-2,-1), 'PRE-K', if(match([GRADE_LEVEL],0,1,2,3,4,5), 'ELEMENTARY', if(match([GRADE_LEVEL],6,7,8),'MIDDLE', if(match([GRADE_LEVEL],9,10,11,12),'HIGH', if(match([GRADE_LEVEL],99),'GRAD'))))) AS [CURRENT_SCHOOL_LEVEL], Date(Floor(ENTRYDATE),'MM/DD/YYYY') AS ENTRYDATE,
GENDER AS GENDER_S, Pick(WildMatch(GENDER, 'M', 'F'),'Male','Female') AS [GENDER_L], ETHNICITY AS ETHNICITY_S, Pick(WildMatch(ETHNICITY, 'A', 'B', 'C','H', 'I', 'P'), 'Asian', 'Black', 'Caucasian', 'Hispanic', 'Native American', 'Pacific Islander')AS [ETHNICITY_L]
FROM [lib://QVD Store/STUDENTS.qvd] (qvd)
WHERE ENTRYDATE >= ('7/1/'& YEAR(IF(num(Month(Today())>6),ENTRYDATE,AddYears(Today(),-1)))) AND NOT MATCH (LAST_NAME,'Test','TEST') AND SCHOOLID <> '900' ;
INNER JOIN
LOAD ID AS TESTID, NAME AS TEST_NAME, DESCRIPTION AS TEST_DESCRIPTION FROM [lib://QVD Store/TEST.qvd] (qvd) WHERE NAME = 'AccessELL';
INNER JOIN
LOAD ID AS STUDENTTESTID, STUDENTID AS STUDENTSID, TESTID, DATE (TEST_DATE, 'MM-DD-YYYY') AS STUDENTTEST_DATE, SCHOOLID AS TEST_SCHOOLID_S, GRADE_LEVEL AS STUDENTTEST_GRADELEVEL, TERMID AS STUDENTTEST_TERMID FROM [lib://QVD Store/STUDENTTEST.qvd] (qvd);
INNER JOIN
LOAD ID AS TESTSCOREID, TESTID, NAME AS TESTSCORE_NAME, DESCRIPTION AS TESTSCORE_DESCRIPTION
FROM [lib://QVD Store/TESTSCORE.qvd] (qvd) WHERE NAME = 'Proficiency Level';
INNER JOIN
LOAD STUDENTID AS STUDENTSID, STUDENTTESTID, TESTSCOREID, NUMSCORE, PERCENTSCORE, ALPHASCORE FROM [lib://QVD Store/STUDENTTESTSCORE.qvd] (qvd);
