-
Difference between rows
I need to find the difference between columns in consecutive rows where the type is datetime. I have a query to sort the data, but can't rely on the index of each for this problem.
I think I have a solution if I dump the sorted rows in another table where the index is in order and incremented by one for each row, but I'd like to avoid using another table. Basically, the sorted rows are obtained with
SELECT f1, f2, datetime FROM table
ORDER BY f1, f2, datetime;
One catch is that for the rows where the difference is computed, f1 and f2 and to be the same between the two rows.
Thanks.
-
Can you post some sample data and result you like?
-
Originally Posted by rmiao
Can you post some sample data and result you like?
Sure.
| 6006 | 09008501 | 2009-02-12 08:51:35 |
| 6006 | 09008501 | 2009-02-12 09:23:44 |
| 6006 | 09008501 | 2009-02-12 09:26:27 |
| 6006 | 09008501 | 2009-02-12 10:16:44 |
| 6006 | 09008601 | 2009-02-24 13:58:57 |
| 6006 | 09008601 | 2009-02-24 14:15:15 |
| 6006 | 09008701 | 2009-02-23 11:51:24 |
| 6006 | 09008701 | 2009-02-23 12:01:31 |
| 6007 | 09008701 | 2009-02-23 13:34:59 |
| 6007 | 09008701 | 2009-02-23 14:08:15 |
The result I'm looking for is
6006 09008501 total1
6006 09008601 total2
6006 09008701 total3
6007 09008701 total4
where total is the total of the differences between consecutive rows of the times where the first and second fields are the same.
I hope that makes sense.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|