Message from Greg Kellogg <gkellogg@opentext.com> via eLinkI am trying to build a report that will tell me whoperformed a specific task and how long it took.
I am using the wsubworktask table to get the majority of theinfo but I am attaching to the kuaf table for the user names.
I am lost in two areas – one, I know my join ispreventing me from seeing records that do not have a performerid assigned tothem so I am missing some of the WFMap’s tasks. This may not be aproblem but I would like to see each step regardless of if someone has beenassigned or the step was bypassed. I am thinking I need to do an out jointo kuaf to get this but am not sure and don’t want to screw it up.
The other issue is with the date math. I want tosubtract the Date Ready from the Date Done to get a time elapsed. I amcertain someone has done this type of report before and am hopeful they havesome SQL snippets they can share.
Here is the SQL I am using with a sample result:
select s.subworktask_workidas WorkID, s.subworktask_taskid as TaskID, s.subworktask_titleas Title,
s.subworktask_datereadyas Date_Ready, s.subworktask_datedone asDate_Done,
trunc((((86400*(s.subworktask_datedone- s.subworktask_datedone))/60)/60)/24) as Days,
trunc(((86400*(s.subworktask_datedone- s.subworktask_datedone))/60)/60) as Hours,
trunc((86400*(s.subworktask_datedone- s.subworktask_datedone))/60) as Minutes,
k.firstname|| ' ' || k.lastname as Performer
from wsubworktask s, kuaf k
where s.subworktask_performerid= k.id
Order by workid, taskid;
WORKID TASKID TITLE DATE_READY DATE_DONE DAYS HOURS MINUTES PERFORMER
68963 1 Submit Task 2/13/2008 2:32:05PM 2/13/2008 2:34:29 PM 0 0 0 Name Blanked
68963 2 Approver 2/13/2008 2:34:29 PM 2/13/2008 2:59:25 PM 0 0 0 Name Blanked
68963 3 <Initiator> 2/13/2008 2:59:25 PM 2/13/2008 3:08:45 PM 0 0 0 Name Blanked
Any help would be greatly appreciated.
--
GregKellogg
OpenText Global Services
CertifiedLivelink Administrator
(361)815-8950
