Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Post 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

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

    how do i do this?

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    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

    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?

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    Re: Subtracting Time in the same table

    Originally posted by ss659
    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.

    =)

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: Subtracting Time in the same table

    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

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    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

  6. #6
    Join Date
    Dec 2002
    Posts
    134
    What is the lag function?

    -dmitri

  7. #7
    Join Date
    Feb 2004
    Posts
    5
    Lag can be defined as:
    An Analytical Function that can be used to get the value of an attribute of the previous row.
    Last edited by Lorenzo2004; 02-10-04 at 18:29.

  8. #8
    Join Date
    Dec 2002
    Posts
    134
    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

  9. #9
    Join Date
    Feb 2004
    Posts
    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
    Last edited by Lorenzo2004; 02-11-04 at 12:45.

  10. #10
    Join Date
    Dec 2002
    Posts
    134
    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

    Originally posted by Lorenzo2004
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •