UFN_PERFORMANCECATEGORYLEVELSKILLCATEGORY_HASALLRATINGS

CREATE function dbo.UFN_PERFORMANCECATEGORYLEVELSKILLCATEGORY_HASALLRATINGS(
    @PERFORMANCECATEGORYLEVELSKILLCATEGORYID uniqueidentifier
)
returns bit
with execute as caller
as begin


    declare @SKILLCATEGORYRATING table (
        PERFORMANCECATEGORYLEVELSKILLCATEGORYID uniqueidentifier,   -- ID

        SKILLCATEGORYID uniqueidentifier,                           -- skill category the level has defined

        SKILLRATINGGROUPID uniqueidentifier                         -- all rating groups that are or need to defined

    )

    -- determine ratings table that need to be setup for skill category

    insert into @SKILLCATEGORYRATING
        select 
            PERFORMANCECATEGORYLEVELSKILLCATEGORY.ID
            ,PERFORMANCECATEGORYLEVELSKILLCATEGORY.STUDENTSKILLCATEGORYCODEID
            ,STUDENTSKILLRATINGGROUP.ID
        from 
            dbo.PERFORMANCECATEGORYLEVELSKILLCATEGORY
            join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILLRATINGGROUP.ID in (
                select distinct STUDENTSKILLRATINGGROUPID 
                from dbo.STUDENTSKILL 
                where (STUDENTSKILL.STUDENTSKILLCATEGORYCODEID = PERFORMANCECATEGORYLEVELSKILLCATEGORY.STUDENTSKILLCATEGORYCODEID)
                  or (PERFORMANCECATEGORYLEVELSKILLCATEGORY.STUDENTSKILLCATEGORYCODEID is null and STUDENTSKILL.STUDENTSKILLCATEGORYCODEID is null))
                left join dbo.STUDENTSKILLCATEGORYCODE on   STUDENTSKILLCATEGORYCODE.ID = PERFORMANCECATEGORYLEVELSKILLCATEGORY.STUDENTSKILLCATEGORYCODEID
        where 
            PERFORMANCECATEGORYLEVELSKILLCATEGORY.ID = @PERFORMANCECATEGORYLEVELSKILLCATEGORYID
        order by STUDENTSKILLRATINGGROUP.NAME


    return 
        -- return false if missing any necessary ratings are not defined

        case when exists(
            select PERFORMANCECATEGORYLEVELSKILLCATEGORY.ID
            from @SKILLCATEGORYRATING scr
                join dbo.PERFORMANCECATEGORYLEVELSKILLCATEGORY on PERFORMANCECATEGORYLEVELSKILLCATEGORY.ID = scr.PERFORMANCECATEGORYLEVELSKILLCATEGORYID
                join dbo.STUDENTSKILLRATINGGROUP on STUDENTSKILLRATINGGROUP.ID = scr.SKILLRATINGGROUPID
                left join dbo.PERFORMANCECATEGORYLEVELSKILLCATEGORYRATING on PERFORMANCECATEGORYLEVELSKILLCATEGORYRATING.PERFORMANCECATEGORYLEVELSKILLCATEGORYID = PERFORMANCECATEGORYLEVELSKILLCATEGORY.ID
                    and PERFORMANCECATEGORYLEVELSKILLCATEGORYRATING.STUDENTSKILLRATINGGROUPID = scr.SKILLRATINGGROUPID
            where PERFORMANCECATEGORYLEVELSKILLCATEGORYRATING.ID is null)

            then 0
            else -1
        end

end