Calculating difference between times

[#1]

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:

Incident	Unit	DateTime	ActionTaken
1234	1	5/12/2015 18:34:15	D
1234	1	5/12/2015 18:35:00	E
1234	1	5/12/2015 18:38:27	A
1234	1	5/12/2015 19:07:32	C

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.

[MK_QSL]

Temp:
Load
  Incident,
  Unit,
  TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime,
  If(ActionTaken = 'D', 1,
  If(ActionTaken = 'E', 2,
  If(ActionTaken = 'A', 3,
  If(ActionTaken = 'C', 4)))) as ActionFlag
Inline
[
  Incident, Unit, DateTime, ActionTaken
  1234, 1, 5/12/2015 18:34:15, D
  1234, 1, 5/12/2015 18:35:00, E
  1234, 1, 5/12/2015 18:38:27, A
  1234, 1, 5/12/2015 19:07:32, C


  3456, 1, 5/12/2015 18:34:15, D
  3456, 1, 5/12/2015 18:38:27, A
  3456, 1, 5/12/2015 19:07:32, C


  6789, 1, 5/12/2015 18:34:15, D
  6789, 1, 5/12/2015 18:35:00, E
  6789, 1, 5/12/2015 19:07:32, C
];


Final:
Load
  *,
  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]
Resident Temp
Order By Incident, ActionFlag;


Drop Table Temp;

Same way you can create D-C and others...

Hint : Use

Previous(Previous(Previous(ActionFlag)))

[#3]

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:

Temp:

Load

  "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",

    "unit_id",

    "unit_status_datetime",

    "cad_command_code"

FROM nfirs.dbo."unit_status_hist"

WHERE "incident_num" like '1500001%';

Final:

Load

  *,

  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]

Resident Temp

Order By Incident, ActionFlag;

Drop Table Temp;

[maxgro]

you can join the rows in a single row with all datetime

z:

Load 

  Incident, 

  Unit, 

  TimeStamp(Timestamp#(DateTime,'D/M/YYYY hh:mm:ss')) as DateTime, 

  ActionTaken 

Inline 

[ 

  Incident, Unit, DateTime, ActionTaken 

  1234, 1, 5/12/2015 18:34:15, D 

  1234, 1, 5/12/2015 18:35:00, E 

  1234, 1, 5/12/2015 18:38:27, A 

  1234, 1, 5/12/2015 19:07:32, C 

  3456, 1, 5/12/2015 18:34:15, D 

  3456, 1, 5/12/2015 18:38:27, A 

  3456, 1, 5/12/2015 19:07:32, C 

  6789, 1, 5/12/2015 18:34:15, D 

  6789, 1, 5/12/2015 18:35:00, E 

  6789, 1, 5/12/2015 19:07:32, C 

]; 

y:

load Incident, Unit, DateTime as DateTimeD Resident z where ActionTaken = 'D';

join (y) load Incident, Unit, DateTime as DateTimeE Resident z where ActionTaken = 'E';

join (y) load Incident, Unit, DateTime as DateTimeA Resident z where ActionTaken = 'A';

join (y) load Incident, Unit, DateTime as DateTimeC Resident z where ActionTaken = 'C';

DROP Table z;

[ToniKautto]

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.

[#6]

So here's what I've got so far...

Temp:
LOAD "incident_num" as Incident,
    "unit_id" as Unit,
    If("cad_command_code" = 'D', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as DispatchTime,
      If("cad_command_code" = 'E', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as EnrouteTime,
      If("cad_command_code" = 'A', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ArriveTime,
      If("cad_command_code" = 'C', timestamp("unit_status_datetime",'MM/DD/YYYY hh:mm:ss')) as ClearTime,
    "situation_found_code" as "Situation Found Code",
    "situation_found_code"&' - '&"SFText" as "Situation Found",
    "district_code" as "District";
SQL SELECT "fire_incident_core"."situation_found_code",
    "fire_incident_core"."district_code",
    "fire_incident_core"."shift_code",
    "fire_incident_core"."incident_num",
    "unit_status_hist"."unit_id",
    "unit_status_hist"."unit_status_datetime",
    "unit_status_hist"."cad_command_code",
    "codes_table"."description" as "SFText"
FROM nfirs.dbo."fire_incident_core"
LEFT OUTER JOIN nfirs.dbo."unit_status_hist" ON "fire_incident_core"."incident_num" = "unit_status_hist"."incident_num"
LEFT OUTER JOIN nfirs.dbo."codes_table" ON "fire_incident_core"."situation_found_code"="codes_table"."code"
WHERE "codes_table"."code_group" = '0000';

Final:
Load
  *,
  Interval(EnrouteTime - DispatchTime,'ss') as Turnout,
  Interval(ArriveTime-EnrouteTime) as Travel,
  Interval(ArriveTime-DispatchTime) as Response
Resident Temp
Order By Incident;

Drop Table Temp;

I can use an expression to calculate the time between "enroute" and "dispatch"; however, if I try to pull it up in the load script, I don't get any data.

Any ideas?