Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Help formulating a query

    Hi Guys,

    I need some help formulating a query to get the data i'm after, the database structure being used is as follows:

    Code:
    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)
    I'm performing the following query on the data inside a stored procedure:

    Code:
    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
    What i'd like to do is divide the result of totalEnqueue field in the stored procedure above by the number returned from this query:

    Code:
    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
    and then replace the totalEnqueue with the result of this operation. I've been beating at this for a while and cant figure out how to do it in one query, currently i'm trigerring several individual queries which I believe is the cause of the slow performance being experienced.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    Thanks r937, that worked like a charm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •