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

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

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