USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMCLASS

CREATE procedure dbo.USP_ENROLLGROUPINCLASS_STUDENTSTOADD_FROMCLASS
(
    @SOURCEID as uniqueidentifier,
    @DESTINATIONID as uniqueidentifier
)
as
begin
    declare @STARTDATE date
    declare @ENDDATE date
    select
        @STARTDATE = CLASS.STARTDATE,
        @ENDDATE = CLASS.ENDDATE
    from dbo.CLASS
    where CLASS.ID = @DESTINATIONID

    select
        EDUCATIONALHISTORY.CONSTITUENTID as ID,
        CONSTITUENT.NAME,
        CONSTITUENT.GENDER
    from dbo.STUDENTPROGRESSION
        inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
        inner join dbo.STUDENTCOURSE on EDUCATIONALHISTORY.CONSTITUENTID = STUDENTCOURSE.STUDENTID
        inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
        inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        inner join dbo.CONSTITUENT on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID
    where CLASSMEETINGGROUP.CLASSID = @SOURCEID
        and STUDENTPROGRESSION.STARTDATE <= @STARTDATE
        and STUDENTPROGRESSION.ENDDATE >= @ENDDATE
    and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0
    group by EDUCATIONALHISTORY.CONSTITUENTID, CONSTITUENT.NAME, CONSTITUENT.GENDER
    order by CONSTITUENT.NAME asc
end