-
Re: Core Application DB- Count of Discussions created in the User Group
Caton Guilbault Jun 10, 2014 2:32 PM (in response to don840)1 person found this helpfulHi 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
-
Re: Core Application DB- Count of Discussions created in the User Group
don840 Jun 11, 2014 2:19 PM (in response to Caton Guilbault)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
-