USP_REPORT_PROSPECTANALYSIS_PROSPECTSEGMENTATION

            create procedure dbo.USP_REPORT_PROSPECTANALYSIS_PROSPECTSEGMENTATION
            (
                @ID uniqueidentifier
            ) as
                set nocount on;

                with GROUPEDPROSPECTS(
                    COUNT,
                    PRIMARYSECTIONID, 
                    SECONDARYSECTIONID
                )as(
                    select
                        count(PROSPECT.CONSTITUENTID),
                        PROSPECT.PRIMARYSECTIONID,
                        PROSPECT.SECONDARYSECTIONID
                    from
                        dbo.PROSPECTSEGMENTATIONPROSPECT PROSPECT

                    left join dbo.PROSPECTSEGMENTATIONSECTION PRIMARYSECTION on
                        PRIMARYSECTION.ID = PROSPECT.PRIMARYSECTIONID
                    left join dbo.PROSPECTSEGMENTATIONVARIABLE PRIMARYVARIABLE on
                        PRIMARYVARIABLE.ID = PRIMARYSECTION.VARIABLEID

                    left join dbo.PROSPECTSEGMENTATIONSECTION SECONDARYSECTION on
                        SECONDARYSECTION.ID = PROSPECT.SECONDARYSECTIONID
                    left join dbo.PROSPECTSEGMENTATIONVARIABLE SECONDARYVARIABLE on
                        SECONDARYVARIABLE.ID = SECONDARYSECTION.VARIABLEID    

                    where
                        PRIMARYVARIABLE.PROSPECTSEGMENTATIONID   = @ID and
                        SECONDARYVARIABLE.PROSPECTSEGMENTATIONID = @ID

                    group by
                        PRIMARYSECTIONID, SECONDARYSECTIONID
                ) 
                select
                    PRIMARYVARIABLE.TYPE PRIMARYVARIABLETYPE,
                    PRIMARYSECTION.LABEL PRIMARYSECTIONLABEL,
                    PRIMARYSECTION.SEQUENCE PRIMARYSECTIONSEQUENCE,

          --Combined Secondary Variable Type

                    case
                        when SECONDARYVARIABLE.TYPE2CODE = 0 then SECONDARYVARIABLE.TYPE
                        else SECONDARYVARIABLE.TYPE + ' OR ' + SECONDARYVARIABLE.TYPE2
                    end as SECONDARYVARIABLETYPE,
          --Combined Secondary Variable Label

                    case
                        when SECONDARYSECTION.LABEL2 = '' then SECONDARYSECTION.LABEL
                        else SECONDARYSECTION.LABEL + char(13) + char(10) + SECONDARYSECTION.LABEL2
                    end as SECONDARYVARIABLELABEL,
                    SECONDARYSECTION.SEQUENCE SECONDARYSECTIONSEQUENCE,

                    isnull(GROUPEDPROSPECTS.COUNT,0) as SEGMENTCOUNT,

                    cast(PRIMARYSECTION.ID as nvarchar(36)) + 
                    cast(SECONDARYSECTION.ID as nvarchar(36))  as SEGMENTID

                from
                    dbo.PROSPECTSEGMENTATION    

                --Primary Variable

                left join dbo.PROSPECTSEGMENTATIONVARIABLE PRIMARYVARIABLE on
                    PRIMARYVARIABLE.PROSPECTSEGMENTATIONID = PROSPECTSEGMENTATION.ID and
                    PRIMARYVARIABLE.ISPRIMARY = 1
                left join dbo.PROSPECTSEGMENTATIONSECTION PRIMARYSECTION on
                    PRIMARYSECTION.VARIABLEID = PRIMARYVARIABLE.ID

                --Secondary Variable                   

                left join dbo.PROSPECTSEGMENTATIONVARIABLE SECONDARYVARIABLE on
                    SECONDARYVARIABLE.PROSPECTSEGMENTATIONID = PROSPECTSEGMENTATION.ID and
                    SECONDARYVARIABLE.ISPRIMARY = 0               
                left join dbo.PROSPECTSEGMENTATIONSECTION SECONDARYSECTION on
                    SECONDARYSECTION.VARIABLEID = SECONDARYVARIABLE.ID

                -- Results

                left join GROUPEDPROSPECTS on
                    GROUPEDPROSPECTS.PRIMARYSECTIONID =   PRIMARYSECTION.ID and
                    GROUPEDPROSPECTS.SECONDARYSECTIONID = SECONDARYSECTION.ID

        where
          PROSPECTSEGMENTATION.ID = @ID