Need to Implement TIMESTAMPDIFF in Cognos Report Studio

[gosoccer]

Hi guys,

The following SQL calculated the time that it took between the create date of certain transactions at different instant of time.
I'm trying to find the equivalent approach using Cognos Framework Manager or Report Studio Build In Functions.

Of course, there is no Begin and End Time Stamp but only the Creation Date Time Stamp.

SQL to run in DB2 is as following,
TIMESTAMPDIFF(8, CHAR(MAX([Physical View].[XXXX].[Create_Date]  ) - MIN( [Physical View].[XXXX].[Create_Date]  ))) || ':' || MOD(TIMESTAMPDIFF(4, CHAR(MAX( [Physical View].[XXXX].[Create_Date]   ) - MIN([Physical View].[XXXX].[Create_Date]   ))),
60) || ':' || MOD(TIMESTAMPDIFF(2, CHAR(MAX( [Physical View].[XXXX].[Create_Date]  ) - MIN( [Physical View].[XXXX].[Create_Date]  ))), 60)

Pls let me know if you have a way to do this in Cognos.

Thanks so much :) :)

[hespora]

I am not entirely certain whether I understood the task. Is the attached what you need? Only the "date" column comes from the data source.

[gosoccer]

Yes, that is what I'm trying to do and of course showing the Hours and Minutes under the Data Item.
Thanks and look forward to hear from you. :) :)

[hespora]

My data item just shows zero hours and minutes because my data source only has date stamps, no time stamps. If yours has timestamps, it should automatically work correctly.

Anyways, this is simply

Code Select Expand

maximum([Date] for report) - minimum([Date] for report)

which you then format as Time Interval.

If you need the difference calculated at different levels of aggregation (task id's or something), you just substitute "for report" with "for [Dimension]"

[gosoccer]

Thank you so much. I'll try that.

[gosoccer]

Yes, this is great. Now, I have to add the logic to get the minutes since some processes are taking minutes to end.
I'm using the following.
http://www-01.ibm.com/support/docview.wss?uid=swg21341679
Let me know if you have better suggestions.
Thx so much