UFN_MKTRECORDSOURCE_GETUSAGESTATISTICS

CREATE function dbo.[UFN_MKTRECORDSOURCE_GETUSAGESTATISTICS]()
returns table
as
  return (
    select
      [RS].[ID],

      --Segment count

      (select count(*) from dbo.[MKTSEGMENT] where [MKTSEGMENT].[QUERYVIEWCATALOGID] = [RS].[ID]) 
      as [SEGMENTCOUNT],

      --Mailing count

      (select count(distinct [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID])
         from dbo.[MKTSEGMENTATIONSEGMENT]
         left join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
         left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[ID]
         left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
        where [MKTSEGMENT].[QUERYVIEWCATALOGID] = [RS].[ID]
           or [MKTLIST].[RECORDSOURCEID] = [RS].[ID]) 
      as [MAILINGCOUNT],

      --List count

      (select count(*) from dbo.[MKTLIST] where [MKTLIST].[RECORDSOURCEID] = [RS].[ID]) 
      as [LISTCOUNT],

      --Export count

      (select count(distinct [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID])
         from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
        where [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] = [RS].[ID]
           or [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] = 
                  (select [CONSOLIDATEDQUERYVIEWCATALOGID]
                     from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
                    where [ID] = [RS].[ID])) 
      as [EXPORTCOUNT],

      --Query count

      dbo.[UFN_MKTRECORDSOURCE_GETUSAGESTATISTICSQUERYCOUNT]([RS].[ID]) as [QUERYCOUNT]

    from dbo.[MKTRECORDSOURCE] as [RS]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([RS].[ID]) = 1
  );