How to get the status of all open requests on a given date in the past?


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?

  • You may start with something like:

    SELECT ch.status, COUNT(*)

    FROM pcms_chdoc_history ch INNER JOIN

    ( 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

    GROUP BY ch.status

    The query is pretty heavy, it took about 2 minutes with 8000 requests on my DB. So you may tune it further or add more filtering, indices to speed it up.

  • Thanks alot Alexander!

    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.

    Any direction would be greatly appreciated!

  • Hello,

    Filtering by status should be as simple as adding WHERE condition with IN clause

    SELECT ch.status, COUNT(DISTINCT ch.ch_uid) -- NEW!

    FROM pcms_chdoc_history ch INNER JOIN

    ( 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.