Calculate time working with incident

[Corres]

CR 2008, Oracle
Dear specialists,
Please give me ideas how to accomplish the following:
The report has to be grouped by INC_ID and has to display for department 99 - length of time and person working with incidents:
INC_ID DATE_RECEIVED RESPONSE_TIME TOTAL_TIME PERSON
898
04/06/2012 14:29 218 05/06/2012 10:08:26 AM minus 04/06/2012 2:29:23 PM HARDRIT
05/06/2012 14:08 2400 12/06/2012 2:08:52 PM minus 05/06/2012 2:08:46 PM HARDRIT
904 04/06/2012 14:31 212 05/06/2012 10:03:19 AM minus ITCHARDBP

Here is the data from the table:
INC_ID ACT_ID TYPE_ID SERV_DEPT ASS_DEPT DATE_ACT RESP_TIME PERS_ID
904 3849728 1 1 99 06/04/2012 14:31:57 212 ELNEMRN
904 3851440 1 99 99 06/05/2012 10:03:13 ARDBP
904 3851444 1 99 1 06/05/2012 10:03:19 93 ITCHARDBP
904 3852219 1 1 1 06/05/2012 11:36:52 JAKWND
904 3852559 100 1 0 06/05/2012 12:49:51 DOKKBIAM
904 3855304 136 1 0 06/06/2012 11:46:40 NUALLJ
904 3855306 5 1 0 06/06/2012 11:46:47 TTALLJ
898 3849710 1 1 99 06/04/2012 14:29:23 218 URGEMRN
898 3851494 1 99 99 06/05/2012 10:07:55 HARDRIT
898 3851497 101 99 0 06/05/2012 10:08:13 HARDRIT
898 3851499 1 99 57 06/05/2012 10:08:26 230 HARDRIT
898 3852949 42 57 0 06/05/2012 13:58:46 MEYJEMRN
898 3853011 1 57 99 06/05/2012 14:08:46 2,400 MEYJEMRN
898 3866405 101 99 0 06/12/2012 14:08:46 HARDRIT
898 3866408 4 99 1 06/12/2012 14:08:52 HARDRIT
898 3866409 1 99 1 06/12/2012 14:08:52 HARDRIT
898 3878538 5 1 0 06/19/2012 08:23:06 ENGERM

The incidents can be assigned to department 99, then resolved and reassigned to another dept as in example of incident 904. Or they can be reassigned from other departments twice or more as an incident 898.
The challenge for me is dealing with incidents such as # 898. I was trying to create variables for date_received_first and date_received_next (dates in black, bold), date_reassigned_first and date_reassigned_next (dates in purple), person_first and person_next (in purple color). But the variables show only the latest values in group footer. Also, it is difficult to differentiate dates reassigned first time and next times.
Thank you in advance for any ideas!!

 

[Madawc]

You could group at two levels, first Incident and then Person for the Incident.

Summary totals would give you the minimum and maximum datetime. DateAdd would find the difference. Of course that would just be elapsed time: if you need time worked it would be harder and need knowledge of your working hours.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Corres]

Thank you very much, Madawc for your reply! I appreciated it very much.

I cannot group by Person because persons from different departments can work on the Incident as well. I added colors to make more readable.

The Incident ( here inc_id = 898) can be assigned (assigning occurs when type_id = 1) by serv_dept 1 to ass_dept 99 first time on 06/04/2012 14:29:23. Dept 99 works on incident, then reassigns it to ass_dept 57 on 06/05/2012 10:08:26.
Then the incident can be received by the dept 99 once again on 06/05/2012 14:08:46 by serv_dept 57. Serv_Dept 99 works on incident until assigning to ass_dept 1 on 06/12/2012 14:08:52.
So, the incident can be assigned 1, 2 , 3 or more times to dept 99 until it solved.

INC_ID TYPE_ID SERV_DEPT ASS_DEPT DATE_ACT
898 1 1 99 06/04/2012 14:29:23
898 1 99 99 06/05/2012 10:07:55
898 101 99 0 06/05/2012 10:08:13
898 1 99 57 06/05/2012 10:08:26
898 42 57 0 06/05/2012 13:58:46
898 1 57 99 06/05/2012 14:08:46
898 101 99 0 06/12/2012 14:08:46
898 1 99 1 06/12/2012 14:08:52
898 5 1 0 06/19/2012 08:23:06

The report has to show the length of time working on incident by dept 99:
- 06/05/2012 10:08:26 minus 06/04/2012 14:29:23
- 06/12/2012 14:08:52 minus 06/05/2012 14:08:46
- XX/XX/XXXX XX:XX:XX minus XX/XX/XXXX XX:XX:XX

I was trying creating variables, but I cannot differentiate first reassignment (06/05/2012 10:08:26 ) from next one (06/12/2012 14:08:52). Thanks a lot for any ideas!

 

[IanWaterman]

I think you will have to split data using a command and unioning the two data sets. You can also use a Command Paramter so that if you ever want to run for another dept it can be changed at run time

select INC_ID, TYPE_ID, SERV_DEPT as maindept, DATE_ACT, 'SERV_DEPT' as SourceDept
from yourtable
where = 99
union all
select INC_ID, TYPE_ID, ASS_DEPT as maindept, DATE_ACT, 'ASS_DEPT' as SourceDept
from yourtable
where ASS_DEPT = 99

Ian

 

[Corres]

Thanks very much Ian! I do not have much experience with command line, I will try now and let you know how it is going.