PeopleSoft Wiki

Hi,

I have written something similar in PS Query to return the processing time for a process/job in the process request table. This query returns the process instance, process name, operator ID, run status, run control ID and processing time (which is process end date/time - process begin date/time expressed as seconds).

What you have explained is very close. My processing time expression looks like this:

You can ignore the case statement, the key to this is really the following:

to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60))

This gives the difference in seconds. However if you just want the answer in days it would be just:

to_char(round(ENDDTTM - BEGINDTTM))

Which is pretty much exactly what you have. Note the to_char is to format the output in character format which PSQuery seems to handle a lot better than numeric format.

In terms of formatting instead of messing around with dates I've just used a character expression as I just want to display the number of seconds. I figure you just want the output as the number of days?

My Query SQL looks like this:

SELECT A.PRCSINSTANCE
 , A.PRCSNAME
 , TO_CHAR(A.RUNDTTM
 ,'YYYY-MM-DD-HH24.MI.SS."000000"')
 , A.OPRID
 , A.RUNSTATUS
 , A.RUNCNTLID
 , (CASE WHEN ENDDTTM IS NULL THEN 'Not Available' 
    ELSE to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60)) END) 
   AS PROCESSING_TIME 
  FROM PSPRCSRQST A

If you try to get the output in date format you'll have all kinds of problems. For example what do you display as the year and month? What about the timestamp? I've found that I really just want the difference expressed as a year, month, day, hour, minute or second value and not in date format at all.

Hope that helps.

Here's an example that's similar to yours that may help.

Find all user profiles that were updated in the last year.

To do this in SQL:

select OPRID, LASTUPDDTTM
from PSOPRDEFN
where LASTUPDDTTM between (sysdate - 365) and sysdate
order by LASTUPDDTTM desc;

So find any user where the last update date/time LASTUPDDTTM is greater than or equal to 365 days (1 year) back from the current date, up until the current date.

Here's how you would create this expression in PS Query.

Note that I've replaced sysdate with meta-SQL - %CurrentDateTimeIn and %AddDate(%CurrentDateTimeIn, -365) to go back 365 days.

For the second part, you might want count all user profiles that were updated in the last year, grouped by each month. Here's the SQL you would write to do this:

select to_char(LASTUPDDTTM, 'Month') as MONTH, count(distinct OPRID)
from PSOPRDEFN
where LASTUPDDTTM >= (sysdate - 365) and LASTUPDDTTM <= sysdate
group by to_char(LASTUPDDTTM, 'Month');

The key here is the use of to_char to convert the month part of the LASTUPDDTTM field to a month. Here's how you would create the expression:

Add this as a field, and perform a count on the OPRID field.