1 Reply Latest reply on Feb 27, 2015 12:30 PM by Caton Guilbault

    Looking for a Query to return Unique views on ideas in a Space

    mack_torres

      Does anyone already have a SQL query that would return Unique Views on Ideas by Space? We are trying to get the same information that Impact Metrics returns but for ideas. I believe CMR will give us most of what we need but we also want to see who viewed an Idea. I believe that the unique views are in the jivedw_activity_fact table and I can join the jiveUser table to determine who viewed it and I think there is a place container I can join to get the space name, but not sure what Object type is Ideas?

        • Re: Looking for a Query to return Unique views on ideas in a Space
          Caton Guilbault

          Hi Mahal,

           

          I don't have a single query with all of this information handy but combining  results for these 2 will get you what you want.

           

          Query for Views of Ideas:

          select

              jivedw_day.begin_ts,

              jivedw_activity_fact.dw_container_id,

              jivedw_container.name,

              jivedw_object.object_id,

              jivedw_activity_fact.direct_object_type,

              jivedw_activity_fact.indirect_object_type,

              jivedw_activity_fact.indirect_dw_object_id,

              jivedw_activity_fact.day_id

             

          from jivedw_activity_fact

          join jivedw_object on jivedw_object.dw_object_id = jivedw_activity_fact.direct_dw_object_id

          left join jivedw_day on jivedw_day.day_id = jivedw_activity_fact.day_id

          left join jivedw_container on jivedw_container.dw_container_id = jivedw_activity_fact.dw_container_id

           

          where   jivedw_day.begin_ts > '2/1/2015'

              and jivedw_day.begin_ts <= '3/1/2015'

              and jivedw_activity_fact.activity_type = 10

              and jivedw_activity_fact.direct_object_type = 3227383

           

          Query to return all Ideas and where they live:

          select idea.ideaid,juser.username,juser.userid,idea.score,stage.stagename, idea.containertype,community.name as "Space Name"

          from jiveidea idea

          left join jiveideastage stage on stage.stageid = idea.stageid

          left join jivecommunity community on community.communityid = idea.containerid

          left join jiveuser juser on juser.userid = idea.userid