How to report on work items in JRS in certain state for certain time
Accepted answer
It is possible using the following custom query for Data Warehouse data source:
SELECT T2.REFERENCE_ID
T1.REQUEST_STATUS,
T1.PREV_REQUEST_STATE,
T1.REC_DATETIME,
T1.CHANGE_DATE,
T1.DAYS_IN_STATE,
T1.REQUEST_NAME,
T1.PREV_REQUEST_STATUS,
T1.REQUEST_STATE,
T1.REQUEST_ID,
T1.PREV_REQUEST_STATE_ID,
T1.REQUEST_STATE_ID,
T1.PREV_REQUEST_STATUS_ID,
T1.REQUEST_STATUS_ID,
T1.REQUEST_STATE_HISTORY_ID,
T1.EXTERNAL_KEY1,
T1.EXTERNAL_KEY2,
T1.ISSOFTDELETED
FROM RIDW.VW_REQUEST_STATE_HISTORY T1
JOIN RIODS.REQUEST T2 ON T1.REQUEST_ID = T2.REQUEST_ID
WHERE (T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_STATE_HISTORY_ID <> -1 AND T1.REQUEST_STATE_HISTORY_ID IS NOT NULL)