Create report for jobs that have closed date is within a time period.

2619
2
04-11-2013 10:21 AM
TimJacobsen
New Contributor III
I'm trying to create a report of jobs that have closed within the last 60 days.  It is a report that will get run on a monthly basis so the end date can't be a hard coded value.

Does anyone know how to get something similar to this:

select end_date, end_date - (to_number(substr(tz_offset( DBTIMEZONE ),2, 2))/24) as ActualTimeMinus from jtx.jtx_jobs where jtx.jox.jobs.end_date in (sys:cur_date - 60)

into the report manager sql view?

Any other suggestions?
0 Kudos
2 Replies
KevinArmstrong
New Contributor
Tim,

I have had success integrating more complex SQL queries (especially ones that need aggregate functions) into Workflow Manager by saving them as database views that are associated with the wmx schema.  I am not very familiar with Oracle, but in SQL Server the view name ends up looking like wmx.VIEW_NAME.  Once created, the view shows up just like a table in the Workflow Manager SQL editor for reports and queries.  Maybe this can work for your SQL statement as well.
0 Kudos
TimJacobsen
New Contributor III
Kevin,
That is a great idea!  I had a little wfm SQL view tunnel vision going on there, thanks for pulling me up!
0 Kudos