UFN_STUDENTPROGRESSION_ISUNIQUE

Student progression constraint that determines if a student progression already exists for an enrollment's student record with the same start and end dates.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@STUDENTPROGRESSIONID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN

Definition

Copy



        CREATE function dbo.UFN_STUDENTPROGRESSION_ISUNIQUE(
            @STUDENTPROGRESSIONID uniqueidentifier,
            @STARTDATE date,
            @ENDDATE date
        )
        returns bit
        with execute as caller
        as begin
            declare @ISUNIQUE bit
            set @ISUNIQUE = 1

            select  @ISUNIQUE = CASE WHEN COUNT(SP2.ID) > 0 THEN 0 ELSE 1 END
            from 
                dbo.STUDENTPROGRESSION SP1
                join dbo.EDUCATIONALHISTORY EH1 on SP1.ENROLLMENTID = EH1.ID
                join dbo.SCHOOLGRADELEVEL SGL1 on SP1.SCHOOLGRADELEVELID = SGL1.ID
                join dbo.EDUCATIONALHISTORY EH2 on EH1.CONSTITUENTID = EH2.CONSTITUENTID
                join dbo.STUDENTPROGRESSION SP2 on EH2.ID = SP2.ENROLLMENTID
                join dbo.SCHOOLGRADELEVEL SGL2 on SP2.SCHOOLGRADELEVELID = SGL2.ID
            where 
                (SP1.ID = @STUDENTPROGRESSIONID and SP2.ID <> @STUDENTPROGRESSIONID) and
                (SGL1.SCHOOLID = SGL2.SCHOOLID) and
                (SP2.STARTDATE = @STARTDATE and SP2.ENDDATE = @ENDDATE)

            return @ISUNIQUE
        end