Hello All,

I have to achieve following two points:

1. A query which can return all active users and their actions taken in last 2 years.
2. The list of users who have not approved/modified or made a change to system/files.

For the first point, (please correct me if I am wrong) I have an understanding that the history tab of user class interface shows the activities so far performed by a user. Using this understanding I have developed the following query:

SELECT *
FROM (SELECT ih.id,
le.entryvalue AS action,
au.loginid AS “user”,
(SELECT DateDiff (
‘YYYY’,
(SELECT localClientTime
FROM ( SELECT user_id,
TIMESTAMP AS localClientTime
FROM ITEM_HISTORY
ORDER BY localClientTime ASC)
WHERE user_id = ih.user_id AND ROWNUM <= 1),
(SELECT localClientTime
FROM ( SELECT user_id,
TIMESTAMP AS localClientTime
FROM ITEM_HISTORY
ORDER BY localClientTime DESC)
WHERE user_id = ih.user_id AND ROWNUM <= 1))
AS yeardiff
FROM DUAL)
AS yeardiff,
ih.TIMESTAMP AS localClientTime,
ih.DETAILS AS details,
ih.comments
FROM ITEM_HISTORY ih
LEFT OUTER JOIN listentry le
ON le.entryid = ih.action AND le.parentid = 4456
LEFT OUTER JOIN agileuser au
ON au.id = ih.user_id
WHERE 1 = 1 –and user_id = 6607658
AND au.enabled = 1
and ih.class = 11605)
WHERE yeardiff >= 2
ORDER BY localClientTime ASC;

Another query which I have developed is as follows:

SELECT *
FROM (SELECT a.userid,
au.loginid,
(SELECT DateDiff (‘YYYY’,
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP ASC)
WHERE userid = a.userid AND ROWNUM <= 1),
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP DESC)
WHERE userid = a.userid AND ROWNUM <= 1))
AS yeardiff
FROM DUAL)
AS yeardiff,
a.timestamp,
ah.object,
ah.details
FROM ADMIN_HISTORY a
INNER JOIN admin_history_details ah
ON a.detailid = ah.id
INNER JOIN agileuser au
ON AU.ID = a.userid AND au.enabled = 1)
WHERE yeardiff >= 2

Where DateDiff is a UDF I have created to calculate years. 

For the second point, I do not have any sort of understanding so far. Is there any way by which I can achieve both points using a single query?

I have to achieve both points using only SQL query.