USP_DATABASESPACEUSED_TOPNOUTPUT

CREATE procedure dbo.[USP_DATABASESPACEUSED_TOPNOUTPUT]
(
  @MAXROWS integer = 20,
  @ORDERBY tinyint = 0,
  @BUSINESSPROCESSCATALOGID uniqueidentifier = null,
  @OWNERID uniqueidentifier = null
)
as begin
  set nocount on;

  with [BUSINESSPROCESSESWITHUNIQUEPAGEIDS] ([ID], [RECORDTYPEID]) as
  (
    select [BUSINESSPROCESSCATALOG].[ID], [BUSINESSPROCESSCATALOG].[RECORDTYPEID]
    from dbo.[BUSINESSPROCESSCATALOG]
    inner join dbo.[PAGEDEFINITIONCATALOG] on [PAGEDEFINITIONCATALOG].[CONTEXTRECORDTYPEID] = [BUSINESSPROCESSCATALOG].[RECORDTYPEID]
    group by [BUSINESSPROCESSCATALOG].[ID], [BUSINESSPROCESSCATALOG].[RECORDTYPEID]
    having count(*) = 1
  ), [OUTPUTBYPARAMETERSET] ([BUSINESSPROCESSPARAMETERSETID], [BUSINESSPROCESSCATALOGID], [TIMESRUN], [ENDEDON], [ROWS], [RESERVEDKB], [DATAKB], [INDEXKB], [UNUSEDKB]) as 
  (
    select
      [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID],
      [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID],
      count(distinct [BUSINESSPROCESSSTATUS].[ID]) as [TIMESRUN],
      max([BUSINESSPROCESSSTATUS].[ENDEDON]) as [ENDEDON],
      sum([DATABASESPACEUSED].[ROWS]) as [ROWS],
      sum([DATABASESPACEUSED].[RESERVEDKB]) as [RESERVEDKB],
      sum([DATABASESPACEUSED].[DATAKB]) as [DATAKB],
      sum([DATABASESPACEUSED].[INDEXKB]) as [INDEXKB],
      sum([DATABASESPACEUSED].[UNUSEDKB]) as [UNUSEDKB]
    from dbo.[DATABASESPACEUSED]
    inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[ID] = [DATABASESPACEUSED].[BUSINESSPROCESSOUTPUTID]
    inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID]
    group by [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID], [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]
  )
  select top (@MAXROWS)
    [BUSINESSPROCESSCATALOG].[NAME] as [PROCESSNAME],
    isnull(case when [APPUSER].[DISPLAYNAME] = '' then [APPUSER].[USERNAME] else [APPUSER].[DISPLAYNAME] end, '') as [OWNER],
    isnull([SITE].[NAME], '') as [SITE],
    [OUTPUTBYPARAMETERSET].[TIMESRUN],
    [OUTPUTBYPARAMETERSET].[ENDEDON],
    [OUTPUTBYPARAMETERSET].[ROWS],
    [OUTPUTBYPARAMETERSET].[RESERVEDKB],
    [OUTPUTBYPARAMETERSET].[DATAKB],
    [OUTPUTBYPARAMETERSET].[INDEXKB],
    [OUTPUTBYPARAMETERSET].[UNUSEDKB],
    [OUTPUTBYPARAMETERSET].[BUSINESSPROCESSPARAMETERSETID],
    [BUSINESSPROCESSCATALOG].[ID] as [BUSINESSPROCESSCATALOGID],
    [PAGEDEFINITIONCATALOG].[ID] as [BUSINESSPROCESSSTATUSPAGEID]
  from [OUTPUTBYPARAMETERSET]
  inner join dbo.[BUSINESSPROCESSCATALOG] on [BUSINESSPROCESSCATALOG].[ID] = [OUTPUTBYPARAMETERSET].[BUSINESSPROCESSCATALOGID]
  left outer join dbo.[BUSINESSPROCESSINSTANCE] on [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID] = [OUTPUTBYPARAMETERSET].[BUSINESSPROCESSPARAMETERSETID]
  left outer join dbo.[APPUSER] on [APPUSER].[ID] = [BUSINESSPROCESSINSTANCE].[OWNERID]
  left outer join dbo.[SITE] on [SITE].[ID] = [BUSINESSPROCESSINSTANCE].[SITEID]
  left outer join [BUSINESSPROCESSESWITHUNIQUEPAGEIDS] on [BUSINESSPROCESSESWITHUNIQUEPAGEIDS].[ID] = [BUSINESSPROCESSCATALOG].[ID]
  left outer join dbo.[PAGEDEFINITIONCATALOG] on [PAGEDEFINITIONCATALOG].[CONTEXTRECORDTYPEID] = [BUSINESSPROCESSESWITHUNIQUEPAGEIDS].[RECORDTYPEID]
  where (@BUSINESSPROCESSCATALOGID is null or [OUTPUTBYPARAMETERSET].[BUSINESSPROCESSCATALOGID] = @BUSINESSPROCESSCATALOGID)
  and (@OWNERID is null or [BUSINESSPROCESSINSTANCE].[OWNERID] = @OWNERID)
  order by case @ORDERBY when 0 then [OUTPUTBYPARAMETERSET].[RESERVEDKB]
                         when 1 then [OUTPUTBYPARAMETERSET].[DATAKB]
                         when 2 then [OUTPUTBYPARAMETERSET].[INDEXKB]
                         when 3 then [OUTPUTBYPARAMETERSET].[UNUSEDKB]
                         when 5 then [OUTPUTBYPARAMETERSET].[ROWS]
                         when 4 then datediff(day, [OUTPUTBYPARAMETERSET].[ENDEDON], getdate()) end desc;

  return 0;
end