StudentInfo By Luffy

BEGIN
    --declare @LastSyncDatetime datetime

    CREATE TABLE #Temp(
        [StudentId] [nvarchar](100) NOT NULL,
        [CourseID] [nvarchar](50) NOT NULL,
        [CourseAttempt] [INT] NOT NULL,
        [FirstName] [nvarchar](50) NOT NULL,
        [LastName] [nvarchar](50) NOT NULL,
        [Email] [nvarchar](100) NOT NULL,
        [StartDate] [datetime] NOT NULL,
        [FinishDate] [datetime] NOT NULL,
        [STATUS] [nvarchar](50) NOT NULL,
        [CampusID] [nvarchar](50) NOT NULL,
        [LastUpdated] [nvarchar](50) NOT NULL
    )

    INSERT INTO #Temp
        SELECT
            sc.StudentID,
            sc.CourseID,
            sc.CourseAttempt,
            s.FirstName,
            s.LastName,
            s.Email,
            sc.StartDate,
            sc.FinishDate,
            sc.STATUS,
            sc.CampusID,
            MAX(CASE WHEN  scl.ChangedTime > sl.ChangedTime THEN scl.ChangedTime ELSE sl.ChangedTime END) AS LastUpdated
        FROM studentcourse sc
        JOIN course c ON c.CourseID = sc.CourseID
        JOIN StudentCourse_Log scl ON scl.StudentID = sc.StudentID AND scl.CourseId = sc.CourseID AND scl.CourseAttempt = sc.CourseAttempt
        JOIN Student s ON s.StudentId = sc.StudentID
        JOIN Student_Log sl ON sl.StudentId = s.StudentId
        WHERE c.CourseType = 'ELICOS'
        AND s.StudentId = '20180020'
        GROUP BY  sc.StudentID, sc.CourseID, sc.CourseAttempt, s.FirstName, s.LastName, s.Email, sc.StartDate, sc.FinishDate, sc.STATUS, sc.CampusID

    UPDATE #Temp
    SET StartDate = (SELECT MIN(StartDate) FROM #Temp t1
                    WHERE t1.StudentId = #Temp.StudentId
                    GROUP BY t1.StudentId),
        FinishDate = isnull((SELECT MAX(FinishDate) FROM #Temp t1
                            WHERE t1.StudentId = #Temp.StudentId
                            AND #Temp.STATUS IN ('Current Student',
                                                'Completed',
                                                'Deferred',
                                                'Finished',
                                                'Returning from Deferral',
                                                'Converted',
                                                'Enrolled')
                                                GROUP BY t1.StudentId )
                            ,(SELECT MAX(FinishDate) FROM #Temp t1
                            WHERE t1.StudentId = #Temp.StudentId
                            GROUP BY t1.StudentId )),
        [STATUS] = (SELECT top 1 LAST_VALUE(STATUS) OVER (ORDER BY StartDate DESC)
                    FROM #Temp t1
                    WHERE t1.StudentId = #Temp.StudentId
                    GROUP BY StudentId,StartDate, STATUS)


    SELECT
        DISTINCT StudentId,
            FirstName,
            LastName,
            Email,
            StartDate,
            FinishDate,
            STATUS,
            c.CampusName AS SchoolName
    FROM #Temp
    JOIN Campus c ON #Temp.CampusID = c.CampusID
    WHERE LastUpdated > isnull(@LastSyncDatetime,0)

    DROP TABLE #Temp

END