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

I want to do a count for those times greater then 5 min.

how do i do this?

So you're saying between each and every record you want to see this?


Ex:

Record 1 ------ Steven ------ 2/2/2004 6:04:50 PM
Record 2 ------ Steven ------ 2/2/2004 6:06:20 PM
Record 3 ------ Steven ------ 2/2/2004 6:10:20 PM

Answer me this -- Looking at these 3 records, do you expect the query to pull anything back? Or would this be null?

-------------------------------------------------------------------------
Thank you for your reply.

I would expect it to be null or return a value of 0 since it is not greater then 5 minutes.

=)

database vendor specific code, db2 udb :

variant 1. first numerate time intervals for the given user, than join

with tempT(username, num, time) as
(select username, row_number() over(partition by username order by time asc) from table)
select a.username, count(*)
from tempT a , tempT b
where
a.username = b.username
and a.num = b.num-1
and timestampdiff(2, b.time - a.time) > 5*60
group by a.username

variant 2. for every record get a next time (subselect in select part)

select username, count(*)
from
(
select username, time,
(select min(b.time) from table b
where a.username = b.username
and a.time<b.time) nexttime
from table a
) a
where timestampdiff(2, nexttime - time) > 5*60
group by a.username

Thank you for the code above, can you make that code into reg SQL.

Does this look right?


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

What is the lag function?

-dmitri

Lag can be defined as:
An Analytical Function that can be used to get the value of an attribute of the previous row.

I see, nice one. In this case I would probably adjust code a little bit (but your code is fine as well)

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

Opps i fogot to add this:


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
where minutes > 5
order by user_id, dstamp)
and shift = 'FOAM'
and shift = 'FOPM'

Does this look right? Or can u help me re-write this more efficiently
F.Y.I. Mind you i fogot to mention how to calculate the time the user name is changed. I.E.
USER_ID DSTAMP SHIFT TIME PREVIOUS_ MINUTES
CHL 11-FEB-04 FOAM 07:59:10 11-FEB-04 14.8333
CHL 11-FEB-04 FOAM 10:31:46 11-FEB-04 36.8333
CHL 11-FEB-04 FOAM 11:39:06 11-FEB-04 15.4833
Total: 67.1499

1. I do not understand the following clause (it will be false always, no records return):
and shift = 'FOAM'
and shift = 'FOPM'
2. why do you need order by inside