USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMGRADELEVEL

CREATE procedure dbo.USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMGRADELEVEL
(
    @GRADELEVELID as uniqueidentifier,
    @CLASSID as uniqueidentifier
)
as
begin
    declare @STARTDATE date
    declare @ENDDATE date
    select
        @STARTDATE = CLASS.STARTDATE,
        @ENDDATE = CLASS.ENDDATE
    from dbo.CLASS
    where CLASS.ID = @CLASSID

    select
        EDUCATIONALHISTORY.CONSTITUENTID as ID,
        CONSTITUENT.NAME,
        CONSTITUENT.GENDER
    from dbo.STUDENTPROGRESSION
        inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
        inner join SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.CONSTITUENT on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID
    where SCHOOLGRADELEVEL.GRADELEVELID = @GRADELEVELID
        and STUDENTPROGRESSION.STARTDATE <= @STARTDATE
        and STUDENTPROGRESSION.ENDDATE >= @ENDDATE
    group by EDUCATIONALHISTORY.CONSTITUENTID, CONSTITUENT.NAME, CONSTITUENT.GENDER
    order by CONSTITUENT.NAME asc
end