Calculate difference of current row and previous row.

[Ixhers]

After searching the interwebs and various forums, still with no solution, I turn here for your expertise.

I have a simple list report including a timestamp.
I want to compare the timestamp on the current row with the timestamp on the previous row and get the difference of this as hh:mm:ss.

The list report looks something like;

| Date	| Sequence id	| Actual Time
| 2014-03-17	| 115033121	| 05:35:18
| 2014-03-17	| 115033135	| 05:37:22

And I would like the output to look like;

| Date	| Sequence id	| Actual Time	| Time Diff
| 2014-03-17	| 115033121	| 05:35:18	| 00:00:00    <-- No value here as there is no row above the value in "Actual Time".
| 2014-03-17	| 115033135	| 05:37:22	| 00:02:04    <-- The difference between the value in "Actual Time" and the row above.

I have tried "running-difference" along with 2 reports on an outer join and various other rather intricate solutions.
And I still am not closer to my goal.
I am sure there must be an easier way to solve this?

I hope I described my issue in a proper way.
Brgs / Rob

[srinu_anu2007]

you see the function like Running-difference()

[navissar]

Running-difference would require a number. I don't have a suitable time dimension to test, but here's a thought:
1. Convert your time to numeric:
There are several methods for this depending on which DB you're using. The most general case I can think of is if your time field is textual, in which case you simply concatenate substring([time],1,2)+substring([time],4,2)+substring([time],7) and case the whole thing to numeric.
So: 14:05:23 is now 140,523; 00:05:42 is now 542 and so on.
Use running difference on this field.
the result will also be numeric (The running difference between 05:35:18 and 05:37:22 is 53,722-53,518=204).
You can convert it back - again, depending on your DB. The generalized solution is something like:
HOURS:
case when character_length(cast([time_diff]/10000,varchar(2)))<2 then '0'+cast([time_diff]/10000,varchar(2)) else cast([time_diff]/10000,varchar(2)) end
MINUTES
case when character_length(cast(mod([time_diff],10000)/100,varchar(2)))<2 then '0'+cast(mod([time_diff],10000)/100,varchar(2)) else cast(mod([time_diff],10000)/100,varchar(2)) end
SECONDS:
case when character_length(cast(mod([time_diff],100),varchar(2)))<2 then '0'+cast(mod([time_diff],100),varchar(2)) else cast(mod([time_diff],100),varchar(2)) end
Concatenate these with ':' and you get back to a time format...

[nblank]

If your source is an Oracle database your can use the Oracle function "LAG".

Something like:

select
sequence_id,
actual_time,
lag(actual_time) over (order by sequence_id) prev_actual_time,
actual_time - lag(actual_time) over (order by sequence_id) prev_actual_time time_diff
from table

Put this in a sql item as source of a query.