CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Mark
Mark

Reputation: 135

calculate and sum time difference between multiple rows

I have a child table that contains the history of statuses for the particular parent record.

The table would be:

Parent_id NUMBER(38)
Date_Created  DATE
Status VARCHAR2(15)

Sample Data:

1, sysdate-20, REQ
1, sysdate-10, INPRG
1, sysdate-5, WAIT
1, sysdate-2, INPRG
1, sysdate, COMP

For any particular parent_id, how can I calculate the total time a parent_id stayed in a particular status? Given that the calculation is the date next status was created minus the teh date record was created. Keeping in mind that the status can occur multiple times.

For the sample data, how could you calculate the total time the record was in "INPRG" status?

It has to be done entirely in Oracle SQL. No functions, procedures, packages, etc.

Thanks in advance.

Upvotes: 1

Views: 2339

Answers (2)

chezy525
chezy525

Reputation: 4174

I primarily use SQL Server, not Oracle, so forgive me if my syntax is a little off...

with base as (
  select Parent_id, Date_Created, Status,
    row_number() over(partition by Parent_id order by Date_Created) as 'row'
  from Table
)
select Parent_id, Status, sum(timeInStatus)
from (
  select this.Parent_id, this.Status,
    next.Date_Created-this.Date_Created as 'timeInStatus'
  from base this
    join base next on this.Parent_id=next.Parent_id
                  and this.row=next.row-1
  ) t
where Status = 'INPRG'
group by Parent_id, Status

The basic concept is to utilize row_number to self join each row to it's next row and compute the time between them. Then it's just a simple aggregation of that data to get the answer you want.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

You can use the analytic function LEAD and LAG to access data from the next or prior row in the result set. Something like this will give you the total time in each status

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 parent_id, sysdate-20 date_created, 'REQ' status from dual
  3    union all
  4    select 1, sysdate-10, 'INPRG' from dual
  5    union all
  6    select 1, sysdate-5, 'WAIT' from dual
  7    union all
  8    select 1, sysdate-2, 'INPRG' from dual
  9    union all
 10    select 1, sysdate, 'COMP' from dual
 11  )
 12  select parent_id,
 13         status,
 14         sum(time_in_status)
 15    from (
 16      select parent_id,
 17             date_created,
 18             nvl(lead(date_created) over
 19                          (partition by parent_id
 20                               order by date_created),
 21                 sysdate) next_status_date,
 22             nvl(lead(date_created) over
 23                          (partition by parent_id
 24                               order by date_created),
 25                 sysdate) -
 26               date_created time_in_status,
 27             status
 28        from t)
 29*  group by parent_id, status
SQL> /

 PARENT_ID STATU SUM(TIME_IN_STATUS)
---------- ----- -------------------
         1 REQ                    10
         1 COMP                    0
         1 WAIT                    3
         1 INPRG                   7

Upvotes: 3

Related Questions