Project and Portfolio Management Practitioners Forum
cancel

How to calculate the duration between two dates in PPM using Rules

SOLVED
Go to solution
Highlighted
Victoria Warndo
Valued Contributor.

How to calculate the duration between two dates in PPM using Rules

How do I set up the Rules in a request type in PPM workbench to calculate actual duration in days between two dates?
On a project request type, I have an actual start date and an actual end date and I need to determine the duration between the two dates in days. I'm not certain how to set up the sql in the Rules in the request type to do the calculation.
Thank you for any assistance!
7 REPLIES
Jim Esler
Acclaimed Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

You can use any of the standard Oracle date functions in an expression in the sql for a rule. Common functions are described at http://psoug.org/reference/date_func.html. Set up the rule to access the tokens for the fields and plug the tokens into the sql query as appropriate. Oracle will do the calculations for you.

A simple example (without tokens) that adds 14 days to today:

select sysdate+14, sysdate+14 from dual
Marlene Mazzeo
Super Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

Here is an example (you would just replace the parameters with your tokens):

SELECT To_Char(round((To_Date(parameter11,'YYYY-MM-DD HH24:MI:SS')
- To_Date(parameter20,'YYYY-MM-DD HH24:MI:SS'))*24,1)),
To_Char(round((To_Date(visible_parameter11,'YYYY-MM-DD HH24:MI:SS')
- To_Date(visible_parameter20,'YYYY-MM-DD HH24:MI:SS'))*24,1)) FROM dual

Above also assumes you are storing both fields in parameters. If you have one field that is stored in a date field and another in one of the parameter fields, then above would not work, since you would be dealing with two different types of data.

You can sort of see what I mean here:

and to_date(a.PARAMETER30,'yyyy-mm-dd hh24:mi:ss') >= ADD_MONTHS(SYSDATE, - '[VP.P_NUM]' )
and a.PARAMETER30 <= TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss')

Where I change parameter30 to date, then where I change sysdate to char in order to do the comparisons.

Hope above examples help.
Victoria Warndo
Valued Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

I've set up my sql in the Rules substituting in the tokens in the date parameters formula but I am still getting an error -

Error in SQL in Rule 10 -{1}:

SELECT To_Char(round((To_Date(REQ.P.P_ACT_COMPLETE_DATE,'YYYY-MM-DD HH24:MI:SS')
- To_Date(REQ.P.P_ACT_START_DATE,'YYYY-MM-DD HH24:MI:SS'))*24,1)),
To_Char(round((To_Date(REQ.VP.P_ACT_COMPLETE_DATE,'YYYY-MM-DD HH24:MI:SS')
- To_Date(REQ.VP.P_ACT_START_DATE,'YYYY-MM-DD HH24:MI:SS'))*24,1)) FROM dual

Both the P_ACT_COMPLETE_DATE and the P_ACT_START_DATE are defined as date fields. The result field is defined as a numeric text field. I read the date information from the link to sql suggested and figure that the result field should be numeric.

Any suggestions on what I am missing? Thanks!
Jim Esler
Acclaimed Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

You need to put brackets around your tokens:
[REQ.P.NAME].
Victoria Warndo
Valued Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

Thank you for the info on brackets, as you can tell I am a newbie with PPM. I added the brackets and now see the dates substituted into the sql, but I am still receiving an sql error. I have played around with the date, stripping it back to a very simple format, but continue to receive the sql error.

This what the sql statements look like now:
SELECT To_Char(round((To_Date([REQ.P.P_ACT_COMPLETE_DATE],'YYYY-MM-DD HH24:MI:SS')
- To_Date([REQ.P.P_ACT_START_DATE],'YYYY-MM-DD HH24:MI:SS'))*24,1)),
To_Char(round((To_Date([REQ.VP.P_ACT_COMPLETE_DATE],'YYYY-MM-DD HH24:MI:SS')
- To_Date([REQ.VP.P_ACT_START_DATE],'YYYY-MM-DD HH24:MI:SS'))*24,1)) FROM dual


This is the error information:
SELECT To_Char(round((To_Date(2011-05-06 00:00:00,'YYYY-MM-DD HH24:MI:SS')
- To_Date(2011-05-01 00:00:00,'YYYY-MM-DD HH24:MI:SS'))*24,1)),
To_Char(round((To_Date(May 6,2011,'YYYY-MM-DD HH24:MI:SS')
- To_Date(May 1,2011,'YYYY-MM-DD HH24:MI:SS'))*24,1)) FROM dual (KNTA-10521)

Thank you for your continuing assistance!
Jim Esler
Acclaimed Contributor.
Solution

Re: How to calculate the duration between two dates in PPM using Rules

The dates need to be enclosed in single quotes. Also the date formats need to match the strings you are entering. Your query should look like:

SELECT To_Char(round((To_Date('2011-05-06 00:00:00','YYYY-MM-DD HH24:MI:SS')
- To_Date('2011-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))*24,1)),
To_Char(round((To_Date('May 6,2011','MONTH DD,YYYY')
- To_Date('May 1,2011','MONTH DD,YYYY'))*24,1))FROM dual
Victoria Warndo
Valued Contributor.

Re: How to calculate the duration between two dates in PPM using Rules

Yes, that worked! Thank you for assistance and patience in working through this with me!