SELECT hs.*,AVG(hsm.Mark)
FROM H_Students hs
LEFT JOIN H_Students st_later
ON st_later.UID > hs.UID AND
st_later.Id = hs.Id
LEFT JOIN H_StudentMarks hsm
ON hsm.StudentId = hs.Id
LEFT JOIN H_StudentMarks sm_later
ON sm_later.UID > hsm.UID AND
sm_later.StudentId = hsm.StudentId AND
sm_later.SubjectID = hsm.SubjectId AND
sm_later.ExamDate = hsm.ExamDate
WHERE st_later.UID IS NULL AND
hs.EventType <> 'D' AND
sm_later.UID IS NULL AND
(hsm.EventType <> 'D' OR hsm.UID IS NULL)
GROUP BY hs.Id,hs.UID