Highlighted
shawnmhooper Absent Member.
Absent Member.
11145 views

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?
0 Likes
3 Replies
Micro Focus Expert
Micro Focus Expert

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


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.
0 Likes
shawnmhooper Absent Member.
Absent Member.

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


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!
0 Likes
Micro Focus Expert
Micro Focus Expert

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


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.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.