2 Replies Latest reply on Dec 1, 2011 12:14 PM by jemjanik

    Querying for groups created and by whom - some rows are duplicates

    jemjanik

      Here is my query.  Note: I already know that this query does not yet take into account that some of these groups may have been deleted.  I figured let me me get the groups created first and then I add to the query to constrain out those that have been deleted.

       

      select     jivedw_user.username, 
          jivedw_user.email, 
          jivedw_container.container_id, 
          jivedw_container.name as container_name,
          activity_ts
      from     jivedw_activity_fact,
          jivedw_user,
          jivedw_container,
          jivedw_object
      where    activity_type=20 and
          direct_object_type=700 and
          jivedw_activity_fact.direct_dw_object_id=jivedw_object.dw_object_id and 
          jivedw_object.object_id=jivedw_container.container_id and
          jivedw_container.container_type=700 and
          jivedw_container.name=jivedw_object.name and
          jivedw_activity_fact.user_id=jivedw_user.user_id and
          jivedw_user.username='mitchelj'
      order by container_id
      

       

       

      Some of the rows are duplicates.  Here's some of the sample output (minus the column of my email address).  The groups in bold still exist & the others have been deleted so that doesn't seem to be it.

      Any ideas of why I'm getting duplicate rows?

       

      mitchelj2322Engage Technical Support7/8/10 7:43 PM
      mitchelj2322Engage Technical Support7/8/10 7:43 PM
      mitchelj2648jem-migration-test7/30/10 6:33 PM
      mitchelj2648jem-migration-test7/30/10 6:33 PM
      mitchelj2881Negotiation Roadmap8/19/10 6:18 PM
      mitchelj2881Negotiation Roadmap8/19/10 6:18 PM
      mitchelj3565Communications & Marketing10/20/10 8:59 PM
      mitchelj3656What's in Your View10/29/10 5:07 PM
      mitchelj4379Naperville Swap & Shop1/20/11 5:13 PM
      mitchelj4604Naperville News2Use2/9/11 7:19 PM
        • Re: Querying for groups created and by whom - some rows are duplicates
          deesteel

          Personally, I wouldn't use the Jive Analytics tables for a query of this sort, unless of course you have to for some reason. Here is a SQL query using the jivesgroup table that should return what you need. This query is written for oracle database, the dates will need to be tweeked for the proper syntax for epoch time conversion if your not using oracle.

           

          -- Social Groups by Modification Date
          -- Modification Date means any piece of content has been modified or created
          -- For example a document being created, or a discussion being replied to
          -- This query returns social groups created by a particular user
          SELECT jg.groupid, jg.name, jg.displayname, u.username, u.email,
          TO_CHAR(TO_DATE('01-JAN-1970 00:00','DD-MON-YYYY HH24:MI')+(jg.modificationdate/1000/60/60/24),'MM/DD/YYYY HH24:MI') as readable_modified_date,
          TO_CHAR(TO_DATE('01-JAN-1970 00:00','DD-MON-YYYY HH24:MI')+(jg.creationdate/1000/60/60/24),'MM/DD/YYYY HH24:MI') as readable_created_date,
          TO_CHAR(TO_DATE('01-JAN-1970 00:00','DD-MON-YYYY HH24:MI')+(jg.modificationdate/1000/60/60/24),'YYYYmmdd') as date_modified
          FROM jivesgroup jg, jiveuser u
          where jg.userid = u.userid
          and u.username = 'myersdd'
          ORDER BY date_modified asc