Entry/Exit time difference report?

[#1]

Hi guys,

I have an extract (attached) which contains all the access times (in and outs) to the building. Because the time data is in the same column (as opposed to having two columns: one for In and one for out), I am unable to calculate the differences/ time spent in the building.

Can any one suggest an approach to solving this? I have tried splitting the data out into separate tables and then joining in a variety of ways. Failed at all accounts. There has to be a simpler way of achieving this?

Time	Action
03/12/2012 07:51:08	IN
03/12/2012 08:09:17	OUT
03/12/2012 08:15:18	IN
03/12/2012 14:10:05	OUT
03/12/2012 16:11:09	IN
03/12/2012 18:32:16	OUT
04/12/2012 07:56:29	IN
04/12/2012 11:03:20	OUT
04/12/2012 11:08:44	IN
04/12/2012 14:31:17	OUT
04/12/2012 16:04:28	IN
04/12/2012 18:09:42	OUT
05/12/2012 08:00:37	IN
05/12/2012 12:31:26	OUT
05/12/2012 12:36:22	IN
05/12/2012 16:26:12	OUT
06/12/2012 08:00:09	IN
06/12/2012 11:27:08	OUT
06/12/2012 11:31:26	IN
06/12/2012 14:28:31	OUT
06/12/2012 16:19:42	IN
06/12/2012 19:01:22	OUT
07/12/2012 07:59:10	IN
07/12/2012 12:42:33	OUT
10/12/2012 08:01:20	IN
10/12/2012 12:56:51	OUT
10/12/2012 13:14:23	IN

[swuehl · Accepted solution]

Try something like

LOAD Time,

          Action,

          if( Action = 'OUT', interval(Time - peek('Time',-1) )) as TimeSpent

FROM ...;

[er_mohit]

in script

try this

interval((Time(Time,'hh:mm:ss')) -Previous(Time(Time,'hh:mm:ss')),'hh:mm:ss') as timediff

[#4]

Thank you! that works perfectly