Oracle - Calculating time differences

Question

Let's say I have following data:

Create Table Pm_Test (
Ticket_id Number,
Department_From varchar2(100),
Department_To varchar2(100),
Routing_Date Date
);

Insert Into Pm_Test Values (1,'A','B',To_Date('20140101120005','yyyymmddhh24miss'));
Insert Into Pm_Test Values (1,'B','C',To_Date('20140101130004','yyyymmddhh24miss'));
Insert Into Pm_Test Values (1,'C','D',To_Date('20140101130004','yyyymmddhh24miss'));
Insert Into Pm_Test Values (1,'D','E',To_Date('20140201150004','yyyymmddhh24miss'));
Insert Into Pm_Test Values (2,'A','B',To_Date('20140102120005','yyyymmddhh24miss'));
Insert Into Pm_Test Values (3,'D','B',To_Date('20140102120005','yyyymmddhh24miss'));
Insert Into Pm_Test Values (3,'B','A',To_Date('20140102170005','yyyymmddhh24miss'));

For the following requirements I already added two virtual columns, I think they might be necessary:

Select t.*,
Count(Ticket_id)  Over (Partition By Ticket_id Order By Ticket_id) Cnt_Id,
Row_Number() Over (Partition By Ticket_id Order By Ticket_id ) row_number
From Pm_Test t;

1) I want to measure how long each ticket stayed in a department (routing_date of successor_department - routing_date of predecessor department) by adding the column PROCESSING_TIME:

enter image description here

2) I want to measure the total processing time by adding the column TOTAL_PROCESSING_TIME:

enter image description here

What SQL statements would be necessary to do so?

Thank you very much in advance!

Can you clarify Total_Processing_Time? Is it the total time a ticket was processing? — Nick, Mar 25 '15 at 16:45
In order to add total processing time per group, yes SQL is necessary. — Ben, Mar 25 '15 at 16:51
@Ben: Guessed so, I just don't know what SQL-Statements that would be. That's why I posted this question here. — royskatt, Mar 25 '15 at 16:56
for the first part, you should investigate lag/lead analytic functions. For the 2nd, you would need to either sum the times, or subtract the maximun date from the minimum date for each group. — Boneist, Mar 25 '15 at 17:00

score 1 · Accepted Answer · answered Mar 25 '15 at 18:00

To solve your problem, the way you described, the following sql should get you there. One thing to keep in mind, this data model doesn't seem the most efficient to capture processing times, if that's its true intent as the first department to get the ticket isn't measured.

 select dept.ticket_id, department_from, department_to, routing_date, dept_processing_time, total_ticket_processing_time
from
(select ticket_id, max(routing_date) - min(routing_date) total_ticket_processing_time
from pm_test
group by ticket_id) total
join
(select ticket_id, department_from, department_to, routing_date, 
coalesce(routing_date - lag(routing_date) over (partition by ticket_id order by routing_date), 0) dept_processing_time
from pm_test) dept
on (total.ticket_id = dept.ticket_id);

Thank you very much for that solution. About the data model, well, it's the AS-IS-state on which i have no influence on. It's also a business specific peculiarity. Btw, do you use coalesce instead of nvl because you want to be closer to ANSI SQL or is there any other reason for that? — royskatt, Mar 26 '15 at 08:30
I try to be ANSI compliant whenever possible just because I work in a multi-database environment. — Nick, Mar 26 '15 at 11:31

score 1 · Answer 2 · answered Mar 25 '15 at 18:56

This query produces desired output. Analytic functions max(), min() and lag() used for calculations. Results are in hours, like in your question.

SQLFiddle

select t.ticket_id, t.department_from, t.department_to, 
    to_char(t.routing_date, 'mm.dd.yy hh24:mi:ss') rd,
    count(ticket_id)  over (partition by ticket_id) cnt_id,
    row_number() over (partition by ticket_id order by t.routing_date ) rn,
    round(24 * (t.routing_date-
      nvl(lag(t.routing_date) over (partition by ticket_id 
        order by t.routing_date), routing_date) ) , 8) dept_time,
    round(24 * (max(t.routing_date) over (partition by ticket_id) 
      - min(t.routing_date) over (partition by ticket_id)), 8) total_time
  from pm_test t

Oracle - Calculating time differences

2 Answers2