Add multiple "changes in time" from the same table

Question

My table takes a timestamp whenever the user status changes. Every day it begins at "a" and always progresses a->b->c->d->e->...

So I'm trying to find the total time between 'a' and 'b' plus the time betwen 'c' and 'd' plus 'e' and 'f' and so on.

This works to get the time between two of statuses:

SELECT cast(us2.status_timestamp as date) - cast(us1.status_timestamp as date)
FROM user_status US1, user_status US2
WHERE US1.statID = 'a' and us2.statID = 'b'
AND US1.user_ID = US2.user_ID
AND US1.work_date = US2.work_date

Is there a way to do this without just doing this select statement multiple times, then adding the results? I feel that there is a faster way.

Note: using Toad for Oracle

Justin Cave · Answer 1 · 2011-07-21T19:57:02.240

1

You can probably use the LEAD and LAG analytic functions

SELECT cast(status_timestamp as date) - cast(prior_status_timestamp as date) diff,
       user_id,
       work_date,
       statID
  FROM (
    SELECT status_timestamp,
           lag(status_timestamp) over 
              (partition by user_id, 
                            work_date 
               order by status_timestamp) prior_status_timestamp,
           user_id,
           statID,
           work_date
      FROM user_status )
 WHERE statID in ('b','d','f')

For every row in the table, this will get the status timestamp of the prior row for the same user_id and work_date (so the 'a' row for each user on each day will have a prior_status_timestamp of NULL). It then displays the difference between the current row and that prior row. You can then aggregate appropriately.

edited Jul 21 '11 at 19:57

answered Jul 21 '11 at 19:37

Justin Cave

227,342
24
367
384

I believe the OP wanted the differences between every other set of rows (i.e. 'a' and 'b', 'c' and 'd', etc., but not 'b' and 'c'). – Allan Jul 21 '11 at 19:47
@Allan - Good point. Updated my query to filter out the difference between 'b' and 'c', 'd' and 'e', and 'f' and 'g'. – Justin Cave Jul 21 '11 at 19:59
@Kevin - I was assuming that there were a fixed and known number of statID values since there are, presumably, a fixed number of user statuses in the course of the day. If that is not the case and there are an unknown number of statID values, Allen's `MOD( ASCII( statID ), 2 )` could be substituted for my `statID in` clause. Or we could add `ROW_NUMBER() OVER (partition by user_id, work_date order by status_timestamp) rnk` in the inner query and eliminate the even rnk's. – Justin Cave Jul 22 '11 at 13:34

score 1 · Answer 2 · answered Jul 21 '11 at 19:39

If I understand your requirements correctly, the following query will work:

SELECT SUM (CAST (us2.status_timestamp AS date) - CAST (us1.status_timestamp AS date))
FROM   user_status us1, user_status us2
WHERE  CHR (ASCII (us1.statid) + 1) = us2.statid
   AND MOD (ASCII (us1.statid), 2) <> 0
   AND us1.user_id = us2.user_id
   AND us1.work_date = us2.work_date;

MOD (ASCII (us1.statid), 2) will return 0 for characters with an even ASCII value and something else for all other values. 'a' is 97, so we always want to start where this value is not 0. We can use very similar logic to get the next value in the sequence: convert the letter to it's ASCII value, add 1, then convert back. Using that value in the join lets us find the corresponding end for each of our start times. Finally, we just sum all of the differences.

score 0 · Answer 3 · answered Jul 21 '11 at 19:41

SELECT US1.user_ID, SUM(difference)
FROM (
SELECT US1.user_ID, cast(us2.status_timestamp as date) - cast(us1.status_timestamp as date) AS difference
FROM user_status US1, user_status US2
WHERE 
   ((US1.statID = 'a' and us2.statID = 'b') OR
    (US1.statID = 'C' AND us2.statID = 'D') OR
    (US1.statID = 'E' AND us2.statID = 'F'))
AND US1.user_ID = US2.user_ID
AND US1.work_date = US2.work_date
) GROUP BY US1.user_id

Add multiple "changes in time" from the same table

3 Answers3