Using Dimensions CM 12.1, is there a way to get a status of all open requests on a given date in the past? For example, I need to get a count of how many requests were open (grouped by their status) on the last day of each month of this year. I have been trying to look at SQL queries based on the pcms_chdoc_history table, however, I am unable to figure it out. Any ideas?
Another follow-up question...I've also been asked to filter to only to include requests in the NEW, REVIEW, ASSIGNED, or DEV states (basically requests that are open and have not being tested). And I've been asked to count by an attribute called "Priority" (things like Critical, High, Major, Minor). I've tried a few things, but so far with no luck.
( SELECT ch_uid, MAX(ch_sub.date_amend) as max_date
FROM pcms_chdoc_history ch_sub
WHERE ch_sub.date_amend < '11/11/2012'
GROUP BY ch_sub.ch_uid) ch2 ON ch2.ch_uid = ch.ch_uid AND ch.date_amend = ch2.max_date
WHERE ch.status IN ('NEW', 'REVIEW', 'ASSIGNED', 'DEV') -- NEW!
GROUP BY ch.status
I have also replaced COUNT(*) with COUNT(DISTINCT ch.ch_uid) to avoid several same requests mistakenly summed, which may appear due to several rows in request history dated by the exactly same timestamp.
As for grouping by user attribute (as 'Priority') - I think you need to join with other views (PCMS_ATTRIBUTES_CHDOC or PCMS_CHDOC_ATTR_HISTORY).
The information about all these views is provided in "Reports Guide" PDF.