Dear comminity, Im having trouble with a situation that I hope you can help me with.
Im trying to do the following: I have a lot of data of students where. The data is:
ID - FirstYear (the year when he entered the University) - AcadYear (academic year) - AcadSem (Academical semester) - Program ID - Courses - CourseLvl.
The first problem is: I have some students that entered in a year, then quit and later they re enter. What I want to do is show only the latest register of their entrance to the University.
The second problem is: Each and every student has a different number of courses they take each semester (semester 1 or 2). Each course has a level. The level that the student currently is, is the lowest of the level of the courses that he is currently taking.
Here is the sample data:
ID
FirstYear
AcadYear
AcadSem
ProgramID
Courses
CourseLvl
1
1996
1996
1
a
a
1
1
2014
2014
1
b
a
1
1
2014
2014
1
b
b
1
1
2014
2014
1
b
c
1
1
2014
2014
1
b
d
1
1
2014
2014
1
b
e
1
1
2014
2014
2
b
a
1
1
2014
2014
2
b
f
2
1
2014
2014
2
b
g
2
1
2014
2014
2
b
h
2
1
2014
2015
1
b
a
1
1
2014
2015
1
b
i
3
1
2014
2015
1
b
j
3
1
2014
2015
1
b
k
3
1
2014
2015
2
b
l
3
1
2014
2015
2
b
a
1
As you can see, the student 1 has two years when he entered the university (1996 and 2014) I want to get only the data from the latest entrance.
Then with the 2014 entrance, I have a lot of courses that he is taking. If I want to know which level he is, I have to see which is the lowest level of the courses he is taking on the latest registry (AcadYear=2015 and AcadSem=2). So in this case the level of the student would be 1, because even if he has courses from level 3, he is still doing the course "a" which is on lvl 1.
I know that the way to do this is with a group by on the back end (that's the way I tried) but I can't do it yet. Hope you can help me with this.
