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?
Parents

  • 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.
Reply

  • 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.
Children
No Data