Proc_GEL_StudentFetchForGEL

USE [RTOManager_Demo]
GO
/****** Object:  StoredProcedure [dbo].[Proc_GEL_StudentFetchForGEL]    Script Date: 6/10/2022 9:50:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----Procedure to fetch student information for GEL integration
----Created By: Yuvaraj Upadhyaya
----Created date: May 19 2022

ALTER PROCEDURE [dbo].[Proc_GEL_StudentFetchForGEL]
(@LastSyncDatetime datetime)
AS
BEGIN
    ----- In case of any change please maintain column names as below:
    ----- StudentId,FirstName,LastName,Email,StartDate,FinishDate,Status

    SELECT s.StudentId,s.FirstName,s.LastName,s.Email,sc_grp.StartDate,sc_grp.FinishDate,(SELECT top 1 STATUS FROM StudentCourse WHERE studentId=s.StudentId ORDER BY StartDate DESC) AS STATUS FROM student s
    INNER JOIN (
    SELECT sc.StudentId, (SELECT MAX(ChangedTime) FROM studentCourse_log WHERE StudentId=sc.StudentId) AS ChangedTime,
    MIN(sc.StartDate) AS StartDate,MAX(sc.FinishDate) AS FinishDate FROM studentcourse sc
    INNER JOIN course c ON c.CourseID= sc.CourseId
    WHERE c.CourseType='ELICOS'
    GROUP BY sc.StudentId) AS sc_grp ON sc_grp.StudentId=s.StudentId
    WHERE sc_grp.ChangedTime > @LastSyncDatetime


END