2 Replies Latest reply on Jun 11, 2014 2:19 PM by don840

    Core Application DB- Count of Discussions created in the User Group

    don840

      Hi All,

      I have written a query that tells me count of Discussions by the create date.  I am also able to breakdown the counts by jive community space, eg, 'Support'. I am unable to isolate this for Special User Groups. In jive community, special user groups show as blank. How can I get the counts of discussions posted under a special user group? . I need to know what all tables to join to get to the special user group names, and the # of threads under each.

       

      My original query is below:-

      SELECT
      jivemessage.threadid,  --jivemessage.subject,
      jivecommunity.name,

      MM') as "msgcreatedate"
      , count (CASE WHEN jivemessage.parentmessageid is null AND jivequestion.threadid is NULL THEN 1 ELSE NULL END) as "# Disucssions"
        
      FROM   
      jivethread LEFT JOIN jivemessage ON jivethread.threadid = jivemessage.threadid
      LEFT JOIN jiveuser ON jiveuser.userid = jivemessage.userid
      LEFT JOIN jivecommunity ON jivethread.containerid = jivecommunity.communityid

      Where 1=1
      group by jivecommunity.name, 
      to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')
      order by to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')desc

        • Re: Core Application DB- Count of Discussions created in the User Group
          Caton Guilbault

          Hi Donna,

           

          Since Jive has multiple group types we wrote this so the user could specify the one they are looking for in the where statements. The primary group tables are jivesgroup, jivesgroupmember, and jivesgroupprop.  I don't have a query handy for threads but here is the one we use for documents. Replacing the jivedocuemnt table with jive thread should be fairly straightforward.

           

          select 
              doc.internaldocid,
              doc.documentid, 
              person.username as "Original Author", 
              sgroup.name as "Space Name",
              to_timestamp(doc.creationdate/1000) as "creationdate",
              to_timestamp(docversion.creationdate/1000) as "VersionCreatedate", 
              to_timestamp(docversion.modificationdate/1000) as "Modificationdate",
              docversion.userid as "Last Modified UserID", 
              jcount.viewcount,docversion.title
              
          from jivedocument doc
          left join jiveuser person on person.userid = doc.userid
          left join jiveobjecttype objtype on objtype.typeid = doc.containertype
          left join jivesgroup sgroup on sgroup.groupid = doc.containerid
          left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
          left join jiveviewcount jcount on jcount.objectid = doc.internaldocid
          
          where docversion.state = 'published' and objtype.code = 'socialgroup' and jcount.objecttype = '102'
          
          order by jcount.viewcount desc
          
          1 person found this helpful
            • Re: Core Application DB- Count of Discussions created in the User Group
              don840

              Thanks Caton. That helped. Below is the updated query -

               

              SELECT

              to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM') as "msgcreatedate" 

              , count (CASE WHEN jivemessage.parentmessageid is null AND jivequestion.threadid is NULL THEN 1 ELSE NULL END) as "# Disucssions"                          

              FROM   

              jivethread LEFT JOIN jivemessage ON jivethread.threadid = jivemessage.threadid

              LEFT JOIN jiveanswer ON jivemessage.messageid   = jiveanswer.messageid

              LEFT JOIN jivequestion ON jivethread.threadid   = jivequestion.threadid

              LEFT JOIN jiveuser ON jiveuser.userid = jivemessage.userid

              LEFT join jiveobjecttype objtype on objtype.typeid = jivethread.containertype

              LEFT join jivesgroup on jivesgroup.groupid = jivethread.containerid

              Where

              1=1

              and jivesgroup.grouptype IN ('1', '2')

              group by

              to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')

              order by to_char( to_timestamp(jivemessage.creationdate/1000) , 'YYYY-MM')desc