topic_activity
-------------------------------------------
PK  activity_id                numeric(6,0)
PK  topic_id                   numeric(6,0)

activity
-------------------------------------------
PK  activity_id                numeric(6,0)
    activity_date              date
    activity_time              time
    activity_hwm               int
    activity_dequeue_count     int
    activity_enqueue_count     int
    activity_interval          int
    activity_object_name       varchar(100)
    activity_subscription_name varchar(100)

topics
-------------------------------------------
PK  topic_id                   numeric(6,0)
    topic_name                 varchar(100)
    topic_queue_manager        varchar(48)

create procedure sp_getTotals
	@targetDate	varchar(10)
as
select
    sum(activity.activity_enqueue_count) as totalEnqueue,    
    max(activity.activity_hwm) as maxHwm,
    topics.topic_name
from
    topic_activity,
    activity,
    topics
where
    topic_activity.topic_id = topics.topic_id and
    topic_activity.activity_id = activity.activity_id and
    activity.activity_date = @targetDate
group by
    topics.topic_name

CREATE PROCEDURE dbo.sp_getDistinctSubscriptions
	@topicName		varchar(100)
AS
select
    count(distinct activity.activity_subscription_name)
from
    activity, topic_activity, topics
where
    activity.activity_id = topic_activity.activity_id and
    topic_activity.topic_id = topics.topic_id and
    topics.topic_name = @topicName

select sum(activity.activity_enqueue_count) as totalEnqueue
     , max(activity.activity_hwm) as maxHwm
     , topics.topic_name
     , ( select count(distinct A.activity_subscription_name)
           from topics as T
         inner
           join topic_activity as TA
             on TA.topic_id = T.topic_id 
         inner
           join activity as A
             on A.activity_id = TA.activity_id
          where T.topic_name = topics.topic_name ) as DistinctSubscriptions
  from activity
inner
  join topic_activity
    on topic_activity.activity_id = activity.activity_id
inner
  join topics
    on topic_activity.topic_id = topics.topic_id
 where activity.activity_date = @targetDate
group 
    by topics.topic_name