USE [SDS_DevSchoolDistrict]
GO

ALTER FUNCTION [dbo].[CurrentStudentsForStudentGroupAssignment]()
--DECLARE @OutputTable TABLE
RETURNS @OutputTable TABLE
(       ------------------------------------------
        StudentId           int NULL,
        Ssid                varchar(20) NULL,

        LastName            Name NOT NULL,
        FirstName           Name NULL,
        MiddleName          Name NULL,
        NickName            Name NULL,
        BirthDate           BirthDate NULL,
        Gender              GenderAbbreviation NULL,

        SchoolYear          varchar(11) NULL,
        GradeLevel          varchar(100) NULL,
        School              varchar(100) NULL,

        StudentIsEll        bit NULL,
        IsSpecialEducation  bit NULL,
        IsHighlyCapabale    bit NULL,
        IsTitleOneLap       bit NULL
)       ------------------------------------------
AS
BEGIN
     -- Courses, Class Periods, Teachers, Absences, Assessment Test Scores, Languages

    INSERT  @OutputTable
    (       StudentId,
            Ssid,

            LastName,
            FirstName,
            MiddleName,
            NickName,
            BirthDate,
            Gender,

            SchoolYear,
            GradeLevel,
            School,

            StudentIsEll,
            IsSpecialEducation,
            IsHighlyCapabale,
            IsTitleOneLap
    )
    SELECT  S.StudentId,
            S.Ssid,
            P.LastName,
            P.FirstName,
            P.MiddleName,
            P.NickName,
            P.BirthDate,
            P.Gender,
            SY.SchoolYear,
            GL.GradeLevel,
            Sc.School,
            0 AS StudentIsEll,
            0 AS IsSpecialEducation,
            0 AS IsHighlyCapabale,
            0 AS IsTitleOneLap
    FROM    Students S
    JOIN    Persons  P
      ON    S.StudentId = P.PersonId
    JOIN    StudentSchoolYears SSY
      ON    S.StudentId = SSY.StudentId
     AND    SSY.SchoolYearId = 11 -- Current School Year Id
    JOIN    SchoolYears SY
      ON    SSY.SchoolYearId = SY.SchoolYearId
    JOIN    Schools Sc
      ON    SSY.PredominantSchoolId = Sc.SchoolId
    JOIN    GradeLevels GL
      ON    SSY.GradeLevelId = GL.GradeLevelId


    ORDER
       BY   P.LastName
    ----------------------------------
    --SELECT * FROM @OutputTable
    ----------------------------------



    RETURN
END
GO

SELECT * FROM CurrentStudentsForStudentGroupAssignment()