Time difference between two rows

[#1]

Hi there

I'm trying tow calculate the time difference between two rows.  Below is my script for the two fields.  Im also sorting correctly, but still dont get the proper durations.  PLEASE HELP !!!!???

INCIDENT_MAIN_NEW:

LOAD

  *,

  If(ParentLink_RecID = Peek(ParentLink_RecID), Peek(CreatedDateTime_Step) - CreatedDateTime_Step

  ,If(Status = 'CLOSED', Interval(ClosedDateTime - CreatedDateTime_Step, 'D hh:mm'), Interval(NOw(1) - CreatedDateTime_Step, 'D hh:mm')

  )

  ) As Step_Duration,

  If(ParentLink_RecID = Peek(ParentLink_RecID), Peek(CreatedDateTime_Step) - CreatedDateTime_Step

  ,If(Status = 'CLOSED', Interval(ClosedDateTime - CreatedDateTime_Step, 'D'), Interval(NOw(1) - CreatedDateTime_Step, 'D')

  )

  )

  As Step_Duration_Days

Resident INCIDENT

ORDER BY IncidentNumber, CreatedDateTime_Step ;

[sunny_talwar]

Would you be able to give an example (may be couple of rows of data) with what you got and what you expect to get.

Best,

S

[#3]

Hi S

Here is ONE incident.  Im getting rubbish data back.

IncidentNumber	CreatedDateTime_Step	Step Duration	Step_Duration_Days
724695	22/12/2011 13:27		-0.16685185185634
724695	22/12/2011 13:30		-0.0025231481413357
724695	22/12/2011 09:27		1181
724695	01/12/2012 12:33		-

[Roop]

Try a simpler version to start with to return the variance in seconds:

LOAD Num,

     Date,

     Date - peek(Date) as Datevar,

     24*60*60*(Date - peek(Date)) as DatevarSec

.....

etc

This seems to work with your figures.

[#5]

Thanks Rupert

I've given that a try.  BUt no luck.  I might be doing something wrong somewhere 

[Roop]

Can you include your script that you are trying again?

Thanks

[rubenmarin]

Hi Bradley,

This: Peek(CreatedDateTime_Step) - CreatedDateTime_Step

Should be: CreatedDateTime_Step - Peek(CreatedDateTime_Step)

And not sure but maybe ParentLink_RecID should be on Order By.

Or change the "If(ParentLink_RecID = Peek(ParentLink_RecID)" to

If(IncidentNumber=Peek(IncidentNumber)

[maxgro]

try this

a:

load *, date#(CreatedDateTime_Step, 'DD/MM/YYYY hh:mm')  as d inline [

IncidentNumber, CreatedDateTime_Step, Step Duration, Step_Duration_Days

724695, 22/12/2011 13:27, -0.16685185185634

724695, 22/12/2011 13:30, -0.0025231481413357

724695, 22/12/2011 09:27, 1181

724695, 01/12/2012 12:33, -

];

b:

load

  d,

  d - Peek(d) as Duration,

  floor(d - Peek(d)) as DurationD,

  interval(frac(d - Peek(d))) as DurationS

Resident a

order by d;

DROP Table a;