UFN_SESSION_GETSTUDENTS

create function dbo.UFN_SESSION_GETSTUDENTS
(
    @SESSIONID uniqueidentifier,
    @SCHOOLID uniqueidentifier
)
returns @RESULTS TABLE
(
    ID uniqueidentifier
)
as begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
    where TERM.SESSIONID = @SESSIONID

    insert into @RESULTS
    (
        ID
    )
    select
        CONSTITUENT.ID
    from dbo.EDUCATIONALHISTORY
        inner join dbo.STUDENTPROGRESSION on EDUCATIONALHISTORY.ID = STUDENTPROGRESSION.ENROLLMENTID
        inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
        inner join dbo.CONSTITUENT on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID
    where SCHOOLGRADELEVEL.SCHOOLID = @SCHOOLID
        and not (STUDENTPROGRESSION.ENDDATE < @SESSION_STARTDATE or STUDENTPROGRESSION.STARTDATE > @SESSION_ENDDATE)
    group by CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME
    order by CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME

    return
end