Hours calc

[#1]

Hi all,

Please assist a newby.

This was probably asked a few times. I serached, but nothing.

I need to calc hours worked per employee per day. Data is from SQL and very simple.

 IndivNdx      
In/Out               
EventTime
3996     
IN
02/02/2012  07:52:05 AM
3996     
OUT
02/02/2012  04:55:50 PM
6632     
IN
02/02/2012  08:04:26 AM
6632
  OUT
02/02/2012  12:14:10 PM
6632     
IN
02/02/2012  02:41:13 PM
6632
  OUT
02/02/2012  05:08:36 PM

[#2]

Sample table attached

[eliran · Accepted solution]

See the attachment, this is one approach..

Hope it helps,

Eliran.

[Sokkorn]

Hi,

Use scritp like this

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
[Data]:
LOAD * INLINE [
IndivNdx,     In-Out,    EventTime
3996,    IN,    02-02-2012 07:52:05 AM
3996,    OUT,    02-02-2012 04:55:50 PM
6632,    IN,    02-02-2012 08:04:26 AM
6632,    OUT,    02-02-2012 12:14:10 PM
6632,    IN,    02-02-2012 02:41:13 PM
6632,    OUT,    02-02-2012 05:08:36 PM];
[Data1]:
LOAD
    IndivNdx,
    MIN(EventTime)    AS IN
RESIDENT [Data] WHERE [In-Out]='IN' GROUP BY IndivNdx;
INNER JOIN ([Data1])
[Data2]:
LOAD
    IndivNdx,
    MAX(EventTime)    AS OUT
RESIDENT [Data] WHERE [In-Out]='OUT' GROUP BY IndivNdx;

Next create one Straight Table with IndivNdx as dimension and INTERVAL(OUT-IN,'hh:mm:ss') as expression.

See the sample attached file also.

Regards,

Sokkorn Cheav

[#5]

Brilliant.

Thanks alot.