I have a series of data (to the order of 100,000+ rows of data which denotes the date and time an action was performed. The LOAD statement pulls in four different pieces of data and the structure looks like this:
I need to calculate the amount of time elapsed between actions D and E, E and A, D and A and D and C so that I can look at a specific incident and then report only what the time differences are.
I really don't have the first clue where to start.
Additionally, some of the times may not exist (for example, a unit may not perform action E) and in these cases the TimeStamp is set to 0.
I attempted to adapt your code to my system but I can't seem to get it to work. It loads the data but isn't calculating the times. The times are always 0. When I use your example, it works as expected. My script follows:
"incident_num" as Incident,
"unit_id" as Unit,
TimeStamp(Timestamp#("unit_status_datetime",'D/M/YYYY hh:mm:ss')) as DateTime,
If("cad_command_code" = 'D', 1,
If("cad_command_code" = 'E', 2,
If("cad_command_code" = 'A', 3,
If("cad_command_code" = 'C', 4)))) as ActionFlag;
SQL SELECT "incident_num",
WHERE "incident_num" like '1500001%';
If(ActionFlag = 1, 0) as ActionStart,
If(ActionFlag = 2 and Previous(ActionFlag) = 1, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [E-D],
If(ActionFlag = 3 and Previous(ActionFlag) = 2, Interval(DateTime - Previous(DateTime),'hh:mm:ss'),0) as [A-E],
If(ActionFlag = 3 and Previous(Previous(ActionFlag)) = 1, Interval(DateTime - Previous(Previous(DateTime)),'hh:mm:ss'),0) as [A-D]
Attached is a simple example of how to find the smallest timestamp over Incident and Unit, and the use this to compare with the timestamp on each row. To show the difference you can use the Interval() function.