UFN_COMMITTEECAMPAIGNGOAL_DATALIST

            CREATE function dbo.UFN_COMMITTEECAMPAIGNGOAL_DATALIST
            (
                @GROUPID uniqueidentifier
            )
            returns table
            --with execute as caller

            as 
            return
            (
                with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                select CAMPAIGNGOAL.ID,
                       CAMPAIGNGOAL.AMOUNT,
                       CAMPAIGNHIERARCHYGOAL.STARTDATE,
                       CAMPAIGNHIERARCHYGOAL.ENDDATE as GOALDATE,
                       DATESORT =    Case 
                                    When CAMPAIGNHIERARCHYGOAL.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()) Then 1
                                    When CAMPAIGNHIERARCHYGOAL.ENDDATE IS NULL Then 0
                                    Else -1
                                End,
                       CAMPAIGNHIERARCHYGOAL.NAME,
                       CAMPAIGN.ID as CAMPAIGNID,
                       CAMPAIGN.NAME as CAMPAIGNNAME,
                       (
                            select top 1 
                                KPIINSTANCE.ID
                            from 
                                dbo.KPIINSTANCE 
                            where 
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNGOAL.CAMPAIGNID as varchar(36))
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNHIERARCHYGOALID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID as varchar(36))
                        ) as KPIINSTANCEID,
                        CAMPAIGN.BASECURRENCYID
                from dbo.CAMPAIGN
                inner join dbo.CAMPAIGNGOAL
                    on CAMPAIGN.ID = CAMPAIGNGOAL.CAMPAIGNID
                inner join dbo.CAMPAIGNHIERARCHYGOAL
                    on CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = CAMPAIGNHIERARCHYGOAL.ID
                inner join dbo.CAMPAIGNFUNDRAISER
                    on CAMPAIGN.ID = CAMPAIGNFUNDRAISER.CAMPAIGNID
                where 
                    CAMPAIGNFUNDRAISER.CONSTITUENTID = @GROUPID and
                    (dbo.UFN_DATE_COMPARETODATERANGE(CAMPAIGNHIERARCHYGOAL.STARTDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) = 0 or
                    dbo.UFN_DATE_COMPARETODATERANGE(CAMPAIGNHIERARCHYGOAL.ENDDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) = 0)

                union all

                select CAMPAIGNHIERARCHYGOAL.ID,
                       CAMPAIGNHIERARCHYGOAL.AMOUNT,
                       CAMPAIGNHIERARCHYGOAL.STARTDATE as GOALDATE,
                       CAMPAIGNHIERARCHYGOAL.ENDDATE,
                       DATESORT =    Case 
                                    When CAMPAIGNHIERARCHYGOAL.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()) Then 1
                                    When CAMPAIGNHIERARCHYGOAL.ENDDATE IS NULL Then 0
                                    Else -1
                                End,
                       CAMPAIGNHIERARCHYGOAL.NAME,
                       CAMPAIGN.ID as CAMPAIGNID,
                       CAMPAIGN.NAME as CAMPAIGNNAME,
                       (
                            select top 1 
                                KPIINSTANCE.ID
                            from 
                                dbo.KPIINSTANCE 
                            where 
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNHIERARCHYGOAL.CAMPAIGNID as varchar(36))
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNHIERARCHYGOALID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNHIERARCHYGOAL.ID as varchar(36))
                        ) as KPIINSTANCEID,
                        CAMPAIGN.BASECURRENCYID
                from dbo.CAMPAIGN
                inner join dbo.CAMPAIGNHIERARCHYGOAL
                    on CAMPAIGNHIERARCHYGOAL.CAMPAIGNID = CAMPAIGN.ID
                inner join dbo.CAMPAIGNFUNDRAISER
                    on CAMPAIGN.ID = CAMPAIGNFUNDRAISER.CAMPAIGNID
                where 
                    CAMPAIGNFUNDRAISER.CONSTITUENTID = @GROUPID and
                    (dbo.UFN_DATE_COMPARETODATERANGE(CAMPAIGNHIERARCHYGOAL.STARTDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) = 0 or
                    dbo.UFN_DATE_COMPARETODATERANGE(CAMPAIGNHIERARCHYGOAL.ENDDATE, CAMPAIGNFUNDRAISER.DATEFROM, CAMPAIGNFUNDRAISER.DATETO) = 0)
            );