USP_DATALIST_FAFNFGCAMPAIGNLEVEL

CREATE procedure dbo.USP_DATALIST_FAFNFGCAMPAIGNLEVEL(@NFGCAMPAIGNID uniqueidentifier)
as
    set nocount on;

    select FNCL.ID,
           FNCL.NAME,
           (select PARENTSITE.ID from dbo.FAFNFGCAMPAIGNLEVEL as PARENTSITE where PARENTSITE.HIERARCHYPATH = FNCL.HIERARCHYPATH.GetAncestor(1)) as PARENTID,
       C.NAME AS CONTACTNAME,
       isnull(VL.TOTALRAISED, 0) AS TOTALRAISED,
       FNCL.TOTALREVENUEGOAL,
       CASE
              WHEN (select count(*) from  FAFNFGCAMPAIGNLEVELDISPLAYSITE Where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = FNCL.ID) < 4
                    THEN
                        COALESCE((select dbo.UDA_BUILDLIST(SITE.NAME) FROM dbo.SITE inner join dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE ON FAFNFGCAMPAIGNLEVELDISPLAYSITE.SITEID = SITE.ID Where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = FNCL.ID),'')
              WHEN (select count(*) from  FAFNFGCAMPAIGNLEVELDISPLAYSITE Where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = FNCL.ID) = 4
                    THEN
                        COALESCE((select dbo.UDA_BUILDLISTWITHDELIMITER(FNFGLS.NAME,',') FROM (select top 3 SITE.NAME from dbo.SITE inner join dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE ON FAFNFGCAMPAIGNLEVELDISPLAYSITE.SITEID = SITE.ID Where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = FNCL.ID) FNFGLS) ,'') +', and 1 other'
            ELSE
                  COALESCE((select dbo.UDA_BUILDLISTWITHDELIMITER(FNFGLS.NAME,',') FROM (select top 3 SITE.NAME from dbo.SITE inner join dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE ON FAFNFGCAMPAIGNLEVELDISPLAYSITE.SITEID = SITE.ID Where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = FNCL.ID) FNFGLS) ,'') +', and ' + convert(varchar(100),(select COUNT(*)-3 from FAFNFGCAMPAIGNLEVELDISPLAYSITE where NFGCAMPAIGNLEVELID = FNCL.ID)) +' others'
      END AS SITE
    from dbo.FAFNFGCAMPAIGNLEVEL FNCL
  LEFT JOIN dbo.CONSTITUENT C on C.ID = FNCL.CONTACTID
  left join dbo.V_NFGLEVEL_SUMMARYINFO VL (nolock) on FNCL.ID = VL.ID 
    where FNCL.NFGCAMPAIGNID = @NFGCAMPAIGNID and FNCL.HIERARCHYPATH.GetLevel()> 1
  order by FNCL.HIERARCHYPATH;