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
you see the function like Running-difference()
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...
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.