If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Subtracting Time in the same table

I am trying to write a report to measure loss of time.
DSTAMP is the date field example: 2/2/2004 6:04:50 PM
USERNAME is the other field.

If there are 100 records for USERNAME 'Steve' then i want to know how i can tell if steven has time gaps of more then 5 minutes.
USERNAME DSTAMP
Steven 2/2/2004 6:04:50 PM starttime
2/2/2004 6:07:50 PM endtime/starttime
2/2/2004 6:36:50 PM endtime/starttime
2/2/2004 7:04:50 PM endtime

Re: Subtracting Time in the same table

Originally posted by Lorenzo2004
I am trying to write a report to measure loss of time.
DSTAMP is the date field example: 2/2/2004 6:04:50 PM
USERNAME is the other field.

If there are 100 records for USERNAME 'Steve' then i want to know how i can tell if steven has time gaps of more then 5 minutes.
USERNAME DSTAMP
Steven 2/2/2004 6:04:50 PM starttime
2/2/2004 6:07:50 PM endtime/starttime
2/2/2004 6:36:50 PM endtime/starttime
2/2/2004 7:04:50 PM endtime

select * from
(select user_id, dstamp, shift,
lag(dstamp,1) over (order by user_id, dstamp) as previous_dstamp,
trunc(to_number(dstamp-lag(dstamp,1) over (order by user_id, dstamp))*24*60,4) as minutes
from inventory_transaction
order by user_id, dstamp)
where minutes > 5