Add multiple "changes in time" from the same table

My table takes a timestamp whene开发者_如何学编程ver 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

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.

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.

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