UFN_EDUCATIONALHISTORY_ISUNIQUEENROLLMENT

Determines if a given enrollment is unique across institution and date range.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@EDUCATIONALHISTORYID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@EDUCATIONALINSTITUTIONID	uniqueidentifier	IN
@STARTDATE	UDT_FUZZYDATE	IN
@DATELEFT	UDT_FUZZYDATE	IN

Definition

Copy



        CREATE function dbo.UFN_EDUCATIONALHISTORY_ISUNIQUEENROLLMENT(
            @EDUCATIONALHISTORYID uniqueidentifier,
            @CONSTITUENTID uniqueidentifier,
            @EDUCATIONALINSTITUTIONID uniqueidentifier,
            @STARTDATE dbo.UDT_FUZZYDATE,
            @DATELEFT dbo.UDT_FUZZYDATE
        )
        returns bit
        with execute as caller
        as begin
            declare @ISUNIQUE bit
            set @ISUNIQUE = 1

            select  @ISUNIQUE = CASE WHEN COUNT(dbo.EDUCATIONALHISTORY.[ID]) > 0 THEN 0 ELSE 1 END
            from 
                dbo.EDUCATIONALHISTORY
                inner join dbo.EDUCATIONALHISTORYSTATUSHISTORY on dbo.EDUCATIONALHISTORY.[ID] = dbo.EDUCATIONALHISTORYSTATUSHISTORY.[EDUCATIONALHISTORYID]
                inner join dbo.SCHOOL on dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID] = dbo.SCHOOL.[ID]            
            where
                (dbo.EDUCATIONALHISTORY.[CONSTITUENTID] = @CONSTITUENTID) and
                (dbo.EDUCATIONALHISTORY.[EDUCATIONALINSTITUTIONID] = @EDUCATIONALINSTITUTIONID) and
                (dbo.EDUCATIONALHISTORY.[ID] <> @EDUCATIONALHISTORYID) and
                (    
                    ((dbo.UFN_DATE_FROMFUZZYDATE(@STARTDATE) >= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[STARTDATE])) and (dbo.UFN_DATE_FROMFUZZYDATE(@STARTDATE) <= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT])))
                    or
                    ((dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) >= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[STARTDATE])) and (dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) <= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT])))
                    or
                    ((dbo.UFN_DATE_FROMFUZZYDATE(@STARTDATE) < dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[STARTDATE])) and (dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) > dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT])))
                    or
                    ((dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT]) is null) and (dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) is null))
                    or
                    ((dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT]) is null) and (dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) >= dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[STARTDATE])))
                    or 
                    ((dbo.UFN_DATE_FROMFUZZYDATE(@DATELEFT) is null) and (dbo.UFN_DATE_FROMFUZZYDATE(dbo.EDUCATIONALHISTORY.[DATELEFT]) >  dbo.UFN_DATE_FROMFUZZYDATE(@STARTDATE)))
                )

            return @ISUNIQUE
        end