UFN_KPIINSTANCE_GETFOLDERS

CREATE function dbo.[UFN_KPIINSTANCE_GETFOLDERS]
(
  @CURRENTAPPUSERID uniqueidentifier = null
)

returns @FOLDERS table
(
  [FOLDERID] uniqueidentifier, 
  [PARENTFOLDERID] uniqueidentifier, 
  [FOLDERNAME] nvarchar(255),
  [DISPLAYORDER] int, 
  [COUNT] int
)

as

begin

  declare @KPISTOINCLUDE table 
  (
    [FOLDERID] uniqueidentifier,         
    [PARENTFOLDERID] uniqueidentifier,
        [FOLDERNAME] nvarchar(max),
        [DISPLAYORDER] int,
    [COUNT] int
  );

  insert into @KPISTOINCLUDE ([FOLDERID], [PARENTFOLDERID], [FOLDERNAME], [DISPLAYORDER], [COUNT])
      select 
      isnull(f.[ID], '00000000-0000-0000-0000-000000000000') as [FOLDERID],
      isnull([PARENTFOLDERID], '00000000-0000-0000-0000-000000000000') as [PARENTFOLDERID],
      f.[NAME] as [FOLDERNAME],
      f.[DISPLAYORDER],
      count(distinct k.[ID]) as [COUNT]
      from dbo.[KPIINSTANCEFOLDER] f
    left join dbo.[KPIINSTANCE] k on f.[ID] = k.[FOLDERID]
      group by 
          f.[ID],
          f.[PARENTFOLDERID],
          f.[NAME],
          f.[DISPLAYORDER];

    /* rollup node counts */
    with [ROLLUP] as
    (  
      select 
        [FOLDERID],
        [FOLDERID] as [PARENTFOLDERID],
        [FOLDERNAME],
    [DISPLAYORDER],
    [COUNT]
      from @KPISTOINCLUDE f

      union all 

      select 
        f.[FOLDERID],
        r.[PARENTFOLDERID],
        f.[FOLDERNAME],
    f.[DISPLAYORDER],
    f.[COUNT]
      from @KPISTOINCLUDE f
      inner join [ROLLUP] r on f.[PARENTFOLDERID] = r.[FOLDERID]
    )  

  insert into @FOLDERS ([FOLDERID],[PARENTFOLDERID], [FOLDERNAME], [DISPLAYORDER], [COUNT])
    select
      f.[FOLDERID],
      f.[PARENTFOLDERID],
      f.[FOLDERNAME],
      f.[DISPLAYORDER],
      sum(r.[COUNT]) [COUNT]
    from [ROLLUP] r
    inner join @KPISTOINCLUDE f on r.[PARENTFOLDERID] = f.[FOLDERID]
    group by 
      f.[FOLDERID],
      f.[PARENTFOLDERID],
      f.[FOLDERNAME],
      f.[DISPLAYORDER]

    return
end