Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a database that records a date and timestamp against every transaction and essentially I want to flag up and sum (over a day, week etc) the amount of time between transactions when the difference between them is more than 2 minutes. See below example.
Transaction | Timestamp | Difference |
---|---|---|
1 | 2012-06-08 12:09:12 | |
2 | 2012-06-08 12:10:12 | 00:01:00 |
3 | 2012-06-08 12:14:12 | 00:04:00 |
4 | 2012-06-08 12:15:12 | 00:01:00 |
5 | 2012-06-08 12.26:12 | 00:11:00 |
6 | 2012-06-08 12:45:12 | 00:19:00 |
7 | 2012-06-08 12:46:12 | 00:01:00 |
In this example the total difference between timestamps is 37 minutes, however I don't care about when the difference is less than 2 minutes so don't require 3 of the transactions so the difference I want to see is 34 minutes. I'm thinking that using peek and an if statement in the script will work but any other suggestions?
Thanks,
Ralph
Hi Ralph,
that table above is the input?
And the output should be
Transaction | Timestamp | Difference |
---|---|---|
1 | 2012-06-08 12:09:12 | |
3 | 2012-06-08 12:14:12 | 00:04:00 |
5 | 2012-06-08 12.26:12 | 00:11:00 |
6 | 2012-06-08 12:45:12 | 00:19:00 |
?
In the script I would use
LOAD Transaction, Timestamp, Difference
from ...
where Difference > Timestamp#('00:02:00', 'hh:mm:ss');
Ciao
Klaus
Hi Klaus,
This would work although I need to include all other transactions as well so I would need to essentially add a flag when the difference is greater than 2 minutes as well as summing the differences.
Thanks,
Ralph
Ok Ralpf,
for the flag take
LOAD
IF(Difference > Timestamp#('00:02:00', 'hh:mm:ss'), 1, 0) as relevant_difference,
....
and where do you want to store the sum
or do you need the sum in some chart?
Ciao
Klaus