When did those reports last run?

Here's an interesting piece of code that I developed recently that you may find to be of use. It is aimed at administrators and uses a combination of the statistics (EUL5_QPP_STATS) and the documents (EUL5_DOCUMENTS) EUL tables to determine whether a workbook that has been saved to the database has been run recently.

SQL Code:

SELECT DISTINCT
DOCS.DOC_CREATED_BY DOC_OWNER,
DOCS.DOC_NAME DOC_NAME,
(SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) LAST_RUN
FROM
EUL5_QPP_STATS STATS,
EUL5_DOCUMENTS DOCS
WHERE
DOCS.DOC_NAME NOT LIKE 'Workbook%' AND
STATS.QS_CREATED_DATE(+) > :CUTOFF AND
DOCS.DOC_CREATED_DATE < :CUTOFF AND
DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)
HAVING :RUN_DATE >=
NVL((SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')
GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_NAME, STATS.QS_DOC_NAME
ORDER BY LAST_RUN DESC, DOCS.DOC_CREATED_BY, DOCS.DOC_NAME;

Note: The DOCS.DOC_NAME NOT LIKE 'Workbook%' code omits workbooks that were saved using the generic name that Discoverer always users. You might want to find these later and discuss removing them with your users!

Code explanation
The above code has a number of facets about it that I think I ought to explain a little more in depth. These are:

• The reason for the DISTINCT is to make sure that if the workbook was run more than once during a day that it is not counted twice.

• The :CUTOFF parameter prompts for a date. Whatever date is entered here is used to find workbooks that have been saved to the database prior to that date and have either run since that date or never run at all

• The :RUN_DATE parameter prompts for a date also. It is used to find workbooks that have not been run after this date.

• The following code is interesting because it finds both workbooks that have been run and those which have never run, returning NULL if the workbook did not run at all in the period between the two parameters

HAVING :RUN_DATE >=
NVL((SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')

Sample Output
Here's some sample output, from TOAD, that I ran this evening against my own test database using 01-JUN-2006 for :CUTOFF and 01-SEP-2006 for :RUN_DATE.



Note: If you have never used TOAD or have always wanted to try it but never got around to it please let me know by email at michael@learndiscoverer.com. I have partnered with the manufacturer and have a trial license which you can use for evaluation purposes.