UFN_PROMOTESTUDENTS_STUDENTSTOPROMOTECOUNT

        CREATE function dbo.UFN_PROMOTESTUDENTS_STUDENTSTOPROMOTECOUNT(
            @ACADEMICYEARID uniqueidentifier,
            @PROMOTIONSCHEDULEID uniqueidentifier,
            @STATUSES xml
        )
        returns integer
        with execute as caller
        as begin
            declare @STUDENTCOUNT as integer

            set @STUDENTCOUNT = (select COUNT(STUDENTPROGRESSION.ID)
            from dbo.STUDENTPROGRESSION
                inner join dbo.SCHOOLGRADELEVEL on STUDENTPROGRESSION.SCHOOLGRADELEVELID = SCHOOLGRADELEVEL.ID
                inner join dbo.PROMOTIONSCHEDULEENTRY on STUDENTPROGRESSION.SCHOOLGRADELEVELID = PROMOTIONSCHEDULEENTRY.SCHOOLGRADELEVELID
                inner join dbo.PROMOTIONSCHEDULE on PROMOTIONSCHEDULEENTRY.PROMOTIONSCHEDULEID = PROMOTIONSCHEDULE.ID
                inner join dbo.EDUCATIONALHISTORY on STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
                inner join (select T.c.value('(EDUCATIONALHISTORYSTATUSID)[1]','uniqueidentifier') as EDUCATIONALHISTORYSTATUSID
                            from @STATUSES.nodes('/STATUSES/ITEM') T(c)) STATUSES 
                                on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = STATUSES.EDUCATIONALHISTORYSTATUSID
            where dbo.UFN_ACADEMICYEAR_GET_FORSCHOOL_BYDATE(SCHOOLGRADELEVEL.SCHOOLID, STUDENTPROGRESSION.STARTDATE, STUDENTPROGRESSION.ENDDATE) = @ACADEMICYEARID
                and PROMOTIONSCHEDULEENTRY.PROMOTIONSCHEDULEID = @PROMOTIONSCHEDULEID
                and (PROMOTIONSCHEDULEENTRY.NEXTSCHOOLGRADELEVELID is not null or PROMOTIONSCHEDULE.NEXTSCHOOLGRADELEVELID is not null))

            return @STUDENTCOUNT
        end