Difference in Timestamps Based On Previous Record's Timestamp

[villegasi03]

QV Community,

Sorry for the title of the post, I couldn't think of any other way to put it. I am sure this will be a simple question to answer but I am stumped. I am working with customer call data as shown in the table below. Nothing fancy. Just info on when the customer called us. How would you calculate the elapsed time since the previous call made by the customer. I attempted to use the previous function but it did nothing for me. I kept getting bad results because the calculations would not stay within distinct customer IDs. Any assistance with this would be very much appreciated. Thank you.

CALLAUTOHASHID	CALLDATE	CALLTIME	CUSTOMERID	CALLDATETIME	ElapsedTimeSinceLastCall
3	01/03/2012	7:08 AM	11122222	01/03/2012 7:08 AM	?
677	01/03/2012	9:53 AM	11122222	01/03/2012 9:53 AM	?
747	01/03/2012	10:04 AM	11122222	01/03/2012 10:04 AM	?
10535	01/04/2012	6:10 PM	11122222	01/04/2012 6:10 PM	?
15361	01/05/2012	5:47 PM	11122222	01/05/2012 5:47 PM	?
93834	01/26/2012	4:23 PM	11155555	01/26/2012 4:23 PM	?
93834	01/26/2012	4:23 PM	11155555	01/26/2012 4:23 PM	?
93900	01/26/2012	4:30 PM	11155555	01/26/2012 4:30 PM	?
202200	02/27/2012	7:27 PM	11155555	02/27/2012 7:27 PM	?
262944	03/16/2012	9:19 AM	11155555	03/16/2012 9:19 AM	?

[fosuzuki · Accepted solution]

Hi,

Maybe you could make your load statement like this:

LOAD CALLAUTOHASHID,

          CALLDATE,

          CALLTIME,

          CUSTOMERID,

          CALLDATETIME,

          IF(PEEK('CUSTOMERID')=CUSTOMERID, CALLDATETIME - PEEK('CALLDATETIME')) AS ELAPSEDTIMESINCELASTCALL

FROM XXXXX

ORDER BY CUSTOMERID, CALLDATETIME;

The idea is to use the peek() function to use the previous' record information.

Hope this helps you

Fernando

[villegasi03]

This worked perfectly! Thank you Fernando!