Student Class By Luffy

BEGIN
    --declare @StudentId nvarchar(50) = '20180020'
    --declare @SubjectStartDate datetime = '2018-1-1'
    --declare @SubjectFinishDate datetime = '2018-12-26'

    SELECT
        ClassData.ClassID AS ClassId,
        ClassData.SubjectID AS ClassType,
        ClassData.Batch AS ClassName,
        ClassData.RoomName AS ClassRoom,
        ClassData.ClassTeacher AS ClassTeacher,
        --ClassData.StartTime,
        --ClassData.FinishTime,
        CONVERT(VARCHAR(19), ClassData.StartTime,120) + ' - ' + CONVERT(VARCHAR(19), ClassData.FinishTime,120) AS ClassTime,
        ClassData.DAY AS ClassDay,
        ClassData.SubjectStartDate AS ClassFromDate,
        ClassData.SubjectFinishDate AS ClassToDate,
        ClassData.StudentID AS StudentId,
        --ClassData.ClassID,
        (SELECT MAX(v)
        FROM (VALUES (ClassLog.ClassLastUpdated), (ClassLog.ClassModeLastUpdated), (StudentResultLog.LastUpdated))
        AS VALUE(v)) AS ChangedTime
    FROM
        (SELECT
            cl.ClassID,
            cl.SubjectID,
            sr.Batch,
            r.RoomName,
            st.FirstName + ' ' + st.LastName AS ClassTeacher,
            cm.StartTime,
            cm.FinishTime,
            cm.DAY,
            sr.SubjectStartDate,
            sr.SubjectFinishDate,
            sr.StudentId,
            sr.CourseId,
            sr.CourseAttempt,
            sr.SubAttempt
        FROM studentresults sr
        JOIN course c ON sr.CourseID = c.CourseID
        JOIN class cl ON cl.SubjectID = sr.SubjectID AND cl.Batch = sr.Batch
        JOIN Classmode cm ON cm.ClassID = cl.ClassID
        JOIN Room r ON r.RoomID = cm.RoomID
        LEFT OUTER JOIN Staff st ON st.UserName = cm.TeacherID
        WHERE c.CourseType = 'ELICOS'
        AND sr.Batch <> 'No Batch'
        AND c.FlexiAttd = 0

        UNION ALL

        SELECT
            cl.ClassID,
            cl.SubjectID,
            sr.Batch,
            r.RoomName,
            st.FirstName + ' ' + st.LastName AS ClassTeacher,
            cm.StartTime,
            cm.FinishTime,
            cm.DAY,
            sr.SubjectStartDate,
            sr.SubjectFinishDate,
            sr.StudentId,
            sr.CourseId,
            sr.CourseAttempt,
            sr.SubAttempt
        FROM studentresults sr
        JOIN course c ON sr.CourseID = c.CourseID
        JOIN class cl ON cl.SubjectID = sr.SubjectID AND cl.Batch = sr.Batch
        JOIN Classmode cm ON cm.ClassID = cl.ClassID
        JOIN StudentResultsClassMode srcm
            ON sr.StudentID = srcm.StudentId
            AND sr.CourseID = srcm.CourseID
            AND sr.CourseAttempt = srcm.CourseAttempt
            AND sr.SubjectID = srcm.SubjectId
            AND sr.SubAttempt = srcm.SubjectAttempt
            AND srcm.ClassModeId = cm.ClassModeID
        JOIN Room r ON r.RoomID = cm.RoomID
        LEFT OUTER JOIN Staff st ON st.UserName = cm.TeacherID
        WHERE c.CourseType = 'ELICOS'
        AND sr.Batch <> 'No Batch'
        AND c.FlexiAttd = 1 ) AS ClassData
    LEFT OUTER JOIN
        (SELECT
            cl.ClassId,
            MAX(cl.ChangedTime) AS ClassLastUpdated,
            MAX(cml.ChangedTime) AS ClassModeLastUpdated FROM Class_Log cl
        JOIN ClassMode_Log cml ON cl.ClassId = cml.ClassId
        GROUP BY cl.ClassId) AS ClassLog
        ON ClassData.ClassID = ClassLog.ClassId
    LEFT OUTER JOIN
        (SELECT StudentId, CourseId, CourseAttempt, SubjectId, SubAttempt, MAX(ChangedTime) AS LastUpdated
        FROM StudentResults_Log GROUP BY StudentId, CourseId, CourseAttempt, SubjectId, SubAttempt) AS StudentResultLog
        ON
            StudentResultLog.StudentId = ClassData.StudentId
            AND StudentResultLog.CourseId = ClassData.CourseId
            AND StudentResultLog.CourseAttempt = ClassData.CourseAttempt
            AND StudentResultLog.SubjectId = ClassData.SubjectId
            AND StudentResultLog.SubAttempt = ClassData.SubAttempt
    WHERE ClassData.StudentID = @StudentId
    AND @SubjectStartDate <= ClassData.StartTime
    AND ClassData.FinishTime <= @SubjectFinishDate
END