Re: Time difference between records

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 15 Sep 2004 08:09:40 GMT
Message-ID: <slrnckfu60.364.rene.nyffenegger@zhnt60m34.netarchitects.com>

> I need to find a method (sql, plsql) to find the time difference between
> records
>
> Example,
> Ticket# Operation Time
> A1 Modify 1000
> A1 Modify 1100
> A1 Modify 1300
> A2 Modify 0900
> A2 Modify 0930
>
> Should produce something like:
>
> A1 1 hour
> A1 2 hours
> A2 30 minutes
>
> Has anyone done this before?

That should do it. How to convert 100 into 1 hour and 30 into 30 minutes is left as an exercise....  

create table ex (
  ticket char(2),

  op      varchar(6),
  tm      number(4)

);

insert into ex values ('A1', 'Modify', 1000);
insert into ex values ('A1', 'Modify', 1100);
insert into ex values ('A1', 'Modify', 1300);
insert into ex values ('A2', 'Modify',  900);
insert into ex values ('A2', 'Modify',  930);

select
  ticket, tm
from (
  select ticket, tm - lag(tm) over (

                        partition by ticket 
                        order by tm) tm

  from ex
)
where tm is not null;

drop table ex;

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Wed Sep 15 2004 - 03:09:40 CDT